postgres confuses table alias for schema in selects

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