[postgresql] 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"';

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.

The correct script for postgres (Ubuntu) is:

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

In PostgreSQL 9.4 to create to file CSV with the header in Ubuntu:

COPY (SELECT * FROM tbl) TO '/home/user/Desktop/result_sql.csv' WITH CSV HEADER;

Note: The folder must be writable.

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.

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