CODEWITHSUNDEEP
postgresqlcsvpostgresql-copy
yungblud
yungblud

Reputation: 418

Postgres \copy throws a syntax error at STDOUT when exporting as CSV (v8.0.2)

I'm trying to locally download the results of a query as csv on a postgres instance that I have read-only access to (v8.0.2). I've truly read 10 different ways of going about this and have tried implementing them all (here, here, here, here, and here), but every single time I try to execute the copy command, I get the following error:

ERROR:  syntax error at or near "STDOUT"

Here are five of the roughly 20 permutations I have tried. The foo_bar table is a temporary table that was created as the output of a query.

=> \copy "pg_temp_5.foo_bar" TO '/Users/baz/Downloads/foo_bar.csv' DELIMITER ',' CSV
ERROR:  syntax error at or near "STDOUT"
LINE 1: COPY  "pg_temp_5.foo_bar" TO STDOUT DELIMITER ',' CSV

=> \copy "pg_temp_5.foo_bar" TO '/Users/baz/Downloads/foo_bar.csv' CSV
ERROR:  syntax error at or near "STDOUT"
LINE 1: COPY  "pg_temp_5.foo_bar" TO STDOUT CSV

=> \copy "pg_temp_5.foo_bar" TO '/Users/baz/Downloads/foo_bar.csv' WITH FORMAT "csv"
ERROR:  syntax error at or near "STDOUT"
LINE 1: COPY  "pg_temp_5.foo_bar" TO STDOUT WITH FORMAT "csv"

=> \copy pg_temp_5.foo_bar TO '/Users/baz/Downloads/foo_bar.csv' With CSV
ERROR:  syntax error at or near "STDOUT"
LINE 1: COPY  pg_temp_5.foo_bar TO STDOUT With CSV

=> \copy foo_bar TO foo_bar.csv With CSV
ERROR:  syntax error at or near "STDOUT"
LINE 1: COPY  foo_bar TO STDOUT With CSV

I figure that it isn't a permissions issue, as if it were, I would be thrown a "permission denied" when I try to run the command. I also know that there have been similar issues with the \copy command as documented by postgres here, but nothing has been noted specifically with regards to my case. Any help would be greatly appreciated!

Upvotes: 4

Views: 1837

Answers (2)

Komathi
Komathi

Reputation: 1

Please follow below steps to connect to psql from command prompt and execute. Verify user account have access to underlying schema and table in postgresql and also have access to directory tried to generate the data file.

  1. psql -h hostname -U username -d databasename -p 5432

  2. Provide password when prompted from user account

3.Enter this command: \copy schemaname.tablename TO 'Full Path including filename format' DELIMITER ',' CSV HEADER ENCODING 'UTF8' QUOTE '"' ESCAPE '' NULL 'NULL';

Refer below as example. First try with table exists in schema.

Example: \copy test_schema.table1 TO 'E:/in/user/table1.csv' DELIMITER ',' CSV HEADER ENCODING 'UTF8' QUOTE '"' ESCAPE '' NULL 'NULL';

Upvotes: 0

matt0089
matt0089

Reputation: 471

I recently got this error. I realized (as mentioned in combinatorist's comment) the reason was I was logged into an Amazon redshift db, and not Postgres (psql can be used for both).

My guess is it's not implemented for Redshift. The workaround I used was Redshift's unload statement to unload the data to s3.

unload ('select * from venue')
to 's3://mybucket/unload/'
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole';

https://docs.aws.amazon.com/redshift/latest/dg/r_UNLOAD.html https://docs.aws.amazon.com/redshift/latest/dg/r_UNLOAD_command_examples.html#unload-examples-venue

Upvotes: 1

Related Questions