How to Show Tables in PostgreSQL (psql + Supabase)

The one-line answer

There is no SHOW TABLES in PostgreSQL. The psql client gives you \dt;
every other client gives you information_schema.tables. That is the whole
article in two commands:

-- psql
\dt

-- any client (Supabase SQL editor, Drizzle Studio, DataGrip, pgAdmin)
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name;

The rest of this article is the part that costs you an hour the first time:
schema filtering, sizes, system tables, Supabase specifics, and the three
reasons your query returns zero rows.

Method 1: \dt in psql

\dt is a psql meta-command, not SQL. It only works inside the psql
client. The official PostgreSQL psql documentation documents it
under the relation-listing group:

Command	What it lists
\dt	User tables in the current schema search path
\dt+	Same, plus on-disk size, persistence, description
\dtS	Include system tables (the S means "system")
\dt schema.*	Tables in a specific schema
\dt public.users	A specific table, if it exists

-- List your tables
\dt

-- List with size and description
\dt+

-- List only tables in the "auth" schema
\dt auth.*

-- Include system tables (rarely what you want)
\dtS

The single most common mistake: running \dt in the Supabase SQL
editor, Drizzle Studio, or pgAdmin and getting a syntax error. Backslash
commands are psql-only — they are interpreted by the psql client before
the query ever reaches the server. Any client that is not psql will send
\dt to PostgreSQL as SQL, where it is a syntax error.

Illustration — not a live capture. Example only; the column structure and table sample are quoted from the official PostgreSQL psql documentation and a typical Supabase-style schema. The orange banner inside the image is the renderer-level "not a live capture" badge.

\d vs \dt

\d without arguments lists every relation — tables, views, materialized
views, sequences, and foreign tables. It is the same as \dtvmsE. If you
only want tables, use \dt. If you want a single relation's schema (the
MySQL DESCRIBE table equivalent), use \d table_name — covered in the
PostgreSQL DESCRIBE TABLE equivalent.

Method 2: information_schema.tables (portable SQL)

This is the query that works everywhere — psql, Supabase SQL editor, Drizzle
Studio, DataGrip, pgAdmin, any driver. It is the SQL-standard way and the
only way in the Supabase SQL editor.

-- All tables in the public schema
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
  AND table_type = 'BASE TABLE'
ORDER BY table_name;

The table_type = 'BASE TABLE' filter excludes views. Without it you get
both tables and views in the result.

Why you must filter by schema

information_schema.tables returns every table the server knows about,
including the system catalogs. Run this without a filter:

SELECT table_schema, table_name
FROM information_schema.tables
ORDER BY table_schema, table_name;

You will get hundreds of rows from pg_catalog (the system catalog) and
information_schema itself. Those are not your tables. Always filter on
table_schema unless you genuinely want the system schemas:

-- Only your app tables (typical)
WHERE table_schema = 'public'

-- Multiple app schemas
WHERE table_schema IN ('public', 'auth', 'storage')

-- Everything except system schemas
WHERE table_schema NOT IN ('pg_catalog', 'information_schema', 'pg_toast')

List all tables across all schemas (excluding system schemas)

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

This is the query to reach for when you do not know which schemas your app
uses — for example, a Supabase project with public, auth, storage,
and a custom billing schema.

Method 3: pg_class (the most granular)

information_schema is the SQL standard, but it does not expose physical
metadata like on-disk size or row estimates. For that, query pg_class
directly. This is the approach the PostgreSQL docs describe
through the system catalogs.

-- List all user tables with their schema
SELECT n.nspname AS schema_name,
       c.relname AS table_name
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY n.nspname, c.relname;

relkind = 'r' means "ordinary heap table". Other useful values: p for
partitioned tables, v for views, m for materialized views, S for
sequences. If you want partitioned tables too, use c.relkind IN ('r', 'p').

List tables with their on-disk size

information_schema.tables does not expose size. Use pg_class with
pg_total_relation_size():

SELECT n.nspname AS schema_name,
       c.relname AS table_name,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'p')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(c.oid) DESC;

pg_total_relation_size() includes indexes and TOAST data, not just the
heap. If you want only the table heap, use pg_table_size(); if you want
only the indexes, use pg_indexes_size().

Approximate row counts

information_schema.tables does not give row counts either, and COUNT(*)
on every table is too expensive on a large database. Use the planner's
reltuples estimate:

SELECT c.relname AS table_name,
       c.reltuples::bigint AS approximate_row_count
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname = 'public'
ORDER BY c.reltuples DESC;

reltuples is updated by ANALYZE and is an estimate — good enough for
"which table is the big one" diagnostics. For exact counts you must run
SELECT COUNT(*) per table, which is a sequential scan. The
how to get count in Supabase article
covers the Supabase client side of the same problem.

Method 4: Supabase specifics

Supabase runs PostgreSQL, so every method above works in the Supabase SQL
editor with one constraint: the SQL editor is not psql, so backslash
commands do not work. Use information_schema.tables or pg_class.

The Table Editor (zero-SQL path)

If you just want to see your tables in a browser, the Supabase Dashboard has
a Table Editor: Dashboard → Table Editor. It lists every table in every
schema your service role can see, with row counts and a quick-edit view. For
a developer pasting \dt into Google, the SQL editor query above is faster.

Illustration — not a live capture. Example only; the comparison is synthesized from the official psql documentation and the Supabase Table Editor documentation. The orange banner inside the image is the renderer-level "not a live capture" badge.

Listing tables with RLS enabled

A common Supabase question is not "which tables exist" but "which tables
have row-level security enabled". That is a pg_class join against
pg_namespace with a check on relrowsecurity:

SELECT n.nspname AS schema_name,
       c.relname AS table_name,
       c.relrowsecurity AS rls_enabled
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname = 'public'
ORDER BY c.relrowsecurity DESC, c.relname;

relrowsecurity is true when RLS is enabled. To go deeper — auditing the
actual policies, not just the flag — see the
Supabase RLS policy design patterns
guide and the debugging Supabase RLS issues
post.

Listing tables in a specific schema (auth, storage, realtime)

Supabase ships non-public schemas. To list tables in auth (Supabase
Auth's internal schema) or storage (Supabase Storage):

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'auth'
ORDER BY table_name;

Note: you will only see these schemas if your role has been granted access.
The anon and authenticated roles do not have SELECT on most of auth
and storage; run these queries as the postgres role or a service role.

Why your query returns zero rows

Three causes cover almost every "I ran the query and got nothing" report.

1. You are connected to the wrong database

information_schema.tables is per-database. If you have two Supabase
projects and your connection string points at project A, you will not see
project B's tables. Confirm with:

SELECT current_database();

2. The schema name is wrong or quoted differently

PostgreSQL folds unquoted identifiers to lowercase. A table created as
CREATE TABLE "Users" lives in public.Users (capital U), but
WHERE table_schema = 'public' AND table_name = 'users' returns nothing
because the actual name is Users. Query without the name filter first,
then look at the exact table_name value.

3. Your role does not have access

information_schema.tables only shows tables the current role has
privileges on. The anon role in Supabase sees very little; the postgres
role sees everything. If you run the query as anon and get an empty
result, connect as postgres or your service role and re-run. This is the
same privilege model that produces the
Postgres peer authentication failed
error when the OS user does not match the database role.

Performance: do not SELECT COUNT(*) to list tables

The most expensive mistake on a large database is looping over every table
and running SELECT COUNT(*) FROM table. Each count is a sequential scan;
on a 50M-row table that is seconds to minutes. For "show tables with row
counts", use the reltuples estimate from pg_class (Method 3 above). Run
ANALYZE first if the estimate is stale:

ANALYZE;

ANALYZE updates planner statistics, including reltuples, and is
read-only with respect to user data — it does not lock tables for long. For
the Supabase client equivalent (counting rows from the browser), see
how to get count in Supabase, which
covers the head: true trick that avoids fetching rows.

Production recommendations

• Use information_schema.tables for any non-psql client. It is the only portable way. Backslash commands will fail in the Supabase SQL editor, Drizzle Studio, and most ORMs.
• Always filter on table_schema. Without a filter you get hundreds of system tables that obscure your actual tables.
• Use pg_class for size and row estimates. information_schema.tables does not expose physical metadata; pg_class does.
• Run ANALYZE before trusting reltuples. The estimate is only as fresh as the last ANALYZE. On Supabase, the autovacuum default keeps it reasonably fresh, but a bulk import will leave it stale until the next run.
• For schema auditing, combine pg_class with pg_namespace and pg_description. That gives you table, schema, size, RLS flag, and comment in one query — the basis of any database design optimization review.

Listing views, materialized views, and sequences too

\dt lists only heap tables. Real schemas also contain views, materialized
views, and sequences — and "show me everything in this schema" is a
different question from "show me the tables". Each has its own psql
meta-command and its own relkind filter in pg_class.

Object type	psql command	relkind	information_schema view
Tables	\dt	r (heap) or p (partitioned)	information_schema.tables
Views	\dv	v	information_schema.views
Materialized views	\dm	m	(none — query pg_class)
Sequences	\ds	S	information_schema.sequences
Everything	\d	r,p,v,m,S,f	(no single view)

To list every relation in the public schema regardless of type, in any
client:

SELECT c.relkind AS kind,
       n.nspname AS schema_name,
       c.relname AS relation_name
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public'
  AND c.relkind IN ('r', 'p', 'v', 'm', 'S')
ORDER BY c.relkind, c.relname;

This is the query to run when you inherit a database and need to know what
is in it before you touch anything. Materialized views are particularly
easy to miss because they do not show up in \dt and have no row in
information_schema.tables with table_type = 'BASE TABLE' — they are a
distinct relkind = 'm' and only appear in pg_class.

Refreshing materialized views

A materialized view is a stored query result. It does not update
automatically; you refresh it with REFRESH MATERIALIZED VIEW
view_name;. If you are listing tables to find "why is this data stale", the
materialized view is the usual suspect. List them first with \dm in psql
or the relkind = 'm' query above, then check each one's last refresh time
through pg_stat_user_tables — there is no native "last refreshed" column,
so most teams track it themselves.

FAQ

Is \dt the same as SHOW TABLES?

No. SHOW TABLES is a MySQL statement; PostgreSQL does not have it. \dt
is a psql client command that lists tables. They serve the same purpose for
the user, but \dt never leaves the psql client — it is not SQL.

How do I list tables in a specific schema?

In psql: \dt schema_name.*. In any other client:
SELECT table_name FROM information_schema.tables WHERE table_schema = 'schema_name';.

How do I see table sizes in PostgreSQL?

\dt+ in psql, or query pg_class with pg_total_relation_size(c.oid).
information_schema.tables does not expose size.

How do I list only partitioned tables?

In pg_class, filter on relkind = 'p'. In information_schema.tables,
partitioned tables appear with table_type = 'BASE TABLE' — the view does
not distinguish partitioned from heap tables, which is a reason to use
pg_class for physical metadata.

Related

• PostgreSQL DESCRIBE TABLE: the psql \d equivalent — the sibling article for inspecting a single table's columns, types, and constraints.
• How to get count in Supabase — the Supabase client side of "how many rows", including the head: true trick.
• Supabase RLS policy design patterns — for auditing which tables have RLS and what the policies do.
• Supabase Postgres functions and triggers guide — for listing and inspecting functions and triggers alongside your tables.
• Next.js + Supabase database design optimization — where table listing fits into a schema review.
• Postgres peer authentication failed fix — when "I cannot see my tables" is actually a role/auth problem.

---

Originally published at https://www.iloveblogs.blog