How to Use Variables in PostgreSQL psql Scripts (with Examples)

The psql command-line interface for PostgreSQL lets you pass parameters into your SQL scripts using variables. The psql --help command explains how to accomplish this:

psql is the PostgreSQL interactive terminal.

Usage:
  psql [OPTION]... [DBNAME [USERNAME]]

General options:
  -f, --file=FILENAME      execute commands from file, then exit
  -v, --set=, --variable=NAME=VALUE
                           set psql variable NAME to VALUE
                           (e.g., -v ON_ERROR_STOP=1)

If I create this simple script:

SELECT
  *
FROM
  customers
WHERE
  id = :ID
;

I can invoke it via psql:

psql --file=script.sql --variable=ID=2

 id | name
----+------
  2 | Bob
(1 row)

We immediately run into problems if we don’t consider how variables are interpolated.

psql --file=script.sql --variable=ID="Roger Wilco"
psql:script.sql:7: ERROR:  syntax error at or near "Wilco"
LINE 6:   id = Roger Wilco
                     ^

Note that the variable is not being interpreted as a string, but rather directly substituted in the script itself. This is quite powerful, since it means we can pass in variables to substitute as the name of a table or column. It also means it’s extremely prone to SQL injection attacks.

psql --file=script.sql --variable=ID="42; SELECT VERSION();"

 id | name
----+------
(0 rows)

          version
----------------------------
 PostgreSQL 15.13 (Homebrew)
(1 row)

Quoting Variables as Literals vs. Identifiers in psql

We need to tell psql to quote the variables. There are two ways to quote variables:

• For quoting variables as a literal, use :'VARIABLE_NAME', for example SELECT * FROM customers WHERE id = :'USER_ID';
• For quoting variables as an identifier, use :"VARIABLE_NAME", for example SELECT * FROM customers ORDER BY :"ORDER_BY_FIELD";

SELECT
  *
FROM
  customers
ORDER BY
  :"ORDER_BY_FIELD"
LIMIT
  :'LIMIT'
;

psql --file=script.sql --variable=ORDER_BY_FIELD=name --variable=LIMIT=5

 id |  name
----+---------
  1 | Alice
  2 | Bob
  3 | Charlie
  4 | Diana
  5 | Ethan
(5 rows)

One very important caveat is that psql actually parses the SQL in order to do the substitution, and cannot replace variables inside of a string. This becomes very important if you are trying to substitute variables inside of a pl/PgSQL function or DO block, since these are executed server-side and parsed as a string, making psql substitution impossible.

DO
$$
DECLARE
  customer_name TEXT;
BEGIN
  SELECT
    name
  INTO
    customer_name
  FROM
    customers
  WHERE
    id = :'CUSTOMER_ID'
  ;

  RAISE NOTICE 'Customer % is named %', :'CUSTOMER_ID', customer_name;
END
$$;

Trying to pass the variable into this script will fail:

psql --file=script.sql --variable=CUSTOMER_ID=2
psql:script.sql:18: ERROR:  syntax error at or near ":"
LINE 13:     id = :'CUSTOMER_ID'
                  ^

Unfortunately there doesn’t seem to be a standard way to pass variables into DO blocks, the best workaround I’ve seen is to use current_setting. The downside is that your variable is stored as a string, which means you need to consider casting it to the appropriate type.

SET custom_settings.customer_id = :'CUSTOMER_ID';

DO
$$
DECLARE
  customer_name TEXT;
BEGIN
  SELECT
    name
  INTO
    customer_name
  FROM
    customers
  WHERE
    id = current_setting('custom_settings.customer_id')::INTEGER
  ;

  RAISE NOTICE 'Customer % is named %', current_setting('custom_settings.customer_id'), customer_name;
END
$$;

psql --file=script.sql --variable=CUSTOMER_ID=2
psql:script.sql:20: NOTICE:  Customer 2 is named Bob

Note that you need to prefix/namespace your setting - as I did above with custom_settings - otherwise PostgreSQL will blow up thinking you are trying to access system configuration settings.

One last note - you can’t use variables with inline commands, since --command doesn’t parse variables unless input comes via stdin (e.g., from <<< or |), as it doesn’t treat SQL the same way as file or piped input.

psql --variable=NAME=Bob --command="SELECT :'NAME';"
ERROR:  syntax error at or near ":"
LINE 1: SELECT :'NAME';
               ^

Instead, we would need to use piping:

psql --variable=NAME=Bob <<< "SELECT :'NAME';"
 ?column?
----------
 Bob
(1 row)

echo "SELECT :'NAME';" | psql --variable=NAME=Bob
 ?column?
----------
 Bob
(1 row)

Most of my knowledge in this post comes from this post by depesz. Variables in psql are powerful for dynamic scripting and automation — but they come with some gotchas. By understanding quoting rules and limitations (especially around DO blocks and inline commands), you can write safer, more maintainable SQL scripts. Happy scripting!

PostgreSQL