pg.ddx.io pgsql-admin@postgresql.org mailing list archive help / color / mirror / Atom feed From: Holger Jakobs <holger@jakobs.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: postgres confuses table alias for schema in selects
Date: Mon, 30 Nov 2020 14:41:51 +0100
Message-ID: <655C692B-7578-4127-A400-2D6EC58A0B47@jakobs.com> (raw )
In-Reply-To: <CA+O-ksBdtTjdbKD6uKyZFT4kyaavAVxxfxYttcPA2A=q_3KoUA@mail.gmail.com >
References: <CA+O-ksBdtTjdbKD6uKyZFT4kyaavAVxxfxYttcPA2A=q_3KoUA@mail.gmail.com >
Write (bucket). instead
Am 30. November 2020 14:29:43 MEZ schrieb jim schmidt <txherper@gmail.com>:
> List of relations
> Schema | Name | Type | Owner
>----------+---------------------------+------+-------
> aerodemo | vnd_avail_perf_bucket_sum | view | jjs
>(1 row)
>
>/* select * from vnd_avail_perf_bucket_sum; */
>/* #1 simple */
>select count(*) from vnd_avail_perf_bucket_sum;
> count
>-------
> 1708
>(1 row)
>
>/* #2 simple on view, no join */
>select count(*) from
>(
>select
> item_nbr,
> ic_category_nbr,
> weeks_late,
> count(*) bucket_count,
> line_stat_id
>from vnd_avail_perf_bucket_sum
>group by
> item_nbr,
> ic_category_nbr,
> weeks_late,
> line_stat_id
>) as count_query;
> count
>-------
> 1708
>(1 row)
>
>;
>/* #3 simple with alias */
>select count(*) from
>(
>select
> bucket.item_nbr,
> bucket.ic_category_nbr,
> bucket.weeks_late,
> count(*) bucket_count,
> bucket.line_stat_id
>from vnd_avail_perf_bucket_sum as bucket
>group by
> bucket.item_nbr,
> bucket.ic_category_nbr,
> bucket.weeks_late,
> bucket.line_stat_id
>) as count_query;
> count
>-------
> 1708
>(1 row)
>
>;
>/* #4 error sum on count
> bucket is a table alias, thinks its a schema */
>select count(*) from
>(
>select
> bucket.item_nbr,
> bucket.ic_category_nbr,
> bucket.weeks_late,
> sum(bucket.count(*)) bucket_count,
> bucket.line_stat_id
>from vnd_avail_perf_bucket_sum as bucket
>group by
> item_nbr,
> ic_category_nbr,
> weeks_late,
> line_stat_id
>) as count_query;
>psql:pct3.sql:60: ERROR: schema "bucket" does not exist
>LINE 9: sum(bucket.count(*)) bucket_count,
> ^
>;
>/* #4.1 bucket defined in with
> observation: works
>*/
>with bucket as
>(
>select
> item_nbr,
> ic_category_nbr,
> weeks_late,
> count(*) bucket_count,
> line_stat_id
>from vnd_avail_perf_bucket_sum
>group by
> item_nbr,
> ic_category_nbr,
> weeks_late,
> line_stat_id
>) select count(*) from bucket;
> count
>-------
> 1708
>(1 row)
>
>;
>/* #4.2
> no sum on count error
> bucket is a table alias, thinks its a schema */
>select count(*) from
>(
>select
> bucket.item_nbr,
> bucket.ic_category_nbr,
> bucket.weeks_late,
> bucket.count(*) bucket_count,
> bucket.line_stat_id
>from vnd_avail_perf_bucket_sum as bucket
>group by
> item_nbr,
> ic_category_nbr,
> weeks_late,
> line_stat_id
>) as count_query;
>psql:pct3.sql:101: ERROR: schema "bucket" does not exist
>LINE 10: bucket.count(*) bucket_count,
> ^
>;
>/* #4.3
> no table alias
>*/
>select count(*) from
>(
>select
> item_nbr,
> ic_category_nbr,
> weeks_late,
> count(*) bucket_count,
> line_stat_id
>from vnd_avail_perf_bucket_sum
>group by
> item_nbr,
> ic_category_nbr,
> weeks_late,
> line_stat_id
>) as count_query;
> count
>-------
> 1708
>(1 row)
>
>;
>/* #5 no-count */
>select
> bucket.item_nbr,
> bucket.ic_category_nbr,
> bucket.weeks_late,
> bucket.count(*) / bucket_sum.total_buckets bucket_pct,
> bucket.line_stat_id
>from vnd_avail_perf_bucket_sum bucket,
> bucket_sum
>where bucket_sum.item_nbr = bucket.item_nbr and
> bucket_sum.ic_category_nbr = bucket.ic_category_nbr and
> bucket_sum.line_stat_id = bucket.line_stat_id
>group by
> item_nbr,
> ic_category_nbr,
> weeks_late,
> line_stat_id
>;
>psql:pct3.sql:139: ERROR: relation "bucket_sum" does not exist
>LINE 9: bucket_sum
> ^
>/* #6 undefined view bucket_sum
> ??? what is wrong with the from clause */
>select count(*) from
>(
>select
> bucket.item_nbr,
> bucket.ic_category_nbr,
> bucket.weeks_late,
> bucket.count(*) / bucket_sum.total_buckets) bucket_pct,
> bucket.line_stat_id
>from vnd_avail_perf_bucket_sum bucket,
> bucket_sum
>where bucket_sum.item_nbr = bucket.item_nbr and
> bucket_sum.ic_category_nbr = bucket.ic_category_nbr and
> bucket_sum.line_stat_id = bucket.line_stat_id
>group by
> item_nbr,
> ic_category_nbr,
> weeks_late,
> line_stat_id
>) as count_query
>;
>psql:pct3.sql:163: ERROR: syntax error at or near "from"
>LINE 11: from vnd_avail_perf_bucket_sum bucket,
> ^
>/* #6.1 bucket_sum using with
> extraneous ')'
> observation: syntax error near from,
> question: why?
>*/
>select count(*) from
>(
>with bucket_sum as
>(select
> vs.item_nbr,
> vs.ic_category_nbr,
> count(vs.*) total_buckets,
> vs.line_stat_id
>from vnd_avail_perf_bucket vs
>group by
> vs.item_nbr,
> vs.ic_category_nbr,
> vs.line_stat_id
>)
>select
> bucket.item_nbr,
> bucket.ic_category_nbr,
> bucket.weeks_late,
> bucket.count(*) / bucket_sum.total_buckets) bucket_pct,
> bucket.line_stat_id
>from vnd_avail_perf_bucket_sum bucket,
> bucket_sum
>where bucket_sum.item_nbr = bucket.item_nbr and
> bucket_sum.ic_category_nbr = bucket.ic_category_nbr and
> bucket_sum.line_stat_id = bucket.line_stat_id
>group by
> item_nbr,
> ic_category_nbr,
> weeks_late,
> line_stat_id
>) as count_query
>;
>psql:pct3.sql:201: ERROR: syntax error at or near "from"
>LINE 26: from vnd_avail_perf_bucket_sum bucket,
> ^
>/* #6.2 bucket_sum using with, no alias on with select
> no extranoues ')'
> observation: syntax error near from,
> question: why?
>*/
>select count(*) from
>(
>with bucket_sum as
>(select
> item_nbr,
> ic_category_nbr,
> count(*) total_buckets,
> line_stat_id
>from vnd_avail_perf_bucket
>group by
> item_nbr,
> ic_category_nbr,
> line_stat_id
>)
>select
> bucket.item_nbr,
> bucket.ic_category_nbr,
> bucket.weeks_late,
> bucket.count(*) / bucket_sum.total_buckets bucket_pct,
> bucket.line_stat_id
>from vnd_avail_perf_bucket_sum bucket,
> bucket_sum
>where bucket_sum.item_nbr = bucket.item_nbr and
> bucket_sum.ic_category_nbr = bucket.ic_category_nbr and
> bucket_sum.line_stat_id = bucket.line_stat_id
>group by
> item_nbr,
> ic_category_nbr,
> weeks_late,
> line_stat_id
>) as count_query
>;
>psql:pct3.sql:238: ERROR: schema "bucket" does not exist
>LINE 24: bucket.count(*) / bucket_sum.total_buckets
>bucket_pct...
> ^
>/* #6.3 bucket_sum using with, no alias on with select, fixed
>extraneous
>paren
> observation: syntax error near from,
> question: why?
>*/
>select count(*) from
>(
>with bucket_sum as
>(select
> item_nbr,
> ic_category_nbr,
> count(*) total_buckets,
> line_stat_id
>from vnd_avail_perf_bucket
>group by
> item_nbr,
> ic_category_nbr,
> line_stat_id
>)
>select
> bucket.item_nbr,
> bucket.ic_category_nbr,
> bucket.weeks_late,
> bucket.count(*) / bucket_sum.total_buckets bucket_pct,
> bucket.line_stat_id
>from vnd_avail_perf_bucket_sum bucket,
> bucket_sum
>where bucket_sum.item_nbr = bucket.item_nbr and
> bucket_sum.ic_category_nbr = bucket.ic_category_nbr and
> bucket_sum.line_stat_id = bucket.line_stat_id
>group by
> item_nbr,
> ic_category_nbr,
> weeks_late,
> line_stat_id
>) as count_query
>;
>psql:pct3.sql:274: ERROR: schema "bucket" does not exist
>LINE 23: bucket.count(*) / bucket_sum.total_buckets
>bucket_pct...
> ^
>/* #7 bucket is a table alias thinks it's a schema */
>select count(*) from
>(
>select
> bucket.item_nbr,
> bucket.ic_category_nbr,
> bucket.weeks_late,
> sum(bucket.count(*) / bucket_sum.total_buckets) bucket_pct,
> bucket.line_stat_id
>from vnd_avail_perf_bucket_sum bucket
>where bucket_sum.item_nbr = bucket.item_nbr and
> bucket_sum.ic_category_nbr = bucket.ic_category_nbr and
> bucket_sum.line_stat_id = bucket.line_stat_id
>group by
> item_nbr,
> ic_category_nbr,
> weeks_late,
> line_stat_id
>) as count_query
>;
>psql:pct3.sql:295: ERROR: schema "bucket" does not exist
>LINE 8: sum(bucket.count(*) / bucket_sum.total_buckets) bucke...
> ^
>/* #8 ??? thinks bucket is a schema, but it is a table alias */
>with bucket_sum as (
>select
> vs.item_nbr,
> vs.ic_category_nbr,
> count(vs.*) total_buckets,
> vs.line_stat_id
>from vnd_avail_perf_bucket vs
>group by
> vs.item_nbr,
> vs.ic_category_nbr,
> vs.line_stat_id
>)
>select
> bucket.item_nbr,
> bucket.ic_category_nbr,
> bucket.weeks_late,
> bucket.count(*) / bucket_sum.total_buckets bucket_pct,
> bucket.line_stat_id
>from vnd_avail_perf_bucket_sum bucket,
> bucket_sum
>where bucket_sum.item_nbr = bucket.item_nbr and
> bucket_sum.ic_category_nbr = bucket.ic_category_nbr and
> bucket_sum.line_stat_id = bucket.line_stat_id
>group by
> item_nbr,
> ic_category_nbr,
> weeks_late,
> line_stat_id
>;
>psql:pct3.sql:326: ERROR: schema "bucket" does not exist
>LINE 18: bucket.count(*) / bucket_sum.total_buckets
>bucket_pct...
> ^
>
>
>--
>blog: homeless-by-choice.blogspot.com
--
Holger Jakobs, Bergisch Gladbach
+49 178 9759012
- sent from mobile, therefore short -
view thread (2+ messages)
Message-ID: <655C692B-7578-4127-A400-2D6EC58A0B47@jakobs.com>
Permalink: ../655C692B-7578-4127-A400-2D6EC58A0B47@jakobs.com/
Also on: postgresql.org/message-id/655C692B-7578-4127-A400-2D6EC58A0B47@jakobs.com
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: holger@jakobs.com, pgsql-admin@lists.postgresql.org
Subject: Re: postgres confuses table alias for schema in selects
In-Reply-To: <655C692B-7578-4127-A400-2D6EC58A0B47@jakobs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox