Re: posible BUG on psql... or maybe worst

On Sat, Dec 6, 2008 at 8:50 AM, Martin Marques
<martin(at)marquesminen(dot)com(dot)ar>wrote:

> I was making some table creation on one of our development DB and found
> that psql's \dt has problems showing all tables available. Basically, if you
> have to tables with the same name in different schemas, only one will be
> listed (the one on the schema that is first in the search_path).
>
> IMHO, \dt should show all the tables per-schema.
>
> Now what I can't find is where the problem is. \dt executes this query:
>
> SELECT n.nspname as "Schema",
> c.relname as "Name",
> CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN
> 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
> r.rolname as "Owner"
> FROM pg_catalog.pg_class c
> JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
> LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
> WHERE c.relkind IN ('r','')
> AND n.nspname <> 'pg_catalog'
> AND n.nspname !~ '^pg_toast'
> AND pg_catalog.pg_table_is_visible(c.oid)
> ORDER BY 1,2;
>
> The query looks ok, but it doesn't bring the 2 tables in the list.
>
>
Hi,

Schemas are a lot like directories at operating system level (except that
can't be nested).
When you ls (or dir) in /home/martin/ , normally you don't expect to see
/home/johnny/ listed as well.

But if you really want to see all tables, try adjusting search_path like
this:

SET search_path to myschema1,myschema2,public;

Then it should list all relations as you expect.