How to use variable in pgsql's \copy command?

Question

In my postgresql's shell,set variable's value as '2023-11-01':

\set date  '2023-11-01'

Call it :

\echo :'date'
'2023-11-01'
\echo :date
2023-11-01

Use it in select

select * from quote where date=:'date';

It output all records in quote when date is 2023-11-01,i want to copy all the records into /tmp/records.csv:

\copy (select * from quote where date=:'date')  to '/tmp/result.csv' 
ERROR:  syntax error at or near ":"
LINE 1: COPY  ( select * from quote where date=: 'date' ) TO STDOU...

Same error for:

\copy (select * from quote where date=:date)  to '/tmp/result.csv' 

How to fix it?

Answer 1

Per the docs psql:

\copy ... Unlike most other meta-commands, the entire remainder of the line is always taken to be the arguments of \copy, and neither variable interpolation nor backquote expansion are performed in the arguments.

Tip

Another way to obtain the same result as \copy ... to is to use the SQL COPY ... TO STDOUT command and terminate it with \g filename or \g |program. Unlike \copy, this method allows the command to span multiple lines; also, variable interpolation and backquote expansion can be used.

In your case:

copy (select * from quote where date=:'date')  to stdout \g '/tmp/result.csv'