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

Borszcz

possible duplicate of COPY function in PostgreSQL – Knightly

despite they discuss import I use same semantics and it doesn't work for me – Borszcz

Are you trying it with the triple backslashes (which I assume are typos)? – Knightly

nope, double, preview did show single slash when I add double – Borszcz

Use a forward slash on Windows: 'C:/temp/city.csv' – Towage

same result as with E'C:/temp/city.csv'' – Borszcz

A stab in the dark - try putting temp in all-caps. – Knightly

Didn't work out, i tried copy city.csv in emty folder and folder with file city.csv still nothing – Borszcz

Is the database server on the same machine that you are running copy from? – Knightly

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.

Knightly

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.

Pyonephritis

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;

Object

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.

Nummular

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 ',';

Theatricalize

This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review – Aneroidograph