Why Can’t I See My Table in PostgreSQL Using \dt(+) in psql?

postgresqlpsqlschematable

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

Best Answer

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)

Related Solutions

Postgresql – How to list all postgres tables in one particular schema

\dt schemaname.* will do what you want.

Postgres \copy Command – Accessing Temporary Tables

\copy can use a temporary table.

First I tested and confirmed this with version 9.0 at the command line.
Then I created a file with SQL and psql meta command \copy using multiple temporary tables. That worked for me, too.

CREATE TEMP TABLE tmp as SELECT * FROM tbl;
\copy (SELECT * FROM tmp JOIN tbl USING (id)) TO '/var/lib/postgres/test1.csv';

Call:

psql -p5432 mydb -f test.sql

Note the terminating semicolon, which is optional at the end of a file (terminated implicitly), but required after any other SQL statement and also after the last one if executed in psql interactively.

Normally, psql meta-commands cannot be mixed with SQL on the same line in a file executed per psql -f. I quote the manual on psql:

Parsing for arguments stops at the end of the line, or when another unquoted backslash is found. An unquoted backslash is taken as the beginning of a new meta-command. The special sequence \\ (two backslashes) marks the end of arguments and continues parsing SQL commands, if any. That way SQL and psql commands can be freely mixed on a line. But in any case, the arguments of a meta-command cannot continue beyond the end of the line.

Different rules apply after \copy, though. Essentially, psql switches back to SQL mode automatically after \copy See:

How to update selected rows with values from a CSV file in Postgres?

But you wrote you had all commands on separate lines. So that cannot be the explanation in your case.

All that aside, have you considered using COPY (the SQL command) instead of \copy (the psql meta-command)?

Of course, the target file would have to be local to the server not the client in this case. And different file privileges apply. The manual:

Files named in a COPY command are read or written directly by the server, not by the client application. Therefore, they must reside on or be accessible to the database server machine, not the client. They must be accessible to and readable or writable by the PostgreSQL user (the user ID the server runs as), not the client.

Best Answer

Related Solutions

Postgresql – How to list all postgres tables in one particular schema

Postgres \copy Command – Accessing Temporary Tables

Related Question