Exporting csv file from postgresql database server to local machine

Question

I'm trying to download the select output query as a csv file into my local machine, from the server where the database is stored. I tried using the \copy command :

\copy (SELECT COUNT(*) FROM table) to 'C:\Folder\temp.csv' With CSV DELIMITER ',' HEADER;

It gives output COPY 1 in the terminal, but doesn't generate any csv file in the location. Would really appreciate any help, thanks !

Could it be that you're closing the path with a ` instead of a single quote ' ? — Jim Jones
– Jim Jones, Commented Jun 7, 2021 at 15:09
Not sure if this is cut and paste error, but 'C:\Folder\temp.csv`` should be 'C:\Folder\temp.csv'. This command(with changed to ') worked for me on a Linux machine, in that I got COPY 1 and a temp.csv file was created. Do you have correct permissions on that directory? Also try it with 'C:/Folder/temp.csv'. — Adrian Klaver
– Adrian Klaver, Commented Jun 7, 2021 at 15:10
Really sorry, I typed it out wrong here and I've corrected it now. And no, when I use the path C:/Folder/temp.csv it says "No such file or directory". — Suraj
– Suraj, Commented Jun 7, 2021 at 15:22

Jim Jones · Accepted Answer · 2021-06-07 15:13:17Z

1

You're closing the file path with a ` instead of with a ', so I guess there is a file somewhere with different name than the one you're expecting ;) Otherwise the command is just fine

\copy (SELECT COUNT(*) FROM t) TO 'C:\Folder\temp.csv' WITH CSV DELIMITER ',';

answered Jun 7, 2021 at 15:13

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

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

10 Comments

Adrian Klaver Over a year ago

Actually closing with a ` will get you a syntax error: ERROR: syntax error at or near "," LINE 1: ...PY ( SELECT COUNT ( * ) FROM animals ) TO STDOUT ,' HEADER;

Jim Jones Over a year ago

@AdrianKlaver I also think so, but since I do not have a windows machine to test exactly what happens, I assume the file is being created. In my linux environment I also get an error

Jim Jones Over a year ago

@TonyStark do you get any result with this command: \copy (SELECT COUNT(*) FROM t) TO STDOUT WITH HEADER CSV DELIMITER ',';?

Suraj Over a year ago

Yes, I get this : COPY 1. I'm running this on my company database server since I need to get data from there, is it possible that the server isn't allowing files to be downloaded.

Suraj Over a year ago

Yeah, I get the total rows as the output, 10040. Will try the psql command, thanks man.

|

Collectives™ on Stack Overflow

Exporting csv file from postgresql database server to local machine

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related