On Sat, Dec 6, 2008 at 8:50 AM, Martin Marques
<martin@marquesminen.com.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.