I have connected to remote server and trying to export a table data to local using the command:

copy finance.coa to 'localusername@' DELIMITER ',' CSV HEADER;

I am getting the error:

relative path not allowed for COPY to file


You can try this

  psql postgres://username:password@ip:port/database_name -c "\copy table_name TO 
 'table.csv' DELIMITER ',' CSV HEADER;"

Remove password if not needed postgres://username@ip:port/database_name.

| improve this answer | |
  • I am using this from my local :- psql postgres://postgres@ -c "\copy finance.coa to 'tabl.csv' DELIMITER ',' CSV HEADER;" This time i am getting a different error:- tabl.csv: Permission denied – akhilesh kedarisetty Aug 28 '19 at 4:54
  • psql enet -c "\copy finance.coa TO 'table.csv' DELIMITER ',' CSV HEADER;" @akhileshkedarisetty – Imanol Y. Aug 28 '19 at 7:23
  • After trying that i am getting :- psql: FATAL: role "root" does not exist – akhilesh kedarisetty Aug 29 '19 at 6:28
  • When i am using this syntax :- psql -h -U postgres -d enet -o export.csv -c 'select * from finance.coa'; the file is storing in the local. But the problem is that, It's not storing in the proper csv format. – akhilesh kedarisetty Aug 29 '19 at 7:00

There are two problems:

  • COPY writes to a file on the database server.

    If you want to write to a file on the client machine, use psql's \copy which runs COPY ... TO STDOUT under the hood.

  • You cannot specify a host and user name in the file name in the hope that the file will be transferred to another machine. It has to be a path on the file system (for COPY, it has to be an absolute path on the server's file system, hence the error message).

Start psql on the machine where you want the file to end up and use \copy:

\copy finance.coa to '/home/kedarisettyakhiles/akki.csv' (FORMAT 'csv', DELIMITER ',', HEADER)
| improve this answer | |
  • It's showing:- drwxr-xr-x 24 kedarisettyakhiles kedarisettyakhiles 4096 Aug 27 16:00 /home/kedarisettyakhiles/ – akhilesh kedarisetty Aug 27 '19 at 12:51
  • Looks ok. Not sure where you are going wrong. Use strace to debug this. – Laurenz Albe Aug 27 '19 at 13:16
  • Whats strace? How to use it? – akhilesh kedarisetty Aug 28 '19 at 4:36
  • This leads too far - ask your system administrator for help. She can probably help you figure out what went wrong. – Laurenz Albe Aug 28 '19 at 6:34
  • As, I came to know that /tmp location doesn't contain any privileges, I tried giving the path /tmp location. So the file was stored with complete data using copy. – akhilesh kedarisetty Nov 8 '19 at 9:42

Not the answer you're looking for? Browse other questions tagged or ask your own question.