How to list all postgres tables in one particular schema

Question

Using the psql command line tool, how do I list all postgres tables in one particular schema

Answer 1

\dt schemaname.* will do what you want.

Answer 2

In addition to the \dt match, you can also look into the database catalog:

SELECT nspname||'.'||relname AS full_rel_name
  FROM pg_class, pg_namespace
 WHERE relnamespace = pg_namespace.oid
   AND nspname = 'yourschemaname'
   AND relkind = 'r';

You can also do it with the more standard information schema, but it tends to be slower:

SELECT table_schema||'.'||table_name AS full_rel_name
  FROM information_schema.tables
 WHERE table_schema = 'yourschemaname';

Answer 3

Here is the approach how it works for me on 11.2:

List all schemas:

\dn

Show tables of specific schema (example shows public):

\dt public.*

Show tables of all schemas:

\dt *.*

Finally show tables of selected schemas (here public and custom 'acl' schemas):

\dt (public|acl).*

Note, if no tables are found it will warn you but if some schemas do not have relations or do not exist at all they are just ignored (this is good for me, not sure if it is desired effect for you).

Answer 4

you can run

SELECT * FROM pg_catalog.pg_tables where schemaname="yourschemaname";