skip to content

In a psql deployment script, how do you pass parameters safely with -v and :'var', and generate DDL from a catalog query with \gexec?

level: seniorimportance: should knowfreq 30%

answer

  1. client-side text substitution
  2. colon, quote, name
  3. every result cell is a statement
  4. format() with %I
  5. never inside quotes or $$

basics

~20 s

psql substitutes variables set with -v or \set before sending: :'name' as an escaped literal, :"name" as a quoted identifier. \gexec executes each cell of a query's result as its own statement, so build those statements with format() and %I.

solid answer

~50 s

`psql -v schema=tracking -f build.sql` sets a psql variable; in the file, `:schema` pastes the raw text, `:'schema'` pastes it as a correctly escaped SQL literal and `:"schema"` as a quoted identifier. The substitution is textual and happens in psql, not a server-side bind parameter, so the quoting forms are what stop a value containing a quote from breaking or rewriting the statement. psql does not substitute inside quoted literals, dollar-quoted bodies such as `DO $$ ... $$`, or `-c` strings. `\gexec` sends the query, then executes every non-NULL cell of the result as a separate statement, in order, each in its own transaction unless you opened one, which is what `CREATE INDEX CONCURRENTLY` across many partitions needs. Build the text with `format('%I.%I', ...)`, set `ON_ERROR_STOP`, and set `ECHO` to `queries` to log each generated statement.

code

bash · 2 lines
bash
psql -X -v ON_ERROR_STOP=1 -v schema=tracking \
     -d "$DATABASE_URL" -f build_partition_indexes.sql

go deeper

for a junior

Know that psql has its own variables, set with \set or -v, and that :name inserts a value into a query.

for a middle

Explain the three reference forms and when each is safe, plus the places psql does not interpolate: quoted literals, dollar quotes, -c strings and \copy.

for a senior

Show safe generated DDL: format('%I') in the generator, a review run before \gexec, ON_ERROR_STOP and ECHO, and no -1 when the generated statements refuse transaction blocks.

for a principal

Weigh psql scripting against a migration framework: plain psql files are transparent and dependable, but parameter validation, logging and retry rules must be written down and enforced by the team.

## psql variables are client-side text substitution A **psql variable** is a name-value pair held by psql, set with `\set name value` inside a session or with `-v name=value` (`--set`, `--variable`) on the command line. When psql reads a line, it replaces a reference to a variable that is set before sending anything to the server. This is **text substitution in the client**: the server never sees the variable, and it is not a bind parameter like `$1`. Three reference forms exist, and the choice between them is a correctness and safety decision. Suppose the value is `O'Brien`: | Reference | psql sends | Use for | |---|---|---| | `:who` | `O'Brien` (raw, unbalanced quote) | numbers or fragments you fully control | | `:'who'` | `'O''Brien'` | a value used as a **SQL literal** | | `:"who"` | `"O'Brien"` | a value used as an **identifier** (schema, table, column) | The raw form copies the text literally, so a value with a quote, or one containing a backslash command, can break the statement or change what it does. In scripts that take parameters from a pipeline, use `:'name'` and `:"name"`. ## Where interpolation does not happen - **Inside quoted literals and identifiers.** `':who'` stays the four characters `:who` in quotes; psql documents that this does not work and would be unsafe if it did. - **Inside dollar-quoted strings**, so a `DO $$ ... $$` block or a function body never sees `:'schema'`; the server receives the text unchanged and rejects it. - **In `-c` strings.** A `-c` command must be fully parsable by the server, so `psql -v id=5 -c 'SELECT :id'` sends `:id` as is. Use `-f`, standard input or a here-document. - **When the variable is unset.** Since colons are legal SQL, psql leaves `:name` alone if `name` has no value. With `ON_ERROR_STOP`, the resulting syntax error stops the run. - **In `\copy`'s arguments.** `:{?name}` is replaced by TRUE or FALSE depending on whether `name` is set, which makes `\if :{?schema}` a clean guard at the top of a script. ## \gexec: SQL that writes SQL **`\gexec`** ends a query instead of `;`. psql: 1. sends the query and receives its result; 2. treats every column of every row as a complete SQL statement; 3. executes them in row order, left to right within a row, skipping NULL cells; 4. continues past a failed statement unless `ON_ERROR_STOP` is set. The generated statements are sent literally, so they cannot contain meta-commands or psql variables; interpolate in the generating query instead. Each one runs in its own transaction unless a `BEGIN` is open, so statements that refuse transaction blocks work under `\gexec`, but not under `psql -1`. ## A worked migration: one concurrent index per partition PostgreSQL cannot build an index concurrently on a partitioned table itself, so a common approach is to build one on each partition. Attaching those to a parent index afterwards is a schema-change topic; generating the per-partition statements is psql's job: ```sql \set ON_ERROR_STOP on \set ECHO queries SELECT format('CREATE INDEX CONCURRENTLY IF NOT EXISTS %I ON %I.%I (parcel_id)', c.relname || '_parcel_id_idx', n.nspname, c.relname) FROM pg_inherits AS i JOIN pg_class AS c ON c.oid = i.inhrelid JOIN pg_namespace AS n ON n.oid = c.relnamespace WHERE i.inhparent = format('%I.parcel_events', :'schema')::regclass ORDER BY c.relname \gexec ``` Run it with `psql -X -v schema=tracking -f build_indexes.sql`, without `-1`. Why not a `DO` block that loops and calls `EXECUTE format(...)`? Because the whole `DO` block runs inside one transaction on the server, and `CREATE INDEX CONCURRENTLY` refuses that with `cannot be executed from a function`. `\gexec` sends each generated statement from the client as its own top-level command, which is exactly what the concurrent build needs. ## Guard rails - **Quote names with `format('%I')`** (or `quote_ident`) in the generating query; string concatenation breaks on mixed-case or unusual names. - **Review first:** run the generating query with `;` and read its output before switching to `\gexec`. - **`ON_ERROR_STOP`** stops at the first failed generated statement; without it, \gexec carries on. - **`ECHO queries`** (or `all`) writes every generated statement to the log, which is what an auditor or a post-incident review needs. - **`\gset`** is the sibling for decisions: it stores a one-row result in variables that `\if` can test.

  • What happens if the script references :'schema' but the caller forgot -v schema=...?
    psql substitutes only variables that are set, so `:'schema'` reaches the server as literal text and fails as a syntax error; with ON_ERROR_STOP the run stops there with exit status 3. For a clearer message, test `\if :{?schema}` near the top of the script and print usage in the `\else` branch.
  • When is \gset more useful than \gexec?
    When the script needs to decide rather than generate. `SELECT count(*) > 0 AS has_invalid FROM pg_index WHERE NOT indisvalid \gset` stores `t` or `f` in the variable `has_invalid`, and `\if :has_invalid` branches on it. `\gexec` is for when the query's output is itself the SQL to run.

  • Saying :'var' becomes a server-side bind parameter like $1
  • Writing ':var' inside quotes and expecting the value to be substituted
  • Believing \gexec runs all the generated statements as one transaction
  • Expecting psql -v id=5 -c 'SELECT :id' to substitute the variable
  • Building generated DDL with || concatenation instead of format('%I')