look up table names in PSQL

Question

My DB has a lot of tables (Say 400+), and I only remember part of the name of the one I am looking for.

I know \d would show all the tables, but that's too much to look at. Is there some command to list all the tables whose names match the given regex?

Thanks

Answer 1

It's built in to psql, you can use wildcards in \d, \dt, etc, eg:

craig=> \dt test*
         List of relations
 Schema |   Name    | Type  | Owner 
--------+-----------+-------+-------
 public | test      | table | craig
 public | testtable | table | craig
 public | testu     | table | craig
 public | testx     | table | craig
(4 rows)

You'll want to use \dt since \d will display details for each table, not just list the table.

You can do this with schemas too, eg:

\dt *.sometable

will list all tables named sometable in any schema.

Much more convenient than writing queries against pg_class joined to pg_namespace, or querying information_schema.

The usual globbing syntax is accepted, where ? is any single character and * is zero or more characters. So \dt ???? would list all tables with four-character names.

Multiple wildcards are permitted, eg:

craig=> \dt public.*e?t*
           List of relations
 Schema |     Name     | Type  | Owner 
--------+--------------+-------+-------
 public | exclude_test | table | craig
 public | prep_test    | table | craig
 public | test         | table | craig
 public | testtable    | table | craig
 public | testu        | table | craig
 public | testx        | table | craig
(6 rows)