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

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:

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:


 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:

Leave a Comment