PostgreSQL Show Tables: \dt, pg_catalog, and information

Q: How do I list tables across schemas?

`\dt *.*` lists tables in all schemas. Filter with `\dt schema_name.*` or `\dt *pattern*` for name patterns.

Q: How do I list tables with their row counts?

`SELECT relname, n_live_tup FROM pg_stat_user_tables WHERE schemaname='public' ORDER BY n_live_tup DESC;`. The team that uses `pg_stat_user_tables` needs `pg_stat_statements` extension enabled for accurate stats.

Q: Why doesn't \dt show tables in my custom schema?

`\dt` shows tables in the current schema (default `public`). Use `\dt myschema.*` or set `search_path` to include your schema.

Show tables in PostgreSQL with psql meta-commands (\dt, \d, \dt schema.*) or SQL queries against pg_catalog / information_schema. The team that uses \d (no t) sees tables, views, sequences, and indexes. The team that uses SQL queries has the same info from any client without psql installed.

$PostgreSQL Show Tables: \\dt, pg_catalog, and information_schema$

The psql \dt meta-command
\dt schema filtering
\d shows more than \dt
SQL: query pg_catalog
SQL: query information_schema
Tables by size
Listing views
FAQ

The psql \dt meta-command

After connecting with psql -d dbname:

dbname=# \dt

Shows tables in the current schema (usually public). The team that wants tables across schemas uses \dt *.* or \dt schema_name.*.

\dt schema filtering

Filter by schema:

dbname=# \dt public.*
dbname=# \dt app.*

Filter by name pattern:

dbname=# \dt user_*
dbname=# \dt *_log

The team that uses patterns finds tables matching partial names. The team that uses exact names only finds exact matches.

\d shows more than \dt

\d (without t) shows all relations - tables, views, materialized views, sequences, indexes, foreign tables:

dbname=# \d user

Shows the structure of user (columns, types, indexes, constraints). The team that uses \d table_name is debugging schema; the team that uses \dt is listing tables.

SQL: query pg_catalog

For programmatic access:

SELECT tablename FROM pg_catalog.pg_tables
WHERE schemaname = 'public';

The team that uses pg_catalog.pg_tables has schema-aware queries.

SQL: query information_schema

The portable standard:

SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public' AND table_type = 'BASE TABLE';

The team that uses information_schema has SQL that works across PostgreSQL, MySQL (with caveats), and other databases. The team that uses pg_catalog has PostgreSQL-specific queries.

Tables by size

Find the largest tables:

SELECT
  schemaname || '.' || tablename AS table_name,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS size,
  pg_total_relation_size(schemaname || '.' || tablename) AS size_bytes
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;

The team that uses this finds unexpected large tables.

Listing views

dbname=# \dv

Or SQL:

SELECT table_name FROM information_schema.views WHERE table_schema = 'public';

The team that distinguishes tables from views uses \dv or filters by table_type.

FAQ

What’s the difference between \dt and \d?

\dt lists tables (filtered to type=‘table’). \d lists all relations (tables, views, sequences, indexes). Use \d table_name to describe a specific table’s structure.

How do I list tables across schemas?

\dt *.* lists tables in all schemas. Filter with \dt schema_name.* or \dt *pattern* for name patterns.

Can I show tables from a non-superuser role?

Yes, but only tables the role has SELECT or other privileges on. \dt filters by visibility based on the current user’s permissions.

How do I list tables with their row counts?

SELECT relname, n_live_tup FROM pg_stat_user_tables WHERE schemaname='public' ORDER BY n_live_tup DESC;. The team that uses pg_stat_user_tables needs pg_stat_statements extension enabled for accurate stats.

Why doesn’t \dt show tables in my custom schema?

\dt shows tables in the current schema (default public). Use \dt myschema.* or set search_path to include your schema.

If you are sizing the infrastructure for the kind of project this post covers, the RunxBuild hosting calculator is the right place to model the line items. The compute, the memory, the storage, the bandwidth, the database - each one is a separate number, and the team’s mental model for the platform is the sum of those numbers. The RunxBuild dashboard is where the team sees the actual usage in one place.

Useful related references:

PostgreSQL Show Tables: \dt, pg_catalog, and information_schema

Table of contents