How to list tables in PostgreSQL

Quick Answer

Connect to the database, then run \dt in psql:

\c myapp
\dt

From SQL, use pg_tables:

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

List tables in psql

\dt lists tables in the schemas on your search_path, which by default means public. Tables only exist inside a database, so connect to the right one first with \c. If you're still in the default postgres database, \dt just says Did not find any relations.

myapp=# \dt
           List of relations
 Schema |   Name    | Type  |  Owner
--------+-----------+-------+----------
 public | Invoices  | table | postgres
 public | customers | table | postgres
(2 rows)

List tables with a SQL query

Meta-commands like \dt only work in psql. In application code, pgAdmin, DBeaver or any other client, query a catalog instead. There are two to choose from, and they don't return the same rows:

-- pg_tables: every table in the database
SELECT schemaname, tablename
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY 1, 2;

-- information_schema: standard SQL, works on MySQL and SQL Server too
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 1, 2;

To include views, drop the table_type filter, or select table_type so you can tell them apart ('BASE TABLE' vs 'VIEW').

List tables in one schema

\dn          -- list schemas first
\dt sales.*  -- then the tables in one of them

SELECT tablename
FROM pg_tables
WHERE schemaname = 'sales'
ORDER BY tablename;

If you work in one schema all the time, put it on your search path, and plain \dt will show its tables:

SET search_path TO sales, public;

List tables with size and row count

\dt+ shows the size of each table on its own. The query below also counts indexes and TOAST data, sorts by size and adds the planner's row estimate, which is instant even on huge tables:

SELECT n.nspname                                  AS schema,
       c.relname                                  AS table,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
       c.reltuples::bigint                        AS estimated_rows
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'p')   -- ordinary and partitioned tables
  AND NOT c.relispartition       -- count a partitioned table once
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(c.oid) DESC;

pg_total_relation_size of a partitioned parent is 0, because the data lives in the partitions. Drop the relispartition line to see each partition's size. For -1 row estimates, see above.

Why a table is missing from the list

• Wrong database. Check with SELECT current_database(); and switch with \c.
• It's in another schema. \dt only searches the search_path. Try \dt *.*name*.
• It's a view, materialized view or foreign table, or it's hidden by a same-named table earlier on the search path. See the comparison table above. \dE lists foreign tables.
• You don't have privileges on it, and you used information_schema. Query pg_tables instead.

Names with capital letters ("Invoices" above) sort before lowercase ones and must be double-quoted in queries. See relation does not exist.

Frequently Asked Questions

What is the PostgreSQL equivalent of SHOW TABLES?

In psql it is \dt. In SQL, query pg_tables or information_schema.tables. PostgreSQL has no SHOW TABLES statement; SHOW reads configuration settings.

How do I list all tables in all schemas?

Run \dt *.* in psql, or query pg_tables without a schema filter. Exclude pg_catalog and information_schema to hide the system tables.

Why does \dt say "Did not find any relations"?

You are probably connected to a database with no tables in your search_path, often the default postgres database. Connect with \c yourdb, or look in other schemas with \dt *.*.

Does \dt show views?

No. Use \dv for views, or \dtv for tables and views together.

How do I see the columns of a table?

Run \d tablename in psql, or query information_schema.columns for that table from SQL.

Related Guides

• relation does not exist