List Tables in a Database Postgres: \dt, pg_tables, and information_schema

List tables in a database Postgres with \dt in psql, or query pg_catalog.pg_tables / information_schema.tables from any client. The team that uses \dt schema.* filters by schema. The team that uses \d (no t) shows all relations including views and sequences.

Table of contents

• The psql \dt meta-command
• \d for all relations
• Filter by schema
• SQL: pg_catalog
• SQL: information_schema
• List views separately
• Count rows in tables
• FAQ

The psql \dt meta-command

After connecting with psql -d dbname:

dbname=# \dt

Shows tables in the current schema (default public). The team that uses \dt for quick lookups has the right tool.

\d for all relations

dbname=# \d

Shows tables, views, sequences, indexes, foreign tables. The team that uses \d for overview has the full picture.

Filter by schema

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

The team that uses schema filters finds tables in specific namespaces.

SQL: pg_catalog

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

PostgreSQL-native. Fast, uses system catalogs.

SQL: information_schema

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

Standard SQL. Portable across databases.

List views separately

dbname=# \dv

Or SQL:

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

The team that distinguishes tables from views uses \dv.

Count rows in tables

SELECT schemaname || '.' || tablename AS table_name,
       n_live_tup AS row_count
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 for accurate counts.

FAQ

How do I list tables in a non-public schema?

\dt myschema.* or \dt *.* for all schemas.

How do I list tables and their sizes?

Use the query from the ‘count rows’ section, joining with pg_total_relation_size.

Can I see when each table was last modified?

Yes via pg_stat_user_tables (last_analyze, last_autoanalyze times). The team that monitors table activity uses this.

How do I list tables that match a name pattern?

\dt *user* matches all tables containing ‘user’.

What’s the difference between pg_tables and information_schema.tables?

pg_tables: PostgreSQL-specific, fast. information_schema.tables: SQL standard, portable. Both work; the team that picks based on portability vs speed has the right choice.

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:

• List Of Users In Postgres
• Postgres List Users
• Database/Introduction