Weirdness with OIDs and JOIN ON?
Why doesn't this work:
test=# select oid, relname, indisclustered from pg_index join pg_class
on indexrelid=oid where indexrelid > 17205;
ERROR: column "oid" does not exist
I'm _joining_ on the oid column.
If I qualify it, it works:
test=# select pg_class.oid, relname, indisclustered from pg_index join
pg_class on indexrelid=oid where indexrelid > 17205;
oid | relname | indisclustered
-------+--------------+----------------
17214 | child_b_key | t
17210 | parent_a_key | t
(2 rows)
I can't see that I've made an error in the first example - is it a
Postgres bug?
Chris
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
Why doesn't this work:
test=# select oid, relname, indisclustered from pg_index join pg_class
on indexrelid=oid where indexrelid > 17205;
ERROR: column "oid" does not exist
The JOIN is a bit like a view --- it doesn't have any system columns.
For example consider
select alias.* from (a join b on ...) as alias;
This should certainly not show any system columns --- both because SQL92
says so, and because there'd be no way to choose which input table's
system columns to use.
In scenarios where only one input table has an OID column, we could
maybe kluge things to allow it to be accessed without qualification,
but I don't really see the value of adding a wart for that ...
regards, tom lane