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!

Answer 1

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

Answer 2

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