List tables in a PostgreSQL schema

Question

When I do a \dt in psql I only get a listing of tables in the current schema (public by default).

How can I get a list of all tables in all schemas or a particular schema?

score 781 · Accepted Answer · edited Nov 14 '19 at 15:49

781

In all schemas:

=> \dt *.*

In a particular schema:

=> \dt public.*

It is possible to use regular expressions with some restrictions

\dt (public|s).(s|t)
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 public | s    | table | cpn
 public | t    | table | cpn
 s      | t    | table | cpn

Advanced users can use regular-expression notations such as character classes, for example [0-9] to match any digit. All regular expression special characters work as specified in Section 9.7.3, except for . which is taken as a separator as mentioned above, * which is translated to the regular-expression notation .*, ? which is translated to ., and $ which is matched literally. You can emulate these pattern characters at need by writing ? for ., (R+|) for R*, or (R|) for R?. $ is not needed as a regular-expression character since the pattern must match the whole name, unlike the usual interpretation of regular expressions (in other words, $ is automatically appended to your pattern). Write * at the beginning and/or end if you don't wish the pattern to be anchored. Note that within double quotes, all regular expression special characters lose their special meanings and are matched literally. Also, the regular expression special characters are matched literally in operator name patterns (i.e., the argument of \do).

edited Nov 14 '19 at 15:49

Randall

2,859
1
21
24

answered Mar 26 '13 at 18:40

Clodoaldo Neto

118,695
26
233
260

12

Simply `\dt` is equivolent to `\dt public.*`, am I right? – Frozen Flame Aug 25 '15 at 12:09
How about, say, two specific tables in a specific schema? Like `\dt public.user_info, public.user_scope`? – James M. Lay Nov 11 '15 at 03:53
Nevermind, it's easier just to do `\dt public.a; \dt public.b;` on one line. – James M. Lay Nov 11 '15 at 03:54
it is kind of implicit.. If \dt gives only "public" tables, one would not expect anything more via regular expressions.. – mehmet Sep 18 '18 at 21:35
And if your table name contains capital letters you have to quote the schema name like this `\dt "MySchema".*` – Catfish May 20 '19 at 20:44
7

@FrozenFlame It is not! By default it shows whatever is in your `search_path`, and _that_ defaults to `"$user", public.*`. Consequently, `set search_path=s; \dt` is going to list all tables in the schema `s`. – Lukas Juhrich Jan 25 '20 at 15:32
This does not list tables of **foreign imported schemas**: *"No matching relations found."*. (See [postgres docs](https://www.postgresql.org/docs/current/sql-importforeignschema.html)). The [answer](https://stackoverflow.com/a/15644435/2414736) of @jakub-kania is working. – Philipp Gächter Aug 23 '23 at 10:26

score 387 · Answer 2 · answered Mar 26 '13 at 18:12

387

You can select the tables from information_schema

SELECT * FROM information_schema.tables 
WHERE table_schema = 'public'

answered Mar 26 '13 at 18:12

Jakub Kania

15,665
2
37
47

17

very helpful if your interface doesn't support the shortcuts. thanks. – Matt Bannert Jan 20 '14 at 17:08
1

This is also nice because you could do something like select table_schema, table_name from information_schema.tables where table_name like '%whatever%'; if you need to know which schema the table is located in. Not sure you can do that with \dt – Josh Brown Sep 29 '15 at 21:07
3

Thank you, it works on Amazon Redshift and \dt (accepted answer) doesn't. – Carlos2W Nov 10 '15 at 15:53
2

This is the most generally useful answer. information_schema is defined in SQL Standards, and available on most databases that comply – Davos Nov 10 '17 at 14:54
2

To exclude views, put `AND table_type = 'BASE TABLE'` to the where clause. – SzieberthAdam Jul 14 '21 at 07:52
The ACTUAL answer... – ataraxia Jan 20 '23 at 11:04
This also lists tables of **foreign imported schemas**! (See [postgres docs](https://www.postgresql.org/docs/current/sql-importforeignschema.html)). Therefore the **actual and always working** answer. (`\dt` and `pg_tables` don't) – Philipp Gächter Aug 23 '23 at 10:30

score 89 · Answer 3 · answered May 28 '15 at 14:39

89

Alternatively to information_schema it is possible to use pg_tables:

select * from pg_tables where schemaname='public';

answered May 28 '15 at 14:39

Radek Postołowicz

4,506
2
30
47

5

note that if you only want the table name is the resultant query it is `SELECT tablename FROM pg_tables WHERE schemaname = 'public';` – Grant Humphries Feb 14 '16 at 23:22
Found a permissions issue with `information_schema` not listing items from the `public` schema, but the `pg_tables` method worked nicely. Many thanks! – John Crawford Jan 08 '18 at 22:58
This does not list tables of **foreign imported schemas**. (See [postgres docs](https://www.postgresql.org/docs/current/sql-importforeignschema.html)). The [answer](https://stackoverflow.com/a/15644435/2414736) of @jakub-kania is working. – Philipp Gächter Aug 23 '23 at 10:29

score 15 · Answer 4 · edited Feb 01 '18 at 02:20

For those coming across this in the future:

If you would like to see a list of relations for several schemas:

$psql mydatabase
mydatabase=# SET search_path TO public, usa;   #schema examples
SET
mydatabase=# \dt
              List of relations
 Schema |      Name       | Type  |  Owner
--------+-----------------+-------+----------
 public | counties        | table | postgres
 public | spatial_ref_sys | table | postgres
 public | states          | table | postgres
 public | us_cities       | table | postgres
 usa    | census2010      | table | postgres

score 11 · Answer 5 · answered Mar 29 '21 at 09:22

11

If you are interested in listing all tables in a particular schema, I found this answer relevant :

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

answered Mar 29 '21 at 09:22

linog

5,786
3
14
28

2

To exclude views, put `AND table_type = 'BASE TABLE'` to the where clause. – SzieberthAdam Jul 14 '21 at 07:52

score 0 · Answer 6 · answered Jun 28 '23 at 13:15

All of the previous answers has covered the public schemas. However, as Postgres supports multiple schemas, you can do query in other schemas too, just put your schema name in place of public.

For Example:

select * from pg_tables where schemaname='your_own_schema_name';

List tables in a PostgreSQL schema

6 Answers6

Linked