Problem with postgres \copy command for export query resullt to file

Question

I tried to export a select query result to a csv file. I used Postgres \copy metacommand and command line (psql) to do it. But I got a Syntax error and can't understand why. The Query looks fine to me. Maybe the reason for using metacommand instead of COPY? The query

\copy
  (
    SELECT geo_name, state_us_abbreviation, housing_unit_count_100_percent
    FROM us_counties_2010
    ORDER BY housing_unit_count_100_percent DESC
    LIMIT 20
  )
TO '/username/Desktop/us_counties_2010_export.csv' 
WITH(FORMAT CSV, HEADER, DELIMITER '|');

Error message

ERROR:  syntax error at or near "TO"
LINE 7: TO '/username/Desktop/us_counties_2010_export.csv'

To rule out the obvious: you are using psql to run this? — user330315
– user330315, Commented May 10, 2021 at 11:25
using psql run psql -d yourdb -c "COPY .... TO STDOUT " > /username/Desktop/us_counties_2010_export.csv — Jim Jones
– Jim Jones, Commented May 10, 2021 at 11:52

jjanes · Accepted Answer · 2021-05-10 16:20:30Z

2

\copy is a metacommand given to psql, not a regular command sent to the server. So like other metacommands, the entire \copy command must all be given on one line and doesn't end in a ; but rather a newline.

If you look closely, you will see the first error you got was \copy: arguments required

answered May 10, 2021 at 16:20

jjanes

45k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Crampus Over a year ago

worked in one line and without ; Thanks a lot!

Collectives™ on Stack Overflow

Problem with postgres \copy command for export query resullt to file

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related