psql fluency

Why this lesson exists

psql is the one client that is on every PostgreSQL server, in every container image, in every runbook. In an incident nobody opens a GUI: you ssh in, run psql, and need to find the right table, read its definition, run a query, export some rows and get out - quickly and without leaving a transaction open behind you. This lesson is the psql you need for the rest of the chapter.

What you need to know already: sudo -u postgres psql and peer authentication (the last lesson), basic SQL, shell quoting ('...' vs "...").

The words you need first

• Meta-command - a psql command that starts with a backslash (\dt, \x). psql handles it itself (often by sending catalog queries); it never goes to the server as text.
• Statement terminator - SQL is sent when psql sees ; (or \g). Without it, psql waits for more lines.
• Catalog - the system tables (pg_class, pg_namespace, pg_roles, ...) that describe everything in the cluster. \d and friends are queries on it.
• Autocommit - psql's default: each statement is its own transaction unless you say BEGIN.

Connecting

The lab database is orders (the setup made it: customers, products, orders, order_items). Connect as the superuser, over the socket:

$ sudo -u postgres psql -d orders
psql (18.6 (Ubuntu 18.6-0ubuntu0.26.04.1), server 18.3)
Type "help" for help.

orders=# \conninfo
           Connection Information
      Parameter       |        Value
----------------------+---------------------
 Database             | orders
 Client User          | postgres
 Socket Directory     | /var/run/postgresql
 Server Port          | 5432
 Options              |
 Protocol Version     | 3.0
 Password Used        | false
 GSSAPI Authenticated | false
 Backend PID          | 17412
 SSL Connection       | false
 Superuser            | on
 Hot Standby          | off
(12 rows)

orders=# \q

The prompt tells you three things: the database (orders), the state (= ready for a new statement, - in the middle of one, ' or ( inside a string or parentheses) and who you are (# superuser, > everyone else). In a transaction it gains *; after an error inside a transaction, !.

Every connection option has a flag, an environment variable and a key in a connection string - the same keys libpq (and every driver built on it) uses:

Two ways to write the whole target in one argument:

psql "host=127.0.0.1 port=5432 dbname=orders user=orders_ro sslmode=require"
psql "postgresql://[email protected]:5432/orders?sslmode=require"

A host that starts with / is a socket directory; anything else is TCP. That difference matters: -h localhost goes through TCP and pg_hba.conf's host lines (password), no -h goes through the socket and the local lines (peer).

Passwords: never put them on the command line (they end up in shell history and in ps). ~/.pgpass holds hostname:port:database:username:password lines and must be mode 0600 or libpq ignores it with a warning. PGPASSWORD works but leaks into the environment of every child process.

Finding your way around

$ sudo -u postgres psql -d orders
psql (18.6 (Ubuntu 18.6-0ubuntu0.26.04.1), server 18.3)
Type "help" for help.

orders=# \l
                                                       List of databases
   Name    |    Owner     | Encoding | Locale Provider | Collate |  Ctype  | Locale | ICU Rules |       Access privileges
-----------+--------------+----------+-----------------+---------+---------+--------+-----------+-------------------------------
 orders    | orders_owner | UTF8     | libc            | C.UTF-8 | C.UTF-8 |        |           | =T/orders_owner              +
           |              |          |                 |         |         |        |           | orders_owner=CTc/orders_owner+
           |              |          |                 |         |         |        |           | orders_app=c/orders_owner    +
           |              |          |                 |         |         |        |           | orders_ro=c/orders_owner
 postgres  | postgres     | UTF8     | libc            | C.UTF-8 | C.UTF-8 |        |           |
 template0 | postgres     | UTF8     | libc            | C.UTF-8 | C.UTF-8 |        |           | =c/postgres                  +
           |              |          |                 |         |         |        |           | postgres=CTc/postgres
 template1 | postgres     | UTF8     | libc            | C.UTF-8 | C.UTF-8 |        |           | =c/postgres                  +
           |              |          |                 |         |         |        |           | postgres=CTc/postgres
(4 rows)

orders=# \dt
               List of tables
 Schema |    Name     | Type  |    Owner
--------+-------------+-------+--------------
 public | customers   | table | orders_owner
 public | events      | table | orders_owner
 public | order_items | table | orders_owner
 public | orders      | table | orders_owner
 public | products    | table | orders_owner
(5 rows)

orders=# \d orders
                                    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
 total_cents | integer                  |           | not null |
 created_at  | timestamp with time zone |           | not null | now()
 updated_at  | timestamp with time zone |           | not null | now()
Indexes:
    "orders_pkey" PRIMARY KEY, btree (id)
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)

orders=# \q

\l lists databases with owner, encoding, locale and access privileges. \dt lists tables in the schemas on your search_path. \d orders is the one you will use most: columns with types, nullability and defaults, then indexes, constraints and foreign keys pointing in and out. Add + for more (\d+ adds storage, compression, statistics target and the table's size; \dt+ adds sizes):

$ sudo -u postgres psql -d orders -c '\dt+'
                                          List of tables
 Schema |    Name     | Type  |    Owner     | Persistence | Access method |  Size   | Description
--------+-------------+-------+--------------+-------------+---------------+---------+-------------
 public | customers   | table | orders_owner | permanent   | heap          | 392 kB  |
 public | events      | table | orders_owner | permanent   | heap          | 2280 kB |
 public | order_items | table | orders_owner | permanent   | heap          | 2064 kB |
 public | orders      | table | orders_owner | permanent   | heap          | 3000 kB |
 public | products    | table | orders_owner | permanent   | heap          | 48 kB   |
(5 rows)
$ sudo -u postgres psql -d orders -c '\di'
                          List of indexes
 Schema |        Name         | Type  |    Owner     |    Table
--------+---------------------+-------+--------------+-------------
 public | customers_email_key | index | orders_owner | customers
 public | customers_pkey      | index | orders_owner | customers
 public | events_pkey         | index | orders_owner | events
 public | order_items_pkey    | index | orders_owner | order_items
 public | orders_pkey         | index | orders_owner | orders
 public | products_pkey       | index | orders_owner | products
 public | products_sku_key    | index | orders_owner | products
(7 rows)

Patterns work everywhere: \dt order*, \df *size*. To see the query behind a meta-command, start psql with -E (--echo-hidden) - the best way to learn the catalog.

Reading results

Wide rows wrap and become unreadable. Expanded display prints one field per line:

$ sudo -u postgres psql -d orders
psql (18.6 (Ubuntu 18.6-0ubuntu0.26.04.1), server 18.3)
Type "help" for help.

orders=# \x
Expanded display is on.
orders=# select * from customers where id = 42;
-[ RECORD 1 ]----------------------
id         | 42
email      | [email protected]
name       | Customer 42
country    | NL
created_at | 2026-01-02 18:00:00+00

orders=# \x
Expanded display is off.
orders=# \q

\x auto switches only when a row would not fit. \gx instead of ; expands a single query. \timing prints how long each statement took:

$ sudo -u postgres psql -d orders
psql (18.6 (Ubuntu 18.6-0ubuntu0.26.04.1), server 18.3)
Type "help" for help.

orders=# \timing on
Timing is on.
orders=# select status, count(*) from orders group by status order by 2 desc;
  status   | count
-----------+-------
 paid      | 15000
 shipped   | 10000
 refunded  |  5000
 cancelled |  5000
 new       |  5000
(5 rows)

Time: 12.333 ms
orders=# \q

\watch 2 re-runs the last query every 2 seconds - the poor man's dashboard during an incident (select state, count(*) from pg_stat_activity group by 1 \watch 2). Ctrl+C stops it.

psql in scripts

For scripts, cron jobs and quick shell pipelines, the flags you want:

-At is the combination for "give me just the value":

$ sudo -u postgres psql -d orders -Atc "select count(*) from orders where status = 'refunded'"
5000
$ n=$(sudo -u postgres psql -d orders -XAtc "select count(*) from customers"); echo "customers: $n"
customers: 4000

The exit-code trap: by default psql runs every statement in a file and exits 0 even if some failed. Watch:

$ printf 'select 1/0;\nselect 42;\n' > /tmp/two.sql
$ sudo -u postgres psql -X -f /tmp/two.sql; echo "exit: $?"
psql:/tmp/two.sql:1: ERROR:  division by zero
 ?column?
----------
       42
(1 row)

exit: 0
$ sudo -u postgres psql -X -v ON_ERROR_STOP=1 -f /tmp/two.sql; echo "exit: $?"
psql:/tmp/two.sql:1: ERROR:  division by zero
exit: 3

Without ON_ERROR_STOP the second statement still ran and the exit code was 0 - a migration script in CI would have "passed". With it psql stopped and exited 3. psql's exit codes: 0 fine, 1 a fatal psql error (out of memory, file not found), 2 the connection to the server went bad, 3 an error in a script with ON_ERROR_STOP set. Add -1 (--single-transaction) to make a whole file all or nothing.

One more trap on Ubuntu, where home directories are mode 0750: sudo -u postgres psql -f ~/fix.sql runs psql as postgres, and postgres cannot read your home - could not open file ... Permission denied. Feed the file on standard input instead, so your own shell opens it: sudo -u postgres psql -v ON_ERROR_STOP=1 < ~/fix.sql. The same goes for \copy ... to '/home/learner/x.csv': write to stdout and redirect with >.

Note the error format, which is the same everywhere: psql:FILE:LINE: ERROR: message. Inside psql it is ERROR: with the SQLSTATE in \errverbose, and for syntax errors a LINE n: excerpt with a ^ under the spot:

$ sudo -u postgres psql -d orders -c "select id, statuss from orders limit 1"
ERROR:  column "statuss" does not exist
LINE 1: select id, statuss from orders limit 1
                   ^
HINT:  Perhaps you meant to reference the column "orders.status".

The server even guessed what you meant. Read the HINT: lines - they are often the answer.

Getting data in and out: \copy

COPY is the SQL command that bulk-loads and dumps tables - but COPY ... TO '/path' runs on the server, writes the file as the postgres OS user, and needs superuser or the pg_write_server_files role. \copy is psql's version: same syntax, the file is read or written by psql, on your side, with your permissions. On a managed database (RDS, Azure) where you have no server filesystem, \copy is the only one that works.

$ sudo -u postgres psql -d orders -c "\copy (select id, email, country from customers where country = 'RO' order by id limit 5) to '/tmp/ro.csv' with (format csv, header)"
COPY 5
$ cat /tmp/ro.csv
id,email,country
4,[email protected],RO
10,[email protected],RO
16,[email protected],RO
22,[email protected],RO
28,[email protected],RO

COPY 5 is the row count. Loading is the same with from: \copy customers (email, name, country) from 'new.csv' with (format csv, header).

Editing, scripts and settings

• \e opens the last query (or a new one) in $EDITOR; save and quit runs it. \ef function_name edits a function.
• \i file.sql runs a file from inside psql; \o file sends results to a file until \o.
• \set sets psql variables: \set ON_ERROR_STOP on, \set id 42 then select * from orders where id = :id; - and :'name' quotes a value as a string literal safely.
• ~/.psqlrc runs at every start (unless -X). Useful lines:

\set ON_ERROR_ROLLBACK interactive
\x auto
\timing on
\set PROMPT1 '%n@%M:%> %/%R%x%# '
\pset null '(null)'

ON_ERROR_ROLLBACK interactive puts an invisible savepoint before each statement you type, so a typo inside a long BEGIN block does not throw away the whole transaction.

The open-transaction trap

psql autocommits. But the moment you type BEGIN, everything you do holds locks until you COMMIT or ROLLBACK - and if you then go and look at something else, your session sits idle in transaction with its locks, blocking the application. The prompt warns you: *.

$ sudo -u postgres psql -d orders
psql (18.6 (Ubuntu 18.6-0ubuntu0.26.04.1), server 18.3)
Type "help" for help.

orders=# begin;
BEGIN
orders=*# update orders set status = 'paid' where id = 1;
UPDATE 1
orders=*# rollback;
ROLLBACK
orders=# \q

Rule for production: if you open a transaction by hand, end it before you look away. For anything risky, write it as BEGIN; ...; then check the row counts, then COMMIT - never UPDATE without a WHERE you have first run as a SELECT.

In an interview: "How do you run a SQL migration script safely with psql in CI?" - psql -X -v ON_ERROR_STOP=1 -1 -f migrate.sql: no ~/.psqlrc, stop at the first error with exit code 3 instead of carrying on and exiting 0, and -1 makes the file one transaction so a failure leaves nothing half done.

What you can do now

• Connect with flags, environment variables, a conninfo string or a URI, and know when you are on the socket and when on TCP.
• Find tables, indexes, roles and settings with \l \dt \d \di \du \dconfig.
• Use \x, \timing, \watch and \copy, and edit with \e.
• Script psql properly: -XAt, --csv, -v ON_ERROR_STOP=1, -1, exit codes.
• Spot (and avoid leaving) an open transaction from the prompt.