Find your way around psql

SQL versus backslash commands

Everything you type at the shop=# prompt is one of two things:

• SQL, which psql sends to the server. It can span several lines and runs when you end it with ;.
• A meta-command, starting with a backslash, which psql handles itself. It runs as soon as you press Enter, no ; needed.

The ten you will use

Command	What it does
\l	List databases on the server
\c shop	Connect to another database
\dt	List tables
\d orders	Describe a table: columns, keys, constraints, indexes
\dn	List schemas
\x	Toggle expanded display, one column per line
\timing	Show how long each query takes
\i file.sql	Run the commands in a file
\?	List every meta-command
\q	Quit

Look around shop

Connect with psql shop and work through these. Our output is shown for each; the Owner column will show your own username rather than ours.

List the tables:

shop=# \dt
            List of tables
 Schema |    Name     | Type  | Owner
--------+-------------+-------+-------
 public | customers   | table | lab
 public | order_items | table | lab
 public | orders      | table | lab
 public | products    | table | lab
(4 rows)

Describe one. This is the command you will use most. It shows the columns, but also every rule the table enforces:

shop=# \d orders
                          Table "public.orders"
   Column    |           Type           | Collation | Nullable | Default
-------------+--------------------------+-----------+----------+---------
 id          | integer                  |           | not null |
 customer_id | integer                  |           | not null |
 status      | text                     |           | not null |
 ordered_at  | timestamp with time zone |           | not null |
Indexes:
    "orders_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "orders_status_check" CHECK (status = ANY (ARRAY['pending'::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)

Read it from the top: four required columns; a primary key backed by a B-tree index; a check that status is one of four values; a foreign key to customers; and order_items pointing back at this table.

Wide rows are easier with \x:

shop=# \x
Expanded display is on.
shop=# SELECT * FROM customers WHERE id = 1;
-[ RECORD 1 ]---------------
id        | 1
name      | Asha Rao
email     | asha@example.com
city      | Pune
joined_on | 2025-01-12

Run \x again to switch back.

Time a query:

shop=# \timing on
Timing is on.
shop=# SELECT count(*) FROM order_items;
 count
-------
    19
(1 row)

Time: 0.302 ms

Two habits worth forming now

• Read the whole error. PostgreSQL errors usually have a DETAIL line that names the exact value or constraint at fault, and sometimes a HINT that says how to fix it.
• Keep SQL in files. Write longer queries in a .sql file and run them with \i. You can edit and rerun them, and keep them.

Check yourself

1. You typed SELECT * FROM customers and pressed Enter, and nothing happened except the prompt changed to shop-#. Why?

   Show answer

   SQL runs when it ends with ;. psql is waiting for the rest of the statement; type ; and press Enter.

   I could explain this without looking

2. Which command shows a table’s columns and its constraints and indexes?

   Show answer

   \d tablename, for example \d orders.

   I could explain this without looking

3. What is the difference between \c shop and SELECT in how psql handles them?

   Show answer

   \c is a meta-command that psql handles itself and runs immediately. SELECT is SQL, which psql sends to the server once the statement ends with ;.

   I could explain this without looking