PostgreSQL SHOW TABLES equivalent

Quick answer

In psql:

\dt

From SQL, which is what you need from an application or a GUI:

SELECT tablename
FROM pg_tables
WHERE schemaname = 'public';

SHOW TABLES is a MySQL command. PostgreSQL does not have it.

Why SHOW TABLES does not work

Type the MySQL command and PostgreSQL gives you something that looks unrelated to tables:

SHOW TABLES;

ERROR:  unrecognized configuration parameter "tables"

The reason is that SHOW is a real PostgreSQL command, it just does something different. It reads configuration parameters:

SHOW work_mem;

 work_mem
----------
 4MB
(1 row)

So PostgreSQL reads SHOW TABLES as a request for a setting called tables, does not find one, and says so. The syntax was fine. The parameter is what does not exist. SHOW DATABASES fails the same way, for the same reason.

Coming from MySQL

See PostgreSQL list databases for the SHOW DATABASES side.

Using \dt in psql

\dt

          List of relations
 Schema |   Name    | Type  |  Owner
--------+-----------+-------+----------
 public | customers | table | postgres
 public | orders    | table | postgres
 public | products  | table | postgres
(3 rows)

\dt shows tables in your current search_path, which by default means the public schema only. That is the single most common reason a table you know exists does not appear.

To see every schema:

\dt *.*

To see one schema:

\dt analytics.*

Add + for size and description:

\dt+

Related listing commands:

Listing tables with SQL

Backslash commands are a psql feature. They do not work from an application driver, a GUI query editor, or anything that is not psql. For those, query the catalog.

Using pg_tables

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

Excluding those two schemas is what separates your tables from PostgreSQL's own. Without the filter you get about sixty system tables.

Using information_schema

information_schema is the SQL standard version, so the same query works on other databases:

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;

table_type = 'BASE TABLE' excludes views. Drop it and you get both.

There is a catch worth knowing: information_schema only shows objects you have privileges on. pg_tables shows everything. If a table is missing from one and present in the other, that is why.

Tables in the current schema only

SELECT tablename FROM pg_tables WHERE schemaname = current_schema();

With row counts

Exact counts require scanning each table. For an estimate, the planner's statistics are free:

SELECT relname AS table_name, n_live_tup AS estimated_rows
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

These are estimates maintained by autovacuum, not exact numbers. For an exact count of one table, SELECT count(*) FROM mytable is the only honest answer.

With table sizes

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_total_relation_size includes indexes and TOAST data. pg_relation_size counts only the table itself, which is usually not the number you want.

Listing tables in a script

For shell scripts, use -A and -t to strip formatting:

psql -U postgres -d mydb -Atc \
  "SELECT tablename FROM pg_tables WHERE schemaname='public';"

customers
orders
products

In Docker:

docker exec -it postgres psql -U postgres -d mydb -c '\dt'

Checking if a table exists

SELECT EXISTS (
  SELECT 1 FROM pg_tables
  WHERE schemaname = 'public' AND tablename = 'customers'
);

to_regclass is shorter and returns NULL rather than an error when the table is absent:

SELECT to_regclass('public.customers');

 to_regclass
-------------
 customers
(1 row)

Common problems

My table is not listed

Almost always a schema issue. \dt only shows your search_path. Check where the table actually lives:

SELECT schemaname, tablename FROM pg_tables WHERE tablename = 'mytable';

Then either qualify it (analytics.mytable), or add the schema to your path (SET search_path TO analytics, public;).

\dt says "Did not find any relations"

You are connected to a database that has no tables in your search path. Confirm which database you are in with \conninfo, because connecting to the wrong database looks identical to having no tables. See connecting to a PostgreSQL database.

\dt does not work in my app

It is a psql meta-command, not SQL. Use the pg_tables or information_schema queries above.

Quick reference