Using variables when executing single command in PSQL

Question

When using PSQL's variables, I can run it as follows:

psql -d database -v var="'123'"

And I will then have access to the variable var when I type the following in the PSQL terminal:

select * from table where column = :var;

This variable feature also works when the SQL is read from a file:

psql -d database -v var="'123'" -f file.sql

But when I try to run the SQL as a single command:

psql -d database -v var="'123'" -c "select * from table where column = :var;"

I can't access the variable and get the following error:

ERROR:  syntax error at or near ":"

Is it possible to pass variables to single SQL commands in PSQL?

Answer 1

It turns out that, as man psql explains, the -c command is limited to SQL that "contains no psql-specific features":

   -c command, --command=command
       Specifies that psql is to execute one command string, command, and then exit. This is useful in shell
       scripts. Start-up files (psqlrc and ~/.psqlrc) are ignored with this option.

       command must be either a command string that is completely parsable by the server (i.e., it contains no
       psql-specific features), or a single backslash command. Thus you cannot mix SQL and psql meta-commands
       with this option. To achieve that, you could pipe the string into psql, for example: echo '\x \\ SELECT
       * FROM foo;' | psql. (\\ is the separator meta-command.)

It looks like I can do what I want by passing in the SQL using stdin:

echo "select * from table where column = :var;" | psql -d database -v var="'123'"