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:

the reason why this would be desirable, and
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;
**************************

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. — , Oct 14 '14 at 08:51
As noted below, I hadn't realised it was a documented behaviour until the first answer -- so my fault there. — saint_utz, Oct 14 '14 at 13:15

score 7 · Accepted Answer · edited Dec 20 '16 at 19:43

7

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

edited Dec 20 '16 at 19:43

Harris

1,775
16
19

answered Oct 14 '14 at 08:53

Ihor Romanchenko

26,995
8
48
44

Cheers -- answer led me to search through the psql page as well (http://www.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. – saint_utz Oct 14 '14 at 09:35
2

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. – saint_utz Oct 14 '14 at 09:43

score 2 · Answer 2 · answered Oct 14 '14 at 09:35

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

answered Oct 14 '14 at 09:35

Clodoaldo Neto

118,695
26
233
260

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. – saint_utz Oct 14 '14 at 09:50

score 0 · Answer 3 · edited Jul 16 '21 at 16:05

0

list all user tables with prefix

\dt *.<table_prefix>*

example:

 \dt *.my*

edited Jul 16 '21 at 16:05

mustaccio

18,234
16
48
57

answered Jul 16 '21 at 15:27

Liang Faan

31
2

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

3 Answers3

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