How do I escape characters when running COPY command on command line?

Question

I'm using Postgres 14 on CentOS 7. I would like to run a copy command from a bash script. I first tried running the command on the command line

$ PGPASSWORD=$DB_PASS psql -U $DB_USER -d $DB_NAME -c 'COPY myapp_currencyprice to STDOUT WITH (DELIMITER ",", FORMAT CSV, HEADER) \g /tmp/prices.csv'
ERROR: syntax error at or near "\"
LINE 1: ...o STDOUT WITH (DELIMITER ",", FORMAT CSV, HEADER) \g /tmp/pr...

What's the right way to escape the delimiter? I've tried changing my single and double quotes but to no avail.

Answer 1

The -c option to psql does not support mixtures of SQL commands and metacommands. And since that is the only way \g can sensibly be used, that means \g is not supported in -c

There are no shortage of other options though.

psql -c 'COPY myapp_currencyprice to STDOUT WITH (DELIMITER ",", FORMAT CSV, HEADER)' > /tmp/prices.csv
psql -c '\COPY myapp_currencyprice to /tmp/prices.csv WITH (DELIMITER ",", FORMAT CSV, HEADER)'
psql -f <(echo 'COPY myapp_currencyprice to STDOUT WITH (DELIMITER ",", FORMAT CSV, HEADER) \g /tmp/prices.csv')
psql <<END
COPY myapp_currencyprice to STDOUT WITH (DELIMITER ",", FORMAT CSV, HEADER) \g /tmp/prices.csv
END