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

Question

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;
**************************

Ragan

What exactly is your question? This is documented behavior - and it works the same way a table name is resolved in e.g. a select statement. – Matildamatilde

As noted below, I hadn't realised it was a documented behaviour until the first answer -- so my fault there. – Ragan

Answer 1

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

Thekla

Cheers -- answer led me to search through the psql page as well (postgresql.org/docs/current/static/app-psql.html), where way down under the Patterns sub-heading of Meta-Commands the following is noted: 'Whenever the pattern parameter is omitted completely, the \d commands display all objects that are visible in the current schema search path', followed by the same definition of visibility. – Ragan

So it seems another way of thinking about \dt is that it returns the list of tables you would be accessing/manipulating if you used a table name without a schema qualification. – Ragan

Answer 2

To list just the table you want in all the schemas:

\dt *.my_table
         List of relations
 Schema |   Name   | Type  | Owner 
--------+----------+-------+-------
 first  | my_table | table | cpn
 public | my_table | table | cpn
 second | my_table | table | cpn

Rosenzweig

Cheers -- makes me realise if tables of interest share a substring you can get them by, e.g., \dt *.my_* . I was really looking for something quick that would list all tables in all search_path schemas, but may have to do a custom function for that. – Ragan

Answer 3

list all user tables with prefix

\dt *.<table_prefix>*

example:

 \dt *.my*

Understate