psql and Describing Tables

Meta-commands

psql is PostgreSQL's interactive client. Besides SQL, it has meta-commands starting with a backslash: \l lists databases, \dt lists tables, \d table describes a table (columns, defaults, indexes, constraints, references), \x toggles expanded output, \timing shows query times, and \i file.sql runs a script. Connection details come from flags or environment variables (PGHOST, PGUSER, PGDATABASE) or a connection URI.

Describing the orders table, run

I ran this with psql against PostgreSQL 16.2 (a local server started with the pgserver Python package), on a fresh database loaded with the shop schema from the first section. \d orders shows the identity primary key, the default status, the CHECK constraint, the foreign key to customers, and that order_items references orders with ON DELETE CASCADE.

\d orders

Output:

                           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 | 'new'::text
 ordered_on  | date   |           | not null | 
Indexes:
    "orders_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "orders_status_check" CHECK (status = ANY (ARRAY['new'::text, 'paid'::text, 'shipped'::text, 'cancelled'::text]))
Foreign-key constraints:
    "orders_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customers(id)
Referenced by:
    TABLE "order_items" CONSTRAINT "order_items_order_id_fkey" FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE

Use \x for wide rows

Expanded display (\x auto) prints one column per line when rows are too wide for the terminal.

Quick check: Which psql meta-command describes a table's columns and constraints?

• \d table_name
• \q
• \timing
• SELECT * FROM information