Set placeholder argument in psql

Question

Using psql, it seems possible to set named arguments using \pset somevar 'hello'. However there appears to be no way to set a positional argument like $1. This makes it difficult to paste in SQL snippets that use positional arguments. In the sqlite3 CLI, it's possible to do .param set ?1 'hello' for this purpose. How do I do this with psql?

Answer 1

You need \set (not \pset!) to set psql variables.

Then you can use SQL interpolation to insert those variables into SQL commands, optionally quoting your values. Demo:

test=> \set a 10
test=> \set foo bar
test=> \set col1 column1
test=> \set col2 'Col 2'

test=> \echo :a :'foo' :col1 :"col2"
10 'bar' column1 "Column 2"
test=> SELECT :a AS :col1, :'foo' AS :"col2";
 column1 | "Col 2" 
---------+---------
      10 | bar
(1 row)

Not sure how "positional arguments" come into this ...

Answer 2

You can use a prepared statement:

PREPARE stmt(integer) AS
   SELECT name FROM person WHERE id = $1;

EXECUTE stmt(42);