PostgreSQL List Tables

Quick answer

To list all tables in the current database using psql:

\dt

Or with SQL:

SELECT tablename FROM pg_tables WHERE schemaname = 'public';

Both give you the tables in the public schema. The rest of this guide covers every variation, from psql shortcuts to detailed queries that include sizes, row counts, and column information.

The \dt command in psql

The \dt meta-command is the fastest way to list tables when you're working in the psql terminal.

Basic table listing

\dt

Output:

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

This shows tables in schemas on your search path (usually public).

Show table sizes with \dt+

Adding + includes size and description columns:

\dt+

Output:

                                    List of relations
 Schema |   Name    | Type  | Owner | Persistence | Access method |  Size   | Description
--------+-----------+-------+-------+-------------+---------------+---------+------------
 public | customers | table | admin | permanent   | heap          | 128 kB  |
 public | orders    | table | admin | permanent   | heap          | 1024 kB |
 public | products  | table | admin | permanent   | heap          | 64 kB   |
(3 rows)

Filter by schema

To list tables in a specific schema, use the pattern schema.*:

\dt myschema.*

To list tables across all schemas (including system schemas):

\dt *.*

Filter by table name pattern

You can use wildcards to match table names:

-- Tables starting with "user"
\dt user*

-- Tables containing "log"
\dt *log*

-- Tables in the "reporting" schema starting with "daily"
\dt reporting.daily*

Other useful \d variants

| Command | Description | |---------|-------------| | \dt | List tables | | \dt+ | List tables with sizes | | \dt schema.* | List tables in a specific schema | | \dt *.* | List tables in all schemas | | \dv | List views | | \dm | List materialized views | | \di | List indexes | | \d tablename | Describe a specific table |

Querying information_schema.tables

The information_schema.tables view follows the SQL standard. It works the same way across PostgreSQL, MySQL, SQL Server, and other databases that support the standard.

List all user tables

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

Output:

 table_schema |  table_name  | table_type
--------------+--------------+------------
 public       | customers    | BASE TABLE
 public       | orders       | BASE TABLE
 public       | products     | BASE TABLE
 reporting    | daily_stats  | BASE TABLE
(4 rows)

List tables in the public schema only

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

List tables and views together

SELECT
  table_name,
  table_type
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_type, table_name;

Output:

  table_name   | table_type
---------------+------------
 customers     | BASE TABLE
 orders        | BASE TABLE
 products      | BASE TABLE
 active_orders | VIEW
(4 rows)

The table_type column distinguishes between BASE TABLE and VIEW, which is useful when you need to tell them apart.

Querying pg_catalog.pg_tables

The pg_tables view is PostgreSQL-specific and provides ownership information that information_schema does not.

List all user tables

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

List tables owned by a specific user

SELECT schemaname, tablename
FROM pg_tables
WHERE tableowner = 'admin'
ORDER BY schemaname, tablename;

Compare pg_tables vs information_schema.tables

| Feature | pg_tables | information_schema.tables | |---------|-----------|--------------------------| | SQL standard | No (PostgreSQL-specific) | Yes | | Shows table owner | Yes | No | | Shows hasindexes, hasrules, hastriggers | Yes | No | | Portable across databases | No | Yes | | Includes views | No | Yes (with table_type filter) |

Use information_schema.tables when you want portable SQL. Use pg_tables when you need PostgreSQL-specific details like ownership.

Filtering by schema

PostgreSQL organizes tables into schemas. Understanding which schemas exist helps you filter results.

List all schemas

SELECT schema_name
FROM information_schema.schemata
ORDER BY schema_name;

Common schemas:

• public: The default schema where user tables go
• pg_catalog: PostgreSQL system tables
• information_schema: SQL standard metadata views
• pg_toast: Storage for large values (internal)

List tables in a specific schema

SELECT tablename
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY tablename;

List tables across multiple schemas

SELECT schemaname, tablename
FROM pg_tables
WHERE schemaname IN ('public', 'reporting', 'staging')
ORDER BY schemaname, tablename;

Exclude system schemas

This is the most common pattern. You want user-created tables and nothing else:

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

Or equivalently, exclude any schema starting with pg_:

SELECT schemaname, tablename
FROM pg_tables
WHERE schemaname NOT LIKE 'pg_%'
  AND schemaname != 'information_schema'
ORDER BY schemaname, tablename;

Listing tables with sizes

Table sizes help you understand disk usage, identify growth problems, and plan maintenance.

Size of each table

SELECT
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;

Output:

 schemaname | tablename |  total_size
------------+-----------+-------------
 public     | orders    | 1200 MB
 public     | customers | 256 MB
 public     | products  | 12 MB
(3 rows)

The pg_total_relation_size function includes the table data, indexes, and TOAST data.

Break down table vs index size

SELECT
  t.schemaname,
  t.tablename,
  pg_size_pretty(pg_relation_size(t.schemaname || '.' || t.tablename)) AS table_size,
  pg_size_pretty(
    pg_total_relation_size(t.schemaname || '.' || t.tablename)
    - pg_relation_size(t.schemaname || '.' || t.tablename)
  ) AS index_and_toast_size,
  pg_size_pretty(pg_total_relation_size(t.schemaname || '.' || t.tablename)) AS total_size
FROM pg_tables t
WHERE t.schemaname = 'public'
ORDER BY pg_total_relation_size(t.schemaname || '.' || t.tablename) DESC;

Top 10 largest tables in the database

SELECT
  n.nspname AS schema,
  c.relname AS table_name,
  pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
  pg_size_pretty(pg_relation_size(c.oid)) AS data_size,
  pg_size_pretty(pg_indexes_size(c.oid)) AS index_size
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 pg_total_relation_size(c.oid) DESC
LIMIT 10;

Listing tables with row counts

Estimated row counts (fast)

PostgreSQL tracks estimated row counts in pg_class.reltuples. This is updated by ANALYZE and is fast because it reads catalog data, not the actual table:

SELECT
  n.nspname AS schema,
  c.relname AS table_name,
  c.reltuples::bigint AS estimated_rows
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;

Output:

 schema | table_name | estimated_rows
--------+------------+----------------
 public | orders     |       12500000
 public | customers  |        1500000
 public | products   |          45000
(3 rows)

These estimates are good enough for most purposes. Run ANALYZE tablename if you need the estimates to be current.

Exact row counts (slow)

Getting exact counts requires scanning each table. This query builds and executes a COUNT(*) for every table. Use it on smaller databases only:

DO $$
DECLARE
  rec RECORD;
  row_count BIGINT;
BEGIN
  CREATE TEMP TABLE IF NOT EXISTS table_counts (
    schema_name TEXT,
    table_name TEXT,
    exact_count BIGINT
  );

  FOR rec IN
    SELECT schemaname, tablename
    FROM pg_tables
    WHERE schemaname = 'public'
  LOOP
    EXECUTE format('SELECT COUNT(*) FROM %I.%I', rec.schemaname, rec.tablename)
      INTO row_count;
    INSERT INTO table_counts VALUES (rec.schemaname, rec.tablename, row_count);
  END LOOP;
END $$;

SELECT * FROM table_counts ORDER BY exact_count DESC;
DROP TABLE table_counts;

On a database with millions of rows, this can take a long time. The estimated count approach is preferred for large databases.

Combine row counts with sizes

SELECT
  n.nspname AS schema,
  c.relname AS table_name,
  c.reltuples::bigint AS estimated_rows,
  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 = 'r'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(c.oid) DESC;

This gives you a complete overview of your database in a single query.

Listing tables with column info

Column count per table

SELECT
  table_schema,
  table_name,
  COUNT(*) AS column_count
FROM information_schema.columns
WHERE table_schema = 'public'
GROUP BY table_schema, table_name
ORDER BY table_name;

Full column details for all tables

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

Tables with a specific column

Sometimes you need to find every table that has a column named created_at or user_id:

SELECT
  table_schema,
  table_name,
  column_name,
  data_type
FROM information_schema.columns
WHERE column_name = 'user_id'
  AND table_schema = 'public'
ORDER BY table_name;

Tables and their primary keys

SELECT
  tc.table_schema,
  tc.table_name,
  STRING_AGG(kcu.column_name, ', ' ORDER BY kcu.ordinal_position) AS primary_key_columns
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON tc.constraint_name = kcu.constraint_name
  AND tc.table_schema = kcu.table_schema
WHERE tc.constraint_type = 'PRIMARY KEY'
  AND tc.table_schema = 'public'
GROUP BY tc.table_schema, tc.table_name
ORDER BY tc.table_name;

Listing views vs tables

PostgreSQL has regular views, materialized views, and base tables. Here is how to list each type.

List only views

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

Or using psql:

\dv

List only materialized views

Materialized views do not appear in information_schema. Query pg_matviews instead:

SELECT
  schemaname,
  matviewname,
  matviewowner
FROM pg_matviews
WHERE schemaname = 'public'
ORDER BY matviewname;

Or using psql:

\dm

List everything (tables, views, materialized views)

SELECT
  n.nspname AS schema,
  c.relname AS name,
  CASE c.relkind
    WHEN 'r' THEN 'table'
    WHEN 'v' THEN 'view'
    WHEN 'm' THEN 'materialized view'
    WHEN 'f' THEN 'foreign table'
    WHEN 'p' THEN 'partitioned table'
  END AS type
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'v', 'm', 'f', 'p')
  AND n.nspname = 'public'
ORDER BY type, name;

This query also picks up foreign tables and partitioned tables. The relkind column in pg_class is the most reliable way to distinguish object types.

Common patterns

Check if a table exists

SELECT EXISTS (
  SELECT 1
  FROM information_schema.tables
  WHERE table_schema = 'public'
    AND table_name = 'orders'
);

Or using pg_tables:

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

List tables created recently

PostgreSQL does not track creation timestamps in the catalog. One workaround is to check the file creation time on disk using pg_stat_file:

SELECT
  c.relname AS table_name,
  (pg_stat_file(pg_relation_filepath(c.oid))).modification AS last_modified
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname = 'public'
ORDER BY last_modified DESC
LIMIT 10;

Note: this shows the last time the file was modified, not when the table was created. It is an approximation.

List empty tables

SELECT
  n.nspname AS schema,
  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 = 'public'
  AND c.reltuples = 0;

Run ANALYZE first to make sure the row estimates are current.

List partitioned tables and their partitions

SELECT
  parent.relname AS parent_table,
  child.relname AS partition_name,
  pg_get_expr(child.relpartbound, child.oid) AS partition_expression
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
JOIN pg_namespace n ON parent.relnamespace = n.oid
WHERE n.nspname = 'public'
ORDER BY parent.relname, child.relname;

List tables with no indexes

Tables without indexes (other than a primary key constraint) might need attention:

SELECT
  t.schemaname,
  t.tablename
FROM pg_tables t
LEFT JOIN pg_indexes i
  ON t.schemaname = i.schemaname
  AND t.tablename = i.tablename
WHERE t.schemaname = 'public'
GROUP BY t.schemaname, t.tablename
HAVING COUNT(i.indexname) = 0;

List tables with their access statistics

This query shows which tables are being used and how:

SELECT
  schemaname,
  relname AS table_name,
  seq_scan AS sequential_scans,
  idx_scan AS index_scans,
  n_tup_ins AS rows_inserted,
  n_tup_upd AS rows_updated,
  n_tup_del AS rows_deleted,
  last_vacuum,
  last_analyze
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY seq_scan + COALESCE(idx_scan, 0) DESC;

Tables with high sequential scan counts and low index scan counts may benefit from adding indexes.

Using a GUI client

If you prefer a visual interface, DB Pro shows your tables, views, sizes, and schemas in a sidebar without needing to remember any of these commands. It connects to PostgreSQL, MySQL, and SQLite databases.

Quick reference

| Task | psql command | SQL query | |------|-------------|-----------| | List tables | \dt | SELECT tablename FROM pg_tables WHERE schemaname = 'public' | | List tables with sizes | \dt+ | Use pg_total_relation_size() (see above) | | List tables in all schemas | \dt *.* | Remove the WHERE schemaname filter | | List views | \dv | SELECT table_name FROM information_schema.views WHERE table_schema = 'public' | | List materialized views | \dm | SELECT matviewname FROM pg_matviews WHERE schemaname = 'public' | | Describe a table | \d tablename | Query information_schema.columns | | Check if table exists | N/A | SELECT EXISTS (SELECT 1 FROM pg_tables WHERE ...) |

Summary

• Use \dt in psql for a quick table listing, and \dt+ for sizes
• Query information_schema.tables when you need portable SQL that works across database systems
• Query pg_tables when you need PostgreSQL-specific details like table ownership
• Filter with WHERE schemaname = 'public' to exclude system tables
• Use pg_class with pg_namespace for the most flexible queries (sizes, row counts, object types)
• Estimated row counts from pg_class.reltuples are fast and good enough for most use cases
• Combine pg_total_relation_size() with pg_size_pretty() for human-readable size output