Psql list all tables

Question

I would like to list all tables in the liferay database in my PostgreSQL install. How do I do that?

I would like to execute SELECT * FROM applications; in the liferay database. applications is a table in my liferay db. How is this done?

Here's a list of all my databases:

postgres=# \list
                              List of databases
Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
 -----------+----------+----------+-------------+-------------+-----------------------
 liferay   | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres+
           |          |          |             |             | liferay=CTc/postgres
 lportal   | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | 
 postgres  | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | 
 template0 | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(5 rows)

postgres=#

Craig Ringer · Accepted Answer · 2013-12-29T08:29:13.400

If you wish to list all tables, you must use:

\dt *.*

to indicate that you want all tables in all schemas. This will include tables in pg_catalog, the system tables, and those in information_schema. There's no built-in way to say "all tables in all user-defined schemas"; you can, however, set your search_path to a list of all schemas of interest before running \dt.

You may want to do this programmatically, in which case psql backslash-commands won't do the job. This is where the INFORMATION_SCHEMA comes to the rescue. To list tables:

SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

BTW, if you ever want to see what psql is doing in response to a backslash command, run psql with the -E flag. eg:

$ psql -E regress    
regress=# \list
********* QUERY **********
SELECT d.datname as "Name",
       pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
       pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
       d.datcollate as "Collate",
       d.datctype as "Ctype",
       pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
**************************

so you can see that psql is searching pg_catalog.pg_database when it gets a list of databases. Similarly, for tables within a given database:

SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

It's preferable to use the SQL-standard, portable INFORMATION_SCHEMA instead of the Pg system catalogs where possible, but sometimes you need Pg-specific information. In those cases it's fine to query the system catalogs directly, and psql -E can be a helpful guide for how to do so.

`information_schema.tables` includes views for some reason. (In PostgreSQL 9.2, anyway.) — jpmc26, Jan 17 '14 at 17:03
@jpmc26 Yes, with `table_type = 'VIEW'`, so they're easy to exclude. In general SQL tries to treat views the same as tables as much as possible. — Craig Ringer, Jan 18 '14 at 01:05

score 97 · Answer 2 · answered Sep 16 '12 at 09:03

97

Connect to the database, then list the tables:

\c liferay
\dt

That's how I do it anyway.

You can combine those two commands onto a single line, if you prefer:

\c liferay \dt

answered Sep 16 '12 at 09:03

d11wtq

34,788
19
120
195

2

You actually want `\dt *.*` if not all tables of interest are on the `search_path`. – Craig Ringer Dec 29 '13 at 08:29

score 15 · Answer 3 · answered Jul 19 '16 at 05:09

15

To see the public tables you can do

list tables

\dt

list table, view, and access privileges

\dp or \z

or just the table names

select table_name from information_schema.tables where table_schema = 'public';

answered Jul 19 '16 at 05:09

nikkypx

1,925
17
12

score 5 · Answer 4 · answered Aug 26 '16 at 13:43

In SQL Query, you can write this code:

select table_name from information_schema.tables where table_schema='YOUR_TABLE_SCHEME';

Replace your table scheme with YOUR_TABLE_SCHEME;

Example:

select table_name from information_schema.tables where table_schema='eLearningProject';

To see all scheme and all tables, there is no need of where clause:

select table_name from information_schema.tables

score 2 · Answer 5 · edited Jan 27 '22 at 15:00

A one-line example is:

\dt schemaname.*

In your scenario:

\dt public.*

And to get a list of schemas:

\dn

Note that public is the default schema when none is specified. Quoting the documentation:

In the previous sections we created tables without specifying any schema names. By default such tables (and other objects) are automatically put into a schema named “public”. Every new database contains such a schema.

Using \dt *.* will output a long list of all tables in all schemas, including internal ones such as pg_catalog. The above can help with filtering.

score 1 · Answer 6 · answered Sep 20 '17 at 11:42

1

This can be used in automation scripts if you don't need all tables in all schemas:

  for table in $(psql -qAntc '\dt' | cut -d\| -f2); do
      ...
  done

answered Sep 20 '17 at 11:42

danbst

3,363
18
38

Psql list all tables

6 Answers6

Linked