current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 4 down vote favorite

I need to export the resulting data from a query in PostgreSQL to Excel/CSV.
I use PostgreSQL 8.2.11.

SQL error:

ERROR:  relative path not allowed for COPY to file
In statement:

COPY (select distinct(m_price) from m_product)TO '"c:\auto_new.txt"';
share|improve this question
1  
The name is PostgreSQL or Postgres for short. There is no Postgre. –  Erwin Brandstetter Nov 14 '11 at 9:28
    
You only need to double-quote the filename if it includes whitespace or special characters. Not necessary for 'C:\\auto_new.txt'. (Not wrong, though.). There is –  Erwin Brandstetter Nov 14 '11 at 12:27
    
i tried giving >TO 'C:\\auto_new.txt' but the same error –  soul Nov 14 '11 at 12:28
    
There is also a space missing before TO. Not sure whether PostgreSQL 8.2 cares. It would still work in 9.0. –  Erwin Brandstetter Nov 14 '11 at 12:52

6 Answers 6

up vote 12 down vote accepted

Example with Unix-style file name:

COPY (SELECT * FROM tbl) TO '/var/lib/postgres/myfile1.csv' format csv;

Read the manual about COPY.
You have to use an absolute path for the target file. Be sure to double quote file names with whitespace. Example for MS Windows:

COPY (SELECT * FROM tbl)
TO E'"C:\\Documents and Settings\\Tech\Desktop\\myfile1.csv"' format csv;

In PostgreSQL 8.2, with standard_conforming_strings = off per default, you need to double backslashes, because \ is a special character and interpreted by PostgreSQL. It's all in the fine manual:

filename

The absolute path name of the input or output file. Windows users might need to use an E'' string and double backslashes used as path separators.

Or you can also use forward slashes for filenames under Windows.

An alternative is to use the meta-command \copy of the default terminal client psql. Read the manual here.

You can also use a GUI like pgadmin and copy / paste from the result grid to Excel for small queries.

I have written a closely related answer here:
Copy results from a PostgreSQL view in one DB to a table in another

For MySQL, there is a similar solution here:
Exporting MYSQL data into Excel/CSV via php

share|improve this answer
    
COPY (SELECT * FROM tbl) TO 'C:/Documents and Settings/Tech/Desktop/myfile1.csv'; SQL error: ERROR: relative path not allowed for COPY to file –  soul Nov 14 '11 at 9:15
    
@soul: I suspect the whitespace in your filename. See my amended answer. –  Erwin Brandstetter Nov 14 '11 at 9:37
    
i get the error still??? –  soul Nov 14 '11 at 10:04
    
@soul: ah .. Windows needs backslashes. See amended answer. (I use Debian.) –  Erwin Brandstetter Nov 14 '11 at 10:27
1  
@sanre6: Always remember that COPY handles files local to the server. If your client is on a different machine, use the meta-command \copy of the psql client or some other tool like pgAdmin. –  Erwin Brandstetter Jan 4 '12 at 11:05
up vote 0 down vote

Several GUI tools like Squirrel, SQL Workbench/J, AnySQL, ExecuteQuery can export to Excel files.

Most of those tools are listed in the PostgreSQL wiki:

http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools

share|improve this answer
up vote 0 down vote

This worked for me: 

COPY (SELECT * FROM table) 
    TO E'C:\\Program Files (x86)\\PostgreSQL\\8.4\\data\\try.csv';

In my case the problem was with the writing permission to a special folder (though I work as administrator), after changing the path to the original data folder under PostgreSQL I had success.

share|improve this answer
    
What version of PostgreSQL? –  Tim Child Mar 21 '13 at 0:25
    
Wonderful. It worked for me in linux. –  phpGeek Jun 16 at 6:17
up vote 0 down vote

You specify path to local file (Windows), but postgres trying to create file on remote pc (unix) where postgres has installed.

share|improve this answer
up vote 0 down vote

The correct script for postgres (Ubuntu) is:

COPY (SELECT * FROM tbl) TO '/var/lib/postgres/myfile1.csv';
share|improve this answer
up vote 0 down vote

If you have error like "ERROR: could not open server file "/file": Permission denied" you can fix it that:

Ran through the same problem, and this is the solution I found: Create a new folder (for instance, tmp) under /home $ cd /home make postgres the owner of that folder $ chown -R postgres:postgres tmp copy in tmp the files you want to write into the database, and make sure they also are owned by postgres. That's it. You should be in business after that.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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