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"';

Answers


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:


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.


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.


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


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.


The correct script for postgres (Ubuntu) is:

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

Need Your Help

Good Form Security - no CAPTCHA

php security forms

Is there a good method of form security that does not involve CAPTCHA? CAPTCHA is so annoying, but I need security because I am getting form spam. My form is PHP.

Generating routes in javascript with Twig and Symfony2

javascript symfony routes twig

Quite odd problem, sorry for asking, i'm quite new to Symfony/Twig. My route requires a mandatory region_id paramenter: