How to List Tables in PostgreSQL (psql and SQL)

PostgreSQL tables belong to a database and a schema. First make sure psql is connected to the database you want to inspect; table-listing commands do not search other databases.

List tables from the command line

To list user tables across schemas without opening an interactive psql prompt, run:

psql --username=myuser --dbname=mydatabase --command='\dt *.*'

Replace the user and database names for your connection. --command accepts a single SQL command or a single psql backslash command, then exits; it cannot combine both in one argument. See PostgreSQL’s psql command-line options.

List tables visible in the search path

Connect with an account that can access the database:

psql --username=myuser --dbname=mydatabase

At the psql prompt, list tables visible in the current search_path (often the public schema):

\dt

\dt is a psql meta-command, not SQL, so enter it without a semicolon. To inspect a particular schema, include its name:

\dt public.*

To list user-created tables in all schemas of the current database, use *.*:

\dt *.*

By default, psql hides system objects. Add the S modifier, as in \dtS *.*, to include system tables.

For extra details such as size and description, add +:

\dt+ public.*

To list views or materialized views instead, use \dv or \dm. For equivalent commands and catalog queries across six engines, see List Views in SQL.

List tables with SQL

Query the PostgreSQL system view pg_catalog.pg_tables to list tables across schemas, excluding system schemas:

SELECT schemaname, tablename
FROM pg_catalog.pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, tablename;

The SQL-standard information_schema.tables view is another option. It shows tables and views the current user has privileges to access; filter table_type to list only base tables:

SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
  AND table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY table_schema, table_name;

Troubleshooting

• If \dt shows no rows, check which database and schema search path you are using:

  SELECT current_database(), current_schema();
  SHOW search_path;
  

• To list a table in a schema that is not visible through the current search path, use \dt schema_name.* or query pg_catalog.pg_tables.

• The information_schema.tables view omits tables the current user cannot access. Check the account’s privileges if an expected table is missing.

• To describe one table in psql, use \d+ schema_name.table_name.

See the PostgreSQL documentation for psql table-listing commands and the pg_tables view.

For equivalent commands in other systems, see how to list tables in SQL databases.