Обсуждение: 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.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. 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