PostgreSQL SHOW TABLES / DESCRIBE TABLE (psql + Supabase)

The one-line answer#

There is no SHOW TABLES in PostgreSQL, and there is no DESCRIBE TABLE either — both are MySQL/Oracle commands. The psql client gives you \dt to list tables and \d table_name to describe one; every other client gives you information_schema.tables and information_schema.columns. That is the whole article in four commands:

-- psql: list tables
\dt
 
-- psql: describe a single table
\d users
 
-- any client: list tables (Supabase SQL editor, Drizzle Studio, DataGrip, pgAdmin)
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name;
 
-- any client: describe a single table
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'users'
ORDER BY ordinal_position;

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:

-- 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.

\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.

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.

DESCRIBE TABLE in PostgreSQL: \d in psql, information_schema.columns everywhere else#

If you came from MySQL, you typed DESCRIBE users and PostgreSQL answered with a syntax error:

postgres=# DESCRIBE users;
ERROR:  syntax error at or near "DESCRIBE"
LINE 1: DESCRIBE users;
        ^

DESCRIBE (and DESC) is a MySQL/Oracle command. PostgreSQL has nothing called DESCRIBE TABLE. The canonical Stack Overflow answer (Q109325, ~1.7M views) is one line: in psql, use \d table_name.

\d table_name — the psql way to describe a table#

postgres=# \d users
                       Table "public.users"
   Column   |           Type           | Collation | Nullable | Default
------------+--------------------------+-----------+----------+---------
 id         | uuid                     |           | not null |
 email      | text                     |           | not null |
 created_at | timestamp with time zone |           | not null | now()
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_email_key" UNIQUE, btree (email)
Referenced by:
    TABLE "posts" CONSTRAINT "posts_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)

\d shows columns with types and nullability, indexes, and foreign-key references — the MySQL DESCRIBE table; SHOW CREATE TABLE users; SHOW INDEX FROM users; triple, in one command. The + variant (\d+ users) adds storage details (pg_size_pretty), the table persistence type, and the table comment:

postgres=# \d+ users

information_schema.columns — the portable way (Supabase, Drizzle, pgAdmin)#

information_schema.columns is the SQL-standard way and the only way to describe a table outside psql:

SELECT column_name,
       data_type,
       character_maximum_length,
       is_nullable,
       column_default
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name   = 'users'
ORDER BY ordinal_position;

For the indexes that DESCRIBE does not show, join against information_schema.statistics or query the system catalog pg_indexes:

SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public'
  AND tablename  = 'users';

For foreign keys (the REFERENCED BY block in psql), query information_schema.table_constraints filtered on constraint_type = 'FOREIGN KEY'. This is the same approach used by the PostgreSQL DESCRIBE TABLE equivalent article, which goes deeper on the pg_catalog side.

When you actually want EXPLAIN, not DESCRIBE#

DESCRIBE users answers "what columns does this table have". If you meant "how is this query going to run", you want EXPLAIN (and EXPLAIN ANALYZE to get the actual plan with row counts). They are not interchangeable:

EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
 
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE email = '[email protected]';

EXPLAIN works in psql, the Supabase SQL editor, Drizzle Studio, every SQL client, and every ORM that lets you pass a raw query. There is no client-side restriction on it the way there is on \dt or \d.

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.

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.

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.
• How to Change a PostgreSQL User Password (Supabase)
• Postgres peer authentication failed fix — when "I cannot see my tables" is actually a role/auth problem.
• Fix: psql: command not found (Install PostgreSQL Client)
• How to Drop All Tables in PostgreSQL Safely (2026)
• How to Exit psql: \q, exit, quit and Ctrl+D