22

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

4 Answers 4

27

\dt schemaname.* will do what you want.

7

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';
4

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

1
  • I often want to work within one particular schema, and just doing \dt lists nothing, and I keep having to specify the schema name everywhere. Is there some way of saying something like use schema so that it defaults, and even \dt will then list from that schema? ... yes there is!: set search_path to <schema-name>;.
    – NeilG
    May 10, 2022 at 10:56
2

you can run

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

Your Answer

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