How to export the resulting data in PostgreSQL to .CSV?

Question

I use PostgreSQL 9.4.1

My query:

copy(select * from city) to 'C:\\temp\\city.csv'
copy(select * from city) to E'C:\\temp\\city.csv'

ERROR: relative path not allowed for COPY to file ********** Error **********

ERROR: relative path not allowed for COPY to file SQL state: 42602

Answer 1

As with this case, it seems likely that you are attempting to use copy from a computer other than the one which hosts your database. copy does I/O from the database host machine's local file system only. If you have access to that filesystem, you can adjust your attempt accordingly. Otherwise, you can use the \copy command in psql.

Answer 2

I am using pgAdmin v1.5 . The first query is

select table_name from information_schema.tables where table_catalog = 'ofbiz' order by table_name

Then I press button download, pgAdmin will return a csv file, is result set of first query.

Answer 3

It could be late but i think it can be helpful.

1. On Windows, make sure the output directory has gain the read/write right for Everyone (or you can specific user name).
2. Using slash(/) instead of backslash(), example

COPY DT1111 TO 'D:/TEST/DT1111_POST.CSV' DELIMITER ',' CSV HEADER;

Answer 4

TLDR: Make sure you also have write permissions in your copy-to location!

I had the exact same first error, ERROR: relative path not allowed for COPY to file, even though I used '/tmp/db.csv' (which is not a relative path).

In my case, the error message was quite misleading, since I was on the host machine, had an absolute filepath and the location existed. My problem was that I used the bitnami postgres:12 docker image, and the tmp folder in the container belongs to root there, while postgres and psql use the postgres user. My solution was to create an export folder there and transform the ownership to the postgres user:

mkdir /tmp/export
chown postgres:postgres /tmp/export

Then I was able to use COPY tablename TO '/tmp/export/db.csv'; successfully.

Answer 5

That's what I've done. Don't forget the preceding forward slash:

COPY (SELECT * FROM city) TO '/temp/city.csv' WITH DELIMITER ',';