How to List Tables in PostgreSQL

psql

\dt is a psql meta-command rather than SQL, so it only works in psql and fails in any other client. It lists tables in the current search_path; add a schema pattern such as archive.* to look elsewhere, or \dt *.* for everything. \dt+ adds size and description columns.

\dt
\dt archive.*

Plain SQL (works from any client)

pg_tables works from any client, including GUI tools and application code. The quote_ident call matters: without it, a table name that is mixed case or contains a special character breaks the size lookup. Change schemaname to target a different schema, or drop the filter and add it to the SELECT to cover the whole database.

SELECT tablename,
       pg_size_pretty(pg_total_relation_size(quote_ident(tablename))) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(quote_ident(tablename)) DESC;

pg_tables, information_schema and \dt list different things

pg_tables shows every table in the cluster's current database whether or not you can read it. information_schema.tables shows only what your role has some privilege on, which is why it can come back shorter, and it includes views alongside tables unless you filter on table_type. pg_class is the one to reach for when you also want row estimates or sizes without scanning anything.

SELECT relname,
       reltuples::bigint AS estimated_rows,
       pg_size_pretty(pg_total_relation_size(oid)) AS size
FROM pg_class
WHERE relkind = 'r' AND relnamespace = 'public'::regnamespace
ORDER BY pg_total_relation_size(oid) DESC;

Common errors

\dt *.*

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

Do more with PostgreSQL

• Export Postgres to Google Sheets on a schedule
• Postgres SQL editor in the browser