Why can't I see my table (PostgreSQL) when I use \dt(+) inside psql?

Question

I have created the table donor in the schema reference as per:

CREATE TABLE reference.donor (
    donor_code smallint PRIMARY KEY,
    donor_name character varying NOT NULL,
    donor_type smallint REFERENCES reference.donor_type (type_id),
    alpha_2_code char(2) REFERENCES reference.iso_3166_1 (alpha_2_code)
);

I have populated the table as per:

INSERT INTO reference.donor (donor_code, donor_name, donor_type, alpha_2_code)
SELECT donor_code, donor_name, donor_type, alpha_2_code
FROM reference.donor_template;

When I run:

\dt+ reference.*

inside psql I see the reference.donor table:

                          List of relations
  Schema   |      Name      | Type  |  Owner   | Size  | Description 
-----------+----------------+-------+----------+-------+-------------
 reference | donor          | table | postgres | 16 kB | 
 reference | donor_template | table | postgres | 16 kB | 
 reference | donor_type     | table | postgres | 16 kB | 
 reference | iso_3166_1     | table | postgres | 48 kB | 
(4 rows)

But when I run \dt+ donor* (or \dt(+)) I don't see the reference.donor table:

                          List of relations
  Schema   |      Name      | Type  |  Owner   | Size  | Description 
-----------+----------------+-------+----------+-------+-------------
 oecd_cl   | donor          | table | postgres | 16 kB | 
 reference | donor_template | table | postgres | 16 kB | 
 reference | donor_type     | table | postgres | 16 kB | 
(3 rows)

Why can I only see the reference.donor table if I run \dt+ reference.* or \dt+ *.donor?
I was expecting \dt (or \dt+) to display it, but it does not.

My search_path includes the schema reference & the user postgres has all permissions on the schema reference and all tables in the schema as per:

GRANT ALL ON ALL TABLES IN SCHEMA reference TO postgres;

Just to clarify, I have two donor tables, but they are in two different schemas i.e., oecd.donor & reference.donor. (I can see oecd.donor without any problems when I use \dt(+) inside psql).

score 14 · Accepted Answer · edited May 23 '17 at 12:40

The documentation on psql explains:

Whenever the pattern parameter is omitted completely, the \d commands display all objects that are visible in the current schema search path — this is equivalent to using * as the pattern. (An object is said to be visible if its containing schema is in the search path and no object of the same kind and name appears earlier in the search path. This is equivalent to the statement that the object can be referenced by name without explicit schema qualification.) To see all objects in the database regardless of visibility, use *.* as the pattern.

Bold emphasis mine.
Obviously, you have oecd_cl before reference in your search path. Use this for your purpose:

\dt *.donor*

And you'll get:

                          List of relations
  Schema   |      Name      | Type  |  Owner   | Size  | Description 
-----------+----------------+-------+----------+-------+-------------
 oecd_cl   | donor          | table | postgres | 16 kB | 
 reference | donor          | table | postgres | 16 kB | 
 reference | donor_template | table | postgres | 16 kB | 
 reference | donor_type     | table | postgres | 16 kB | 
(4 rows)

score 1 · Answer 2 · edited Aug 10 '15 at 11:29

1

First command works because all listed tables has 'reference' in their schema. Second command works the same for 'donor'. so the relation "reference.iso_3166_1" does not have any 'donor' in its name. if you want to list iso_3166_1 then just try

    \dt+ iso*

ref: http://www.postgresql.org/docs/current/static/app-psql.html#APP-PSQL-PATTERNS

edited Aug 10 '15 at 11:29

answered Aug 10 '15 at 11:11

Sahap Asci

3,174
1
17
28

Why can't I see my table (PostgreSQL) when I use \dt(+) inside psql?

2 Answers2