CODEWITHSUNDEEP
postgresqlexport-to-csv
junior
junior

Reputation: 123

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

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

Upvotes: 12

Views: 43050

Answers (5)

Miguel S
Miguel S

Reputation: 11

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

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

Upvotes: 0

bjrne
bjrne

Reputation: 375

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.

Upvotes: 1

Vy Do
Vy Do

Reputation: 52516

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. enter image description here

Upvotes: 7

kidnan1991
kidnan1991

Reputation: 366

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;

Upvotes: 1

Politank-Z
Politank-Z

Reputation: 3719

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.

Upvotes: 15

Related Questions