redshift COPY TO file from Xen-tables not supported

Question

I am trying to copy (not unload) a table from redshift to a local file.
I run in psql:

\copy my_schema.my_table to 'my_file.csv' with csv;  

I get the error

ERROR:  COPY TO file from Xen-tables not supported

Running

\copy (select * from my_schema.my_table) to 'my_file.csv' with csv;  

raises syntax error:

ERROR:  syntax error at or near "("

How should I perform the copy?
Thanks,
Dafna

Answer 1

You can redirect the psql output to a local file:

psql [your connection options go here] -F, -A \
  -c 'select * from my_schema.my_table' >my_file.csv

-F, sets the field separator to a comma

-A gives you unaligned/unformatted output

To specify a different delimiter like pipe, use '|' instead of the -F.

Note: The above command won't tolerate newlines in text fields, they are not encoded and terminate the line prematurely.