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;