16

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).

2 Answers 2

14

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)
2
  • OK, I get it. This is a follow up q: if I didn't know that there are two tables with the same name in two different schemas in a DB and wanted to see all the tables in all the schemas in this DB, is there a psql meta command that will display them all, irregardless of which schema is placed in the search_path first and without me knowing the table/schema names in advance? Or am I better of querying the information schema e.g.,: SELECT table_schema, table_name FROM information_schema.tables ORDER BY table_schema, table_name;?
    – dw8547
    Aug 10, 2015 at 14:23
  • @user4842454: The information schema has it's own caveats.. To see all tables (including the system catalogs) use \dt *.* as instructed in the quote. Aug 10, 2015 at 14:50
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

2
  • The question is why the reference | donor is not listed with the 2nd command. Aug 10, 2015 at 11:52
  • @SahapAsci: my main concern is about why \dt (or \dt+) does not list the reference.donor table. All is OK as per the reference.iso_3166_1 table.
    – dw8547
    Aug 10, 2015 at 12:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.