How to describe a table in PostgreSQL

Show a PostgreSQL table's columns, types, defaults, indexes and foreign keys with psql \d, \d+, or an information_schema query that works from any client.

Updated November 6, 2026 · Tested on PostgreSQL 16.14, psql 16.14

Quick Answer

PostgreSQL has no DESCRIBE statement. In psql, use \d with the table name:

\d orders

From any other client, query information_schema.columns, shown below.

Describe a table in psql

\d tablename prints every column with its type, nullability and default, followed by indexes, constraints, foreign keys and triggers:

shop=# \d orders
                                    Table "public.orders"
   Column    |           Type           | Collation | Nullable |           Default
-------------+--------------------------+-----------+----------+------------------------------
 id          | bigint                   |           | not null | generated always as identity
 customer_id | integer                  |           | not null |
 total       | numeric(10,2)            |           |          | 0
 created_at  | timestamp with time zone |           | not null | now()
Indexes:
    "orders_pkey" PRIMARY KEY, btree (id)
    "orders_customer_idx" btree (customer_id)

\d+ orders adds four columns (Storage, Compression, Stats target, Description) and an Access method: heap footer. Description holds column comments only.

Command	Shows
`\d`	Every table, view and sequence in the search path
`\dt`	Tables only
`\d orders`	Columns, indexes, constraints and triggers of one table
`\d+ orders`	The same, plus storage details and comments
`\d sales.orders`	A table in a schema other than public
`\di`	Indexes
`\dv` / `\d+ viewname`	Views, and a view's definition
`\dp orders`	Who has which privileges on the table

Names are matched as patterns, so \d ord* describes every table starting with "ord". If a table name has capitals, quote it: \d "Orders".

Describe a table with SQL (any client)

\d is a psql feature, so it doesn't work in an application, a GUI query window or a driver. The portable equivalent is information_schema.columns:

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

 column_name |        data_type         | is_nullable | column_default
-------------+--------------------------+-------------+----------------
 id          | bigint                   | NO          |
 customer_id | integer                  | NO          |
 total       | numeric                  | YES         | 0
 created_at  | timestamp with time zone | NO          | now()

data_type drops the precision (numeric instead of numeric(10,2)), and identity columns show no default. If you need the exact type as PostgreSQL prints it, use format_type() from the catalog, below.

Exact types, indexes and foreign keys from the catalog

The system catalogs give the same detail \d shows. Exact column types:

SELECT a.attname AS column,
       format_type(a.atttypid, a.atttypmod) AS type,
       a.attnotnull AS not_null
FROM pg_attribute a
WHERE a.attrelid = 'public.orders'::regclass
  AND a.attnum > 0
  AND NOT a.attisdropped
ORDER BY a.attnum;

Indexes, with their full definitions:

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

Constraints, including foreign keys and what they reference:

SELECT conname, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'public.orders'::regclass;

What we found testing this

We described one table with an identity column, an array, an enum and comments, and compared what each method reports:

Column	`\d`	`information_schema` data_type	`udt_name`
`tags text[]`	`text[]`	`ARRAY`	`_text`
`status` (an enum)	`status`	`USER-DEFINED`	`status`
`note varchar(200)`	`character varying(200)`	`character varying`	`varchar`

information_schema hides arrays and enums. It reports ARRAY and USER-DEFINED, so a schema-diff script built on data_type can't tell text[] from int[]. The leading underscore in udt_name (_text) marks an array. format_type() gives the real type every time.
\d+ shows column comments but not the table comment. COMMENT ON TABLE text appears in the Description column of \dt+ orders, or from SQL with obj_description('public.orders'::regclass, 'pg_class').
Identity columns have an empty column_default, while serial columns show nextval('orders_n_seq'::regclass). Check is_identity (YES) and identity_generation (ALWAYS or BY DEFAULT) instead.
numeric_precision for a bigint is 64, not 19. Integer precision is reported in bits (radix 2); only numeric columns report decimal digits.

Get the CREATE TABLE statement

PostgreSQL has no SHOW CREATE TABLE. The reliable way to get the full DDL is pg_dump with --schema-only:

pg_dump -U postgres -d shop --schema-only --table=public.orders

The output includes the CREATE TABLE, its indexes, constraints and ownership, ready to run elsewhere.

Frequently Asked Questions

Is there a DESCRIBE command in PostgreSQL?

No. DESCRIBE is MySQL and Oracle syntax. In psql use \d tablename; from other clients query information_schema.columns.

What is the difference between \d and \d+?

\d+ adds Storage, Compression, Stats target and Description (column comments) columns, plus the access method. For a view it also prints the view definition. The table's own comment is shown by \dt+, not \d+.

Why does \d say "Did not find any relation named"?

The table is in a schema outside your search_path, the name has capital letters and needs double quotes, or you are connected to a different database.

How do I see the CREATE TABLE statement for a table?

Run pg_dump --schema-only --table=schema.table against the database. PostgreSQL has no SHOW CREATE TABLE.