(opens in a new tab)

How to Describe a Table in PostgreSQL

August 25, 2026 by Chat2DB Team

Developers arriving from MySQL or Oracle reach for DESCRIBE table_name; and get a syntax error. PostgreSQL has no DESCRIBE statement — it has something better, but it is split across a psql meta-command and two catalog interfaces, and which one you want depends on whether a human or a program is reading the answer. This article covers all three, with copy-paste queries for the cases the meta-command does not handle.

The quick answer: \d in psql

Inside psql, the backslash commands describe objects:

\d orders

                                       Table "public.orders"
   Column    |           Type           | Collation | Nullable |           Default
-------------+--------------------------+-----------+----------+------------------------------
 id          | bigint                   |           | not null | generated always as identity
 customer_id | bigint                   |           | not null |
 status      | text                     |           | not null | 'pending'::text
 total       | numeric(12,2)            |           | not null | 0
 created_at  | timestamp with time zone |           | not null | now()
Indexes:
    "orders_pkey" PRIMARY KEY, btree (id)
    "orders_customer_id_created_at_idx" btree (customer_id, created_at DESC)
Check constraints:
    "orders_total_check" CHECK (total >= 0::numeric)
Foreign-key constraints:
    "orders_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
Referenced by:
    TABLE "order_items" CONSTRAINT "order_items_order_id_fkey" FOREIGN KEY (order_id) REFERENCES orders(id)

Add a plus sign for storage details, statistics targets, comments and the access method:

\d+ orders

That adds a Storage column (plain, extended, external, main), per-column Compression, Stats target, Description (the column comment), and at the bottom the table's access method, persistence and any partition information.

Related meta-commands worth memorising:

If \d orders reports "Did not find any relation named", the table exists in a schema that is not in your search_path. Qualify it (\d sales.orders) or check SHOW search_path;.

To see what \d actually runs, start psql with -E (or type \set ECHO_HIDDEN on). It prints the underlying catalog queries, which is the fastest way to learn pg_catalog.

Describing a table from SQL: information_schema

The meta-command only exists inside psql. Application code, migrations and dashboards need real SQL. The SQL-standard view is information_schema.columns:

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

information_schema is portable — the same query works on MySQL 8 and SQL Server — which makes it the right choice for tools that support several engines. The trade-offs are real, though: it is a set of views over the catalog, so it is noticeably slower on databases with tens of thousands of tables, it splits type information across four columns instead of giving you numeric(12,2) as text, and it hides anything Postgres-specific such as generated columns' expressions or index method.

A useful companion query lists the constraints:

SELECT tc.constraint_name,
       tc.constraint_type,
       string_agg(kcu.column_name, ', ' ORDER BY kcu.ordinal_position) AS columns
FROM information_schema.table_constraints tc
LEFT JOIN information_schema.key_column_usage kcu
       ON kcu.constraint_name = tc.constraint_name
      AND kcu.table_schema    = tc.table_schema
WHERE tc.table_schema = 'public'
  AND tc.table_name   = 'orders'
GROUP BY tc.constraint_name, tc.constraint_type
ORDER BY tc.constraint_type;

Describing a table from SQL: pg_catalog

For anything Postgres-specific, query the catalog directly. This version produces output very close to \d, in one row per column, with the type already formatted:

SELECT a.attnum                                        AS position,
       a.attname                                       AS column_name,
       format_type(a.atttypid, a.atttypmod)            AS data_type,
       NOT a.attnotnull                                AS is_nullable,
       pg_get_expr(d.adbin, d.adrelid)                 AS default_expr,
       a.attidentity                                   AS identity,
       a.attgenerated                                  AS generated,
       col_description(a.attrelid, a.attnum)           AS comment
FROM pg_attribute a
LEFT JOIN pg_attrdef d ON d.adrelid = a.attrelid AND d.adnum = a.attnum
WHERE a.attrelid = 'public.orders'::regclass
  AND a.attnum > 0
  AND NOT a.attisdropped
ORDER BY a.attnum;

Three things make this the query to keep in your snippets file:

• 'public.orders'::regclass resolves the name through search_path and throws a clear error if it does not exist — no string matching on schema and table.
• format_type() returns numeric(12,2) or character varying(255) exactly as you would write it in DDL.
• attnum > 0 AND NOT attisdropped filters out system columns and the tombstones left behind by DROP COLUMN.

attidentity is a for GENERATED ALWAYS AS IDENTITY, d for BY DEFAULT, empty otherwise. attgenerated is s for a stored generated column.

Indexes

SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public' AND tablename = 'orders'
ORDER BY indexname;

indexdef is the full CREATE INDEX statement, which is exactly what you want when copying an index between environments.

Constraints, including check expressions

SELECT conname AS constraint_name,
       CASE contype WHEN 'p' THEN 'PRIMARY KEY'
                    WHEN 'f' THEN 'FOREIGN KEY'
                    WHEN 'u' THEN 'UNIQUE'
                    WHEN 'c' THEN 'CHECK'
                    WHEN 'x' THEN 'EXCLUDE' END AS type,
       pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'public.orders'::regclass
ORDER BY contype;

pg_get_constraintdef() gives the exact clause — CHECK (total >= 0::numeric), FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE — which information_schema cannot reproduce without several joins.

The whole DDL

There is no built-in SHOW CREATE TABLE, but pg_dump gives you the authoritative version:

pg_dump --schema-only --no-owner --no-privileges -t public.orders mydb

Add -s -t 'sales.*' to dump a whole schema's structure. This is the only method guaranteed to reproduce everything: storage parameters, partitions, triggers, comments, sequence ownership.

Size, row estimates and bloat

"Describe" often really means "tell me about this table":

SELECT pg_size_pretty(pg_total_relation_size('public.orders')) AS total,
       pg_size_pretty(pg_relation_size('public.orders'))       AS heap,
       pg_size_pretty(pg_indexes_size('public.orders'))        AS indexes,
       (SELECT reltuples::bigint FROM pg_class
        WHERE oid = 'public.orders'::regclass)                 AS estimated_rows;

reltuples is the planner's estimate, updated by ANALYZE — instant, but approximate. count(*) is exact and reads the whole table. For a dashboard, prefer the estimate.

Live statistics come from pg_stat_user_tables:

SELECT seq_scan, idx_scan, n_live_tup, n_dead_tup,
       last_vacuum, last_autovacuum, last_analyze
FROM pg_stat_user_tables
WHERE relname = 'orders';

A large n_dead_tup relative to n_live_tup means bloat; a high seq_scan with a low idx_scan on a big table usually means a missing index.

Describing every table at once

To document a whole schema — column count, size, comment — in one result set:

SELECT c.relname AS table_name,
       obj_description(c.oid)                       AS table_comment,
       count(a.attname) FILTER (WHERE a.attnum > 0
                                 AND NOT a.attisdropped) AS columns,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_attribute a ON a.attrelid = c.oid
WHERE n.nspname = 'public' AND c.relkind = 'r'
GROUP BY c.oid, c.relname
ORDER BY pg_total_relation_size(c.oid) DESC;

relkind is r for ordinary tables, p for partitioned tables, v for views, m for materialized views, i for indexes, S for sequences. Include 'p' if you use declarative partitioning.

Comments: the documentation nobody adds

Postgres stores comments on tables and columns, and every GUI displays them:

COMMENT ON TABLE  orders            IS 'One row per customer order, immutable after fulfilment.';
COMMENT ON COLUMN orders.status     IS 'pending | paid | shipped | cancelled';
COMMENT ON COLUMN orders.total      IS 'Gross total in the order currency, tax included.';

Read them back with col_description(attrelid, attnum) and obj_description(oid), or simply \d+. Two minutes of COMMENT ON when you create a table saves an hour of archaeology later.

Doing it without writing catalog queries

Every serious client shows table structure without SQL. In psql it is \d+; in a GUI it is usually a double-click. Chat2DB (opens in a new tab) is a free AI-powered SQL client that lists columns, types, defaults, indexes, foreign keys and comments in one panel, and generates the CREATE TABLE DDL on demand — useful when comparing structures across environments, since you can open two connections side by side. There is also a browser version at app.chat2db.ai (opens in a new tab) if you would rather not install anything.

Summary

PostgreSQL replaces DESCRIBE with three complementary tools. Use \d and \d+ for interactive work in psql; use information_schema.columns when the query must run on several database engines; use pg_catalog with format_type(), pg_get_expr() and pg_get_constraintdef() when you need the full Postgres-specific truth, including identity columns, generated columns, check expressions and comments. And when you need the exact DDL, pg_dump --schema-only -t table is the one source that never leaves anything out.