Export to CSV from postgresql

Question

I want to export a postgresql table to a csv file. I have tried two ways, however both are unsuccessful for different reasons.

In the first case, you can see what I run and what I get bellow:

COPY demand.das_april18_pathprocess TO '/home/katerina/das_april18_pathprocess.csv' DELIMITER ',' CSV HEADER;


No such file or directory
SQL state: 58P01

I need to mention that in the location /home/katerina/ I have created an empty file named das_april18_pathprocess.csv, for which I modified the Permission settings to allow Read and Write.

In my second try, the query is executed without any errors but I cannot see the csv file. The command that I run is the following:

COPY demand.das_april18_pathprocess TO '/tmp/das_april18_pathprocess.csv' DELIMITER ',' CSV HEADER;

In the /tmp directory there is no cvs file.

Any advice on how to export the table to csv file with any way is really appreciated!

jmelesky · Accepted Answer · 2018-08-28 15:43:57Z

2

Ah, you run into a common problem -- you're creating a file on the server's filesystem, not your local filesystem. That can be a pain.

You can, however, COPY TO STDOUT, then redirect the result.

If you're using linux or another unix, the easiest way to do this is from the command line:

$ psql <connection options> -c "COPY demand.das_april18_pathprocess TO STDOUT (FORMAT CSV)" > das_april18_pathprocess.csv

edited Aug 28, 2018 at 15:43

answered Aug 27, 2018 at 22:14

jmelesky

4,1101 gold badge26 silver badges25 bronze badges

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

4 Comments

katerinaD Over a year ago

It does not work. If I include the -e part when connecting, it returns me a message that this part will be ignored. If I first connect to the database and then type in the -e part, I new command line appears-not sure what this means. The table is really big so it should take several seconds to be written to a file.

jmelesky Over a year ago

Oh, goodness, my apologies. That should be a "-c", not a "-e". I'll edit the response.

katerinaD Over a year ago

One more thing. I am a bit confused with which should be the location of the output file. Probably my /home directory?

katerinaD Over a year ago

Sorry, ignore my previous commend. The file was located in the /home directory. Thank you jmelesky.

Slumdog · Accepted Answer · 2018-08-27 21:23:24Z

0

copy  ( select * from demand.das_april18_pathprocess)  to '/home/katerina/das_april18_pathprocess.csv' with CSV header ;

answered Aug 27, 2018 at 21:23

Slumdog

4702 silver badges5 bronze badges

1 Comment

katerinaD Over a year ago

Hm it does not work. This results to the same error as described in my question.

Collectives™ on Stack Overflow

Export to CSV from postgresql

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related