file io - PostgreSQL: export resulting data from SQL query to Excel/CSV

Question

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 1

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\TechDesktop\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 SettingsTechDesktopmyfile1.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:

• Copy results from a PostgreSQL view in one DB to a table in another

Similar solution for MySQL:

• Exporting MYSQL data into Excel/CSV via php