\dt says "Did not find any relations."

Question

I've been using Postgres 14 successfully, but now I'm stumped. I have a database and schema abc which has tables.

\c abc
\dt     -- shows tables

I made a new database/schema xyz, but I cannot see the tables I made:

\c xyc
\dt
Did not find any relations.

If I use PgAdmin4 I can see xyz's tables. If I use DbVisualizer I can see xyz's tables. But if I use psql all I get is Did not find any relations. It doesn't seem to matter if I am user postgres or myself (also superuser) in psql. I created those xyz tables in PgAdmin4. Was that a mistake? I've tried changing the owner of all the objects in the database or schema xyz, but no matter what I try, I just can't "see" them if I'm using psql.

Not sure if this sheds any light:

xyz-# \conninfo
You are connected to database "xyz" as user "postgres" via socket in "/tmp" at port "5432".
xyz=# \l
                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 xyz       | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =Tc/postgres         +
           |          |          |            |            | postgres=CTc/postgres
 abc       | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 

Answer 1

I think you've answered your own question, but here are some observations.

• From the question, I suspect that the abc database had all of its tables created in the default public schema, while the xyz database has its tables created in the xyz schema. Since public is part of the default search path, psql could find abcs tables successfully while in xyz you had to modify search_path.

• I suggest reading the documentation on how databases, schemas, and tables fit together.

• \c does not connect to a schema, it connects to a database. This is an important distinction.

• search_path defines the order in which schemas are searched when accessing unqualified object names. Objects can always be schema-qualified (i.e, xyz.table1 will find table1 in schema xyz but table1 by itself will use search_path)

• \d and similar commands can discover objects using search_path, but it does not necessarily mean undiscovered objects are inaccessible. As you found, you can access objects using schema-qualified identifiers even if \d et. al. doesn't list them. Relevant quote from the psql manpage under Patterns (bold mine):

  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.

• SET commands are session-local and never saved.

• ALTER DATABASE .. SET search_path only affects new sessions, you would have to reconnect to see the updated path.

Answer 2

I saw this answer and it partially solved the problem I was having ("The relations appear to be missing because Postgres does not copy the search_path to the new database").

That is, I can see the tables like this:

xyz= \dt xyz.*

but not like this:

xyz= \dt

I tried setting the search path, currently:

"$user", public

to:

public, xyz

which is the way the other one looks (the one that works), but the setting didn't stick.

UPDATE This worked:

xyz=# set search_path to xyz,public;
SET
xyz=# show search_path;
 search_path 
-------------
 xyz, public
(1 row)

xyz=# \dt
       List of relations
 Schema |        Name        | Type  |  Owner  
--------+--------------------+-------+---------
 xyz    | aircraft           | table | myname
 xyz    | pilot_availability | table | myname
 xyz    | pilots             | table | myname
 xyz    | sorties            | table | myname
 ...    | ...                | table | myname

Unfortunately, it appears setting the search_path does not stick. I have to set it every time I start psql, for this xyz database/schema.

But this seems to have made the setting stick:

alter database xyz set search_path = xyz, public;