Describe Table in PostgreSQL: psql, SQL Metadata, and Examples

You typed DESCRIBE the way MySQL taught you, and PostgreSQL answered with a syntax error. PostgreSQL has no DESCRIBE statement. In psql, \d table_name prints the table's definition, and from any client that sends only SQL, the same answers come from the catalog views:

The examples run on PostgreSQL 18, against two tables:

CREATE TABLE public.customers (
    customer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL
);

CREATE TABLE public.orders (
    order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL REFERENCES public.customers,
    status varchar(20) NOT NULL DEFAULT 'new',
    total numeric(12,2) NOT NULL,
    created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX orders_status_idx ON public.orders (status);

What describe table means in PostgreSQL

Describing a table usually means answering one of five questions:

• the column names and their data types
• which columns accept NULL, and what the defaults are
• the primary key, the foreign keys and the check constraints
• the indexes on the table
• the comments, and whether a column is an identity or a generated one

psql answers all five with \d and its variants, but \d is not SQL. psql expands it into queries against the catalog and sends those instead, and psql -E prints them. A JDBC connection, an application or the DbSchema SQL Editor sends your text to the server as written, and the server rejects the backslash.

From SQL, two families of views answer. information_schema.columns is the SQL standard's view, portable across engines, and it shows "only those columns ... that the current user has access to (by way of being the owner or having some privilege)". pg_catalog is PostgreSQL's own set, and it holds what the standard has no place for: the exact text of a constraint, and the CREATE INDEX statement behind an index.

One version difference matters when you read constraints. PostgreSQL 18 stores each column's NOT NULL in pg_constraint as well, as a row of type n. In PostgreSQL 17 that code meant a not-null constraint on a domain only, and table columns had no such rows.

Describe a table in psql

psql -U your_username -d your_database

Inside the session, \d and the table name print the definition. Write the schema in front of the name when the table is outside your search path:

\d public.orders

The columns come first, each with its type, whether it accepts NULL, and its default:

                                    Table "public.orders"
   Column    |           Type           | Collation | Nullable |           Default
-------------+--------------------------+-----------+----------+------------------------------
 order_id    | bigint                   |           | not null | generated always as identity
 customer_id | bigint                   |           | not null |
 status      | character varying(20)    |           | not null | 'new'::character varying
 total       | numeric(12,2)            |           | not null |
 created_at  | timestamp with time zone |           | not null | CURRENT_TIMESTAMP

Under them come the objects attached to the table, which for orders are two indexes and the reference to customers:

Indexes:
    "orders_pkey" PRIMARY KEY, btree (order_id)
    "orders_status_idx" btree (status)
Foreign-key constraints:
    "orders_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customers(customer_id)

PostgreSQL created orders_pkey for the primary key, and orders_status_idx is the index the example asked for.

The + form prints more:

\d+ public.orders

It adds columns such as Storage and Description, and a last line, Access method: heap. Description holds the column comments, so reach for \d+ on a schema you did not write. On PostgreSQL 18 it also lists the not-null constraints, under the foreign keys:

Not-null constraints:
    "orders_order_id_not_null" NOT NULL "order_id"
    "orders_customer_id_not_null" NOT NULL "customer_id"
    "orders_status_not_null" NOT NULL "status"
    "orders_total_not_null" NOT NULL "total"
    "orders_created_at_not_null" NOT NULL "created_at"

A pattern describes several objects at once:

\d public.*

That prints every relation in the schema, not only its tables: here both tables, their two identity sequences and the three indexes. \dt lists the tables alone. Show Tables in PostgreSQL covers that command and its SQL equivalents, and Essential PostgreSQL Commands the rest of a psql session.

Query columns with information_schema

From any client that speaks only SQL, the column list comes from the standard view:

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

Both filters matter. Without table_schema, a table named orders in a second schema adds its columns to yours, and without ORDER BY ordinal_position the rows come back in whatever order PostgreSQL finds fastest. data_type carries the type without its length, so the 20 of varchar(20) sits in character_maximum_length, and the 12 and 2 of numeric(12,2) sit in numeric_precision and numeric_scale.

column_default is empty for order_id, although the column fills itself. An identity column takes its values from a sequence attached to it, not from a default expression, which is why is_identity answers separately. is_generated does the same for generated columns: it reads ALWAYS for a column computed from other columns, and NEVER for every other one.

Common column types you will see in output

Both psql and information_schema give back the long spellings: varchar comes back as character varying, and timestamptz as timestamp with time zone. The short names are aliases PostgreSQL accepts on input and never returns. A type you don't recognize may be a domain, in which case data_type shows the type underneath it and domain_name the domain.

If a definition turns out to be wrong, Create Table in PostgreSQL covers the types and constraints you would change, and Foreign Keys in PostgreSQL the references between tables.

Every table in the schema at once

For a schema of any size, \d public.* is a lot of screen. A column count per table is the quicker overview, and it runs from any client:

SELECT t.table_name,
       COUNT(c.column_name) AS column_count
FROM information_schema.tables t
JOIN information_schema.columns c
  ON c.table_schema = t.table_schema
 AND c.table_name = t.table_name
WHERE t.table_schema = 'public'
  AND t.table_type = 'BASE TABLE'
GROUP BY t.table_name
ORDER BY t.table_name;

Inspect constraints and indexes with pg_catalog

List the constraints

SELECT conname AS constraint_name,
       contype AS constraint_type,
       pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'public.orders'::regclass
  AND contype <> 'n'
ORDER BY conname;

pg_get_constraintdef rebuilds the constraint as SQL, which is the shortest path from a constraint name in an error message to the rule that raised it. The type code says what kind of constraint each row is:

• c for a check constraint
• f for a foreign key constraint
• n for a not-null constraint
• p for a primary key constraint
• u for a unique constraint
• t for a constraint trigger
• x for an exclusion constraint

On PostgreSQL 18, the contype <> 'n' filter keeps the result to these two rows. Without it you also get one row per NOT NULL column, the same five that \d+ lists.

List the indexes

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

indexdef is the statement that would recreate the index, so comparing the indexes of two environments means comparing two strings.

Read the comments

COMMENT ON TABLE public.orders IS 'Customer orders';
COMMENT ON COLUMN public.orders.status IS 'new, paid or shipped';

SELECT obj_description('public.orders'::regclass, 'pg_class') AS table_comment,
       col_description('public.orders'::regclass, 3) AS status_comment;

obj_description takes the catalog as its second argument, pg_class for a table. The one-argument form is deprecated, because an OID alone can match an object in another catalog. col_description takes the column's ordinal_position, 3 for status.

Inspect table structure visually in DbSchema

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

Reading one table from the catalog is quick. Reading twenty, and the references between them, is what DbSchema is for:

1. Click Connect to Database, pick PostgreSQL in Choose Your Database, and fill in the Connection Dialog. DbSchema connects through the PostgreSQL JDBC driver.
2. Let DbSchema reverse-engineer the schema. Every table lands on an interactive diagram, with the foreign key lines drawn between the tables.
3. Double-click a table header to open the Table Dialog. The Columns tab lists each column with its type, its not-null flag and its comment, and the Indexes and Foreign Keys tabs hold the keys and the references.

4. Open the SQL Editor from the Editors menu for the catalog queries above, which run there as written.

Reverse-engineering only reads the database into the DbSchema model, so nothing in the database changes while you look around. To share what you read, Diagram → Export HTML5 or PDF Documentation writes interactive HTML5 documentation, with a searchable table list and each column's description as a mouse-over tooltip.

Keep \d for the table in front of you and the catalog queries for everything a script has to read. Download DbSchema at https://dbschema.com/download.html, connect to your PostgreSQL database, and open the table you were describing. Connecting, reverse-engineering, the diagram and the SQL Editor are in the free Community Edition; the HTML5 documentation is in Pro.