PostgreSQL: export resulting data from SQL query to Excel/CSV
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"';
Answer
Example with Unix-style file name:
COPY (SELECT * FROM tbl) TO '/var/lib/postgres/myfile1.csv' format csv;
Read the manual about COPY (link to version 8.2).
You have to use an absolute path for the target file. Be sure to double quote file names with spaces. 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. Works in any version. 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 the modern syntax with standard_conforming_strings = on (default since Postgres 9.1):
COPY tbl -- short for (SELECT * FROM tbl)
TO '"C:\Documents and Settings\Tech\Desktop\myfile1.csv"' (format csv);
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.
You can also use a GUI like pgadmin and copy / paste from the result grid to Excel for small queries.
Closely related answer:
Similar solution for MySQL: