How to list all tables in psql?

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=# 

Answer 1

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.

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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.

Answer 7

First, connect to liferay database:

\c liferay

Now, these list all tables of all schemas of liferay database:

\dt *.*

\dtS *.*

And, these list all tables of all schemas of liferay database in detail:

\dt+ *.*

\dtS *.*

And, these list all tables of pg_catalog and public schemas of liferay database:

\dtS

\dtS *

\dt *

And, these list all tables of pg_catalog and public schemas of liferay database in detail:

\dtS+

\dtS+ *

\dt+ *

And, this lists all tables of public schema of liferay database:

\dt

And, this lists all tables of public schema of liferay database in detail:

\dt+

And, these list all tables of my_schema schema of liferay database:

\dtS my_schema.*

\dt my_schema.*

Lastly, these list all tables of my_schema schema of liferay database in detail:

\dtS+ my_schema.*

\dt+ my_schema.*