Schema search path
Hi there,
Sorry but I've got yet another issue to discuss today, this time that
on schema search path. In fact it may not be a bug, but it may be
worth a note in the documentation.
It seems that if the table in SELECT FROM has an explicit schema
specifier, further references to the same table name will implicitly
inherit it. E.g., this query will be valid because the second
reference will be to foo.bar not public.bar:
SELECT * FROM foo.bar WHERE bar.a=1;
^^^ this means foo.bar
Here is a more complex case where I initially came across this issue:
psql (8.4.4)
Type "help" for help.
pgsql=# show search_path;
search_path
----------------
"$user",public
(1 row)
pgsql=# create table public.tbl_bar (a int);
CREATE TABLE
pgsql=# create schema sch_foo;
CREATE SCHEMA
pgsql=# create table sch_foo.tbl_bar (a int);
CREATE TABLE
pgsql=# insert into public.tbl_bar (a) values (1);
INSERT 0 1
pgsql=# insert into sch_foo.tbl_bar (a) values (2);
INSERT 0 1
pgsql=# select a from tbl_bar where not exists (select a from
sch_foo.tbl_bar where tbl_bar.a=sch_foo.tbl_bar.a);
a
---
(0 rows)
pgsql=# select a from tbl_bar where not exists (select a from
sch_foo.tbl_bar where public.tbl_bar.a=sch_foo.tbl_bar.a);
a
---
1
(1 row)
As just shown, this can be even more confusing with nested queries.
Do you think it's a feature or a bug? :-)
Thanks!
Yar
Yaroslav Tykhiy wrote:
Hi there,
Sorry but I've got yet another issue to discuss today, this time that
on schema search path. In fact it may not be a bug, but it may be
worth a note in the documentation.It seems that if the table in SELECT FROM has an explicit schema
specifier, further references to the same table name will implicitly
inherit it. E.g., this query will be valid because the second
reference will be to foo.bar not public.bar:SELECT * FROM foo.bar WHERE bar.a=1;
^^^ this means foo.bar
No one has ever complained about this before.
As just shown, this can be even more confusing with nested queries.
Do you think it's a feature or a bug? :-)
Feature, and SQL-standard behavior.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
Bruce Momjian <bruce@momjian.us> writes:
Yaroslav Tykhiy wrote:
SELECT * FROM foo.bar WHERE bar.a=1;
^^^ this means foo.bar
Do you think it's a feature or a bug? :-)
Feature, and SQL-standard behavior.
It might be worth pointing out that this has nothing to do with
search_path; rather, the key is that the FROM clause establishes
a table alias "bar" for the query.
regards, tom lane
On 14/09/2010, at 8:56 AM, Tom Lane wrote:
Bruce Momjian <bruce@momjian.us> writes:
Yaroslav Tykhiy wrote:
SELECT * FROM foo.bar WHERE bar.a=1;
^^^ this means foo.barDo you think it's a feature or a bug? :-)
Feature, and SQL-standard behavior.
It might be worth pointing out that this has nothing to do with
search_path; rather, the key is that the FROM clause establishes
a table alias "bar" for the query.
Sure, that makes sense because it just extends the well-known aliasing
for unqualified column names, as in "SELECT a FROM foo", to table
names as well. But a remark on this feature in the SCHEMA related
documentation pages can be a good idea IMHO.
Thanks!
Yar