psql for DBAs: variables, \gset, \gexec, \if and \watch

conceptThe concept

Automatic variables include ERROR, SQLSTATE, ROW_COUNT, LAST_ERROR_MESSAGE, DBNAME, USER and SERVER_VERSION_NUM. Real psql starts with ON_ERROR_STOP off; this course runs with it on, as scripts should.

whyWhy it matters

Most production changes are run through psql: migrations, runbooks, cron jobs. Without ON_ERROR_STOP a failed statement is printed and the script continues, applying half a change. With variables, \gset and \gexec you can write one script that adapts to the server instead of copy-pasting SQL per table.

Use caseReal-world scenario

A runbook needs to ANALYZE every table that changed more than 10% since its last analyze, but only on servers older than 17, and print how many tables it touched. One psql script does it: \gset captures the server version, \if branches, a SELECT with format('ANALYZE %I.%I', ...) and \gexec generates and runs the statements, and ON_ERROR_STOP plus --single-transaction make a failure stop everything cleanly.

Try itExamples

Run and edit these in the interactive lesson.

\set status 'shipped'
\set tbl orders
\echo counting :tbl with status :'status'
SELECT count(*) AS n FROM :"tbl" WHERE status = :'status';

SELECT count(*) AS orders, max(created_at)::date AS latest FROM orders \gset stat_
\echo :stat_orders orders, the latest on :stat_latest

SELECT format('SELECT %L AS table_name, count(*) AS row_count FROM %I', relname, relname)
FROM pg_class
WHERE relnamespace = 'public'::regnamespace AND relname IN ('customers', 'products', 'employees')
ORDER BY relname \gexec

SELECT current_setting('server_version_num')::int >= 170000 AS has_17 \gset
\if :has_17
  \echo 17 or later: checkpoint counters live in pg_stat_checkpointer
  SELECT num_timed >= 0 AS ok FROM pg_stat_checkpointer;
\else
  \echo before 17: use pg_stat_bgwriter
\endif

\set ON_ERROR_STOP off
SELECT 1 / 0 AS boom;
\echo after the error: ERROR=:ERROR SQLSTATE=:SQLSTATE
\errverbose

SELECT count(*) AS sessions FROM pg_stat_activity \watch i=1 c=2

-- ~/.psqlrc
\set QUIET 1
\pset null '(null)'
\x auto
\timing on
\set PROMPT1 '%n@%/%R%# '
\set HISTFILE ~/.psql_history- :DBNAME
\set ON_ERROR_ROLLBACK interactive
\set QUIET 0

-- run a migration: no personal psqlrc, stop at the first error, one transaction
psql -X -v ON_ERROR_STOP=1 --single-transaction -f migrate.sql "service=prod"

-- pass values in from the shell; use them as :'run_date' inside the script
psql -X -v ON_ERROR_STOP=1 -v run_date=2026-10-08 -f report.sql

-- client-side copy: the file is on your machine, no superuser needed
\copy (SELECT * FROM orders WHERE created_at > now() - interval '1 day') TO 'orders.csv' WITH (FORMAT csv, HEADER)

-- extended protocol from psql (16+): parameters sent separately from the SQL text
SELECT * FROM orders WHERE id = $1 \bind 42 \g

internalsUnder the hood

Variables are interpolated by psql before the query is sent, so the server never sees :name. :'name' is quoted with the same rules as quote_literal and :"name" like quote_ident, so they are safe; plain :name is pasted as-is. Interpolation does not happen inside quoted strings or dollar-quoted bodies. \gexec sends each generated statement separately, so statements that cannot run inside a transaction block (VACUUM, CREATE INDEX CONCURRENTLY) still work when the script is not wrapped in one.

Avoid / preferDo this, not that

psql -f migrate.sql

psql -X -v ON_ERROR_STOP=1 --single-transaction -f migrate.sql

SELECT 'DROP TABLE ' || relname || ';' FROM pg_class WHERE relname LIKE 'tmp_%' \gexec

-- review first, then run with \gexec
SELECT format('DROP TABLE %I.%I', relnamespace::regnamespace, relname)
FROM pg_class
WHERE relkind = 'r' AND relname LIKE 'tmp\_%' AND relnamespace = 'scratch'::regnamespace;

SELECT * FROM orders WHERE status = ':status';

SELECT * FROM orders WHERE status = :'status';

PitfallsCommon mistakes

• Using plain :name for a value that came from outside the script, instead of :'name' or :"name".
• Forgetting that \gset needs exactly one row; zero rows leaves the old variable values in place.
• Running \gexec on a generated list of destructive statements without reviewing it first.
• Putting VACUUM or CREATE INDEX CONCURRENTLY in a script run with --single-transaction; they cannot run in a transaction block.

LimitsWhen not to use it

When a script grows loops, retries and real error handling, move it to a proper language (Python, Go) with a driver, or to a PL/pgSQL procedure. psql scripting is for linear runbooks and generation of statements, not for programs.

SecuritySecurity notes

Never interpolate untrusted input with plain :var; use :'var' or :"var". Keep passwords out of scripts and command lines (they show in ps and shell history); use ~/.pgpass (mode 0600) or a service file. \! and \copy ... PROGRAM run commands as your OS user, so review scripts from others before running them.

ChallengeTry it yourself

SELECT customer_id AS top_customer
FROM orders
GROUP BY customer_id
ORDER BY count(*) DESC, customer_id
LIMIT 1 \gset
SELECT id, created_at::date AS ordered_on, total
FROM orders
WHERE customer_id = :top_customer
ORDER BY created_at DESC
LIMIT 3;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• psql reference
• Database cluster and schemas