Thread: knowing which table/schema is going to be chosen
I may have several tables with the same name in different schema. pina.mytable, test.mytable, import.mytable I have a search_path that may not just consist of $user, public. eg. $user, public, test, import I'd like to know which table is going to be chosen if I do a select * from mytable; In this case test.mytable will be chosen. Is there a way to ask postgresql the schema of the table that will be chosen? -- Ivan Sergio Borgonovo http://www.webthatworks.it
Ivan Sergio Borgonovo wrote: > I have a search_path that may not just consist of $user, public. > eg. > $user, public, test, import > > I'd like to know which table is going to be chosen if I do a > select * from mytable; > Is there a way to ask postgresql the schema of the table that will be > chosen? Hmm - I don't know of a specific function. You could do something like this though: SELECT nspname FROM pg_namespace WHERE oid = ( SELECT relnamespace FROM pg_class WHERE oid = 'mytable'::regclass::oid ); -- Richard Huxton Archonet Ltd
On Thu, 12 Nov 2009 10:38:27 +0000 Richard Huxton <dev@archonet.com> wrote: > Ivan Sergio Borgonovo wrote: > > I have a search_path that may not just consist of $user, public. > > eg. > > $user, public, test, import > > > > I'd like to know which table is going to be chosen if I do a > > select * from mytable; > > > Is there a way to ask postgresql the schema of the table that > > will be chosen? > > Hmm - I don't know of a specific function. You could do something > like this though: > SELECT nspname FROM pg_namespace > WHERE oid = ( > SELECT relnamespace FROM pg_class > WHERE oid = 'mytable'::regclass::oid > ); This surely meet my needs, and I'm going to place it in my toolbox still... is there a way that could use information_schema? My need was caused by a compromise with 2 immature API... so I'm not surprised that a solution looks like an hack but I was wondering if in other cases knowing in advance which table postgresql is going to pick up could be a legit interest. BTW I think I've spotted an error in the docs: http://www.postgresql.org/docs/8.3/interactive/ddl-schemas.html http://www.postgresql.org/docs/8.4/interactive/ddl-schemas.html 5.7.2. The Public Schema In the previous sections we created tables without specifying any schema names. By default, such tables (and other objects) are automatically put into a schema named "public". Every new database contains such a schema. Thus, the following are equivalent: ... CREATE TABLE products ( ... ); and: CREATE TABLE public.products ( ... ); I think they are not equivalent if the search_path contains the name of an existing schema. Is there anything equivalent to search_path in the SQL standard? thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it
Ivan Sergio Borgonovo <mail@webthatworks.it> writes: > BTW I think I've spotted an error in the docs: > http://www.postgresql.org/docs/8.3/interactive/ddl-schemas.html > http://www.postgresql.org/docs/8.4/interactive/ddl-schemas.html > 5.7.2. The Public Schema > In the previous sections we created tables without specifying any > schema names. By default, such tables (and other objects) are > automatically put into a schema named "public". Every new database > contains such a schema. Thus, the following are equivalent: ... "By default" means "assuming you're using the default search_path". However saying that here, where we haven't explained search_path yet, wouldn't be an improvement IMO. regards, tom lane