All we need is an easy explanation of the problem, so here it is.
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 ',...
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
(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
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂