Describe Table in PostgreSQL: psql, SQL Metadata, and Examples
For SQL users who know MySQL's DESCRIBE and need a PostgreSQL table's columns, keys and indexes, in psql or in any other SQL client.
On this page
You type DESCRIBE and a table name out of MySQL habit, and PostgreSQL answers ERROR: syntax error at or near "DESCRIBE". PostgreSQL has no DESCRIBE statement. In psql, describe a table with the \d meta-command:
\d table_name
From any other client, such as an application or a GUI, query information_schema.columns:
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'public'
AND table_name = 'table_name'
ORDER BY ordinal_position;
Every result below comes from PostgreSQL 18.6. To follow along, create 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);
Describe a table in psql with \d
Connect with psql, then give \d the table's name:
psql -U postgres -d your_database
\d public.orders
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
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)
The columns come first, each with its type, whether it accepts NULL, and its default. Under them, psql lists the indexes and the foreign keys. PostgreSQL named the primary key's index orders_pkey itself.
The search path is the list of schemas where PostgreSQL looks up a name that has no schema. By default, it holds only the schema public. For a table on the search path, you can leave out the schema: \d orders.
\d works only in psql. psql turns \d into queries on the system catalog. The system catalog is the set of tables where PostgreSQL describes its own objects. To print those queries, start psql with -E. Other clients, such as a JDBC connection, send your text to the server as written. The server answers \d with ERROR: syntax error at or near "\".
To describe every table of a schema at once, give \d a pattern:
\d public.*
The pattern matches every object in public that \d can describe, not only the tables. Here it printed the two tables, their two identity sequences and the three indexes. To list the tables alone, use \dt, which Show Tables in PostgreSQL covers.
Column comments and not-null constraints with \d+
\d+ public.orders
\d+ adds four columns to the column list: Storage, Compression, Stats target and Description. Description holds the column comments. Under the foreign keys, PostgreSQL 18 also lists a not-null constraint for each NOT NULL column:
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"
On PostgreSQL 17.9, \d+ prints no not-null constraints. PostgreSQL 18 stores NOT NULL in pg_constraint, as a constraint with a name of its own.
Columns with information_schema
information_schema holds the views that the SQL standard defines. A query on them is plain SQL, so it works from any client:
SELECT column_name,
data_type,
character_maximum_length,
is_nullable,
column_default,
is_identity
FROM information_schema.columns
WHERE table_schema = 'public'
AND table_name = 'orders'
ORDER BY ordinal_position;
| column_name | data_type | character_maximum_length | is_nullable | column_default | is_identity |
|---|---|---|---|---|---|
| order_id | bigint | NO | YES | ||
| customer_id | bigint | NO | NO | ||
| status | character varying | 20 | NO | 'new'::character varying | NO |
| total | numeric | NO | NO | ||
| created_at | timestamp with time zone | NO | CURRENT_TIMESTAMP | NO |
Filter on both table_schema and table_name. If another schema also holds a table named orders, a filter on table_name alone returns the columns of both tables. Keep the ORDER BY too: without it, the rows come back in no fixed order.
data_type holds the type without its length. The length of varchar(20) is in character_maximum_length. The precision and scale of numeric(12,2) are in numeric_precision and numeric_scale. psql and information_schema both spell types in full: varchar comes back as character varying, and timestamptz as timestamp with time zone.
order_id is an identity column: PostgreSQL gives each new row the next number, as AUTO_INCREMENT does in MySQL. Its column_default is empty, because an identity column takes its values from a sequence, a counter object in the database. is_identity reads YES instead.
Constraints with pg_constraint
Some facts are missing from information_schema, such as a constraint's definition as SQL. The catalog tables in pg_catalog hold them:
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;
| constraint_name | constraint_type | definition |
|---|---|---|
| orders_customer_id_fkey | f | FOREIGN KEY (customer_id) REFERENCES customers(customer_id) |
| orders_pkey | p | PRIMARY KEY (order_id) |
pg_get_constraintdef writes each constraint as SQL. The contype code says what kind of constraint a row is:
cfor a check constraintffor a foreign keynfor a not-null constraintpfor a primary keyufor a unique constrainttfor a constraint triggerxfor an exclusion constraint
The condition contype <> 'n' leaves out the not-null constraints. Without it, PostgreSQL 18 also returns the five rows that \d+ lists.
Indexes with pg_indexes
SELECT indexname,
indexdef
FROM pg_indexes
WHERE schemaname = 'public'
AND tablename = 'orders'
ORDER BY indexname;
| indexname | indexdef |
|---|---|
| orders_pkey | CREATE UNIQUE INDEX orders_pkey ON public.orders USING btree (order_id) |
| orders_status_idx | CREATE INDEX orders_status_idx ON public.orders USING btree (status) |
indexdef is the statement that creates the index again. To compare the indexes of two databases, compare these strings.
Table and column 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;
| table_comment | status_comment |
|---|---|
| Customer orders | new, paid or shipped |
obj_description takes the name of a catalog table as its second argument: pg_class for a table. The form without it is deprecated. col_description takes the column's number, the ordinal_position of information_schema.columns: 3 for status, the third column of orders. In psql, \dt+ shows the table comment.
Why \d finds no table
Add a table in a second schema, and a table with a capital letter in its name:
CREATE SCHEMA sales;
CREATE TABLE sales.invoices (invoice_id int PRIMARY KEY);
CREATE TABLE "OrderLines" (line_id int);
Three things hide a table:
- The schema is not on the search path.
\d invoicesprintsDid not find any relation named "invoices".Write the schema:\d sales.invoices. - The name has capital letters. psql folds an unquoted name to lower case, so
\d OrderLinesfinds nothing. Quote the name:\d "OrderLines". In information_schema.columns, write the name as it was created:table_name = 'OrderLines'. - You hold no privilege on the table. information_schema.columns shows only the columns of the tables you own or hold a privilege on. A new role without grants got 0 rows from the information_schema.columns query on orders.
\dreads the catalog tables, which every role can read, so\d public.ordersstill printed every column for that role.
Describe a table in DbSchema
\d prints each table as a block of text, and each foreign key as one line in it. DbSchema reads the tables and draws them on a diagram, with a line for each foreign key. To connect to the database:
- Choose File → New Model, keep Connect to Database selected, and click PostgreSQL.
- Press Connect to PostgreSQL, fill in the connection dialog, and press Connect.
- In Reverse Engineer the Database, tick the schema public and press Load.
- On the diagram, double-click the header of the orders table to open the Edit Table dialog.
The Columns tab lists each column with its type and its comment. The Primary Key / Unique / Indexes tab and the Foreign Keys tab hold the keys. Close the dialog with Cancel. While DbSchema is connected, OK writes any change to the database.
Reverse-engineering, DbSchema's word for reading the schema into the diagram, changes nothing in the database. To run the queries above, press Editor in the toolbar and choose SQL Editor.
Download DbSchema at https://dbschema.com/download.html, connect to your PostgreSQL database, and double-click the table you were describing. Connecting, reverse-engineering, the diagrams and the SQL Editor are in the free Community Edition.

