Search code examples
postgresqlsearch-path

PostgreSQL \dt command does not show *ALL* tables in search_path schemas

Why in PostgreSQL, when there are tables with the same name in different schemas, does \dt only include the table in the first schema listed in the search path, as per the example below?

I'm interested in:

  1. the reason why this would be desirable, and

  2. how it is actually achieved given the query underlying \dt (see far below).

(BTW, I realise from this answer that \dt *.* will list every table in every schema -- but for the example case below, that gives me 58 system tables that I don't want in addition to the two that I do!)

Example

dt_test=# CREATE SCHEMA first;
CREATE SCHEMA
dt_test=# CREATE SCHEMA second;
CREATE SCHEMA
dt_test=# CREATE TABLE first.my_table(id integer);
CREATE TABLE
dt_test=# CREATE TABLE second.my_table(id integer);
CREATE TABLE
dt_test=# set search_path to first,second;
SET
dt_test=# \dt
         List of relations
 Schema |   Name   | Type  | Owner  
--------+----------+-------+--------
 first  | my_table | table | postgres
(1 row)

dt_test=# set search_path to second,first;
SET
dt_test=# \dt
         List of relations
 Schema |   Name   | Type  | Owner  
--------+----------+-------+--------
 second | my_table | table | postgres
(1 row)

Query underlying \dt (shown when psql launched with -E command, e.g., psql -E dt_test)

********* QUERY **********
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     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 <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************
Solution

  • Here is a quote from the manual: http://www.postgresql.org/docs/current/static/functions-info.html

    Table 9-61 shows functions that determine whether a certain object is visible in the current schema search path. For example, a table is said to be visible if its containing schema is in the search path and no table of the same name appears earlier in the search path.

    Key part: no table of the same name appears earlier in the search path

    For set search_path to first,second; table second.my_table is not visible because it is blocked by first.my_table