Error with non-standard identifier in \copy command

Question

Would like to be able to add characters like '-' in the schema name when running COPY command in PostgreSQL. Any way to get around this?

psql -d postgres -c "\COPY (SELECT * FROM test-schema.tableName) TO data.csv DELIMITER ',' CSV"

ERROR:  syntax error at or near "-"`enter code here`
LINE 1: COPY  ( SELECT * FROM test-schema.tableName ) TO STDOUT DELIMITER ',...

Answer 1

The error is with the illegal identifiers for schema and table name, and not immediately related to COPY (nor to the psql meta-command \copy, which you actually use). Use double-quotes:

"test-schema"."tableName"

(Assuming you registered the table as "tableName", not as tablename, which would be preferable.)
Since you double-quote the command argument in the shell, too, you need to escape the added double-quotes:

psql -d postgres -c "\copy (SELECT * FROM \"test-schema\".\"tableName\") TO data.csv DELIMITER ',' CSV;"

Or use single-quotes in the shell and dollar-quotes for the string:

psql -d postgres -c '\copy (SELECT * FROM "test-schema"."tableName") TO data.csv DELIMITER $$,$$ CSV;'

You wouldn't encounter any of these problems with legal, lower-case identifiers, which do not require double-quoting. See:

• Are PostgreSQL column names case-sensitive?
• Insert text with single quotes in PostgreSQL
• What are '$$' used for in PL/pgSQL