Export Postgres table to csv

Question

I am trying to export my Postgres table to a csv on my desktop and I get this error:

ERROR: could not open file "C:\Users\blah\Desktop\countyreport.csv" for writing: Permission denied
SQL state: 42501

This is my query which I believe is the correct syntax

COPY countyreport TO 'C:\\Users\\blah\\Desktop\\countyreport.csv' DELIMITER ',' CSV HEADER;

Answer 1

According to the user manual:

Files named in a COPY command are read or written directly by the server, not by the client application.

https://www.postgresql.org/docs/current/static/sql-copy.html

The common mistake is to believe that the filesystem access will be that of the (client) user, but it's not. It's normal to run the postgresql server as its own user. Therefore action carried out by the server will be done as a different OS user to the client. The server is usually run as an OS user postgres.

Assuming that you are running the server on your local machine then the simplest way to fix it would be to give postgres access to your home directory or desktop. This can be done by changing the windows security settings on your home directory.

Before you do this.... Stop and think. Is this what you are looking for? If the server is in development then will it always run on the user's machine. If not then you may need to use COPY to write to the stdout. See the manual for information on this.