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

The name is `PostgreSQL` or `Postgres` for short. There is no `Postgre`. — Erwin Brandstetter, Nov 14 '11 at 09:28
You only need to double-quote the filename if it includes whitespace or special characters. Not necessary for `'C:\\auto_new.txt'`. (Not wrong, though.). There is — Erwin Brandstetter, Nov 14 '11 at 12:27
There is also a space missing before `TO`. Not sure whether PostgreSQL 8.2 cares. It would still work in 9.0. — Erwin Brandstetter, Nov 14 '11 at 12:52

score 39 · Accepted Answer · edited May 23 '17 at 11:47

39

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:

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

edited May 23 '17 at 11:47

Community

1
1

answered Nov 14 '11 at 09:11

Erwin Brandstetter

479,275
111
893
1,042

1

COPY (SELECT * FROM tbl) TO 'C:/Documents and Settings/Tech/Desktop/myfile1.csv'; SQL error: ERROR: relative path not allowed for COPY to file – Ghostman Nov 14 '11 at 09:15
@soul: I suspect the whitespace in your filename. See my amended answer. – Erwin Brandstetter Nov 14 '11 at 09:37
@soul: ah .. Windows needs backslashes. See amended answer. (I use Debian.) – Erwin Brandstetter Nov 14 '11 at 10:27
will try pgadmin and let u know @ErwinBrandstetter – Ghostman Nov 14 '11 at 10:29
not able to copy in windows? what might be the issue with the query – Ghostman Nov 14 '11 at 12:05
@soul: Please amend your question with the exact SQL statement you use and the error message you get. – Erwin Brandstetter Nov 14 '11 at 12:07
Do you have write permission in the c: root directory ? – wildplasser Nov 14 '11 at 12:19
Yup i got the administrator privileges .. so i got the permission – Ghostman Nov 14 '11 at 12:21
@soul: With the switch to Windows and it's backslashes, we have triggered another effect: `\\` is a special character an needs to be escaped. (Just like I had to in this comment.) See my amended answer! – Erwin Brandstetter Nov 14 '11 at 12:30
SQL error: ERROR: relative path not allowed for COPY to file In statement: COPY (select distinct(m_ – Ghostman Nov 14 '11 at 12:42
@soul i am facing the same problem , got any fix ? :) – sanre6 Jan 04 '12 at 05:35
@sanre6 : nope just directly downloaded using the export button – Ghostman Jan 04 '12 at 06:12
3

@sanre6: Always remember that `COPY` handles files local to the *server*. If your client is on a different machine, use the meta-command `\copy` of the **psql** client or some other tool like **pgAdmin**. – Erwin Brandstetter Jan 04 '12 at 11:05
@ErwinBrandstetter , hey i did not know that . But , still doesn't work even with \copy using pgadmin version 8.4. same error as mentioned in the post :( – sanre6 Jan 05 '12 at 07:21
There is a horrible bug is Windows PostgreSQL the test for an absolute path the code just looks for a '/' see port.h #define is_absolute_path ( filename ) #define IS_DIR_SEP ( ch ) ((ch) == '/') Value: ( \ IS_DIR_SEP((filename)[0]) \ ) – Tim Child Mar 21 '13 at 00:23
How to use \copy command? to query the data and save in csv file? – AKIWEB Aug 05 '14 at 02:50
1

@AKIWEB: Follow my link above and read the manual. If something is still unclear, ask a *question*. Comments are not the place. – Erwin Brandstetter Aug 05 '14 at 03:30
@ErwinBrandstetter I got the same error as ghostman on Windows and I have no whitespace and use backslashes – Hack-R Sep 12 '16 at 01:38
@ErwinBrandstetter I can export a query result into CSV file using the COPY command , but one of the column data is a JSON data so when I open it in libreoffice it is split into different cells(since it had comma), any idea how to export the json data as is. Thanks in advance! – Arunraj Sep 14 '16 at 11:48

score 8 · Answer 2 · answered Aug 19 '15 at 17:09

8

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.

answered Aug 19 '15 at 17:09

Revol89

561
5
3

score 4 · Answer 3 · edited Jan 16 '13 at 16:30

4

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.

edited Jan 16 '13 at 16:30

J Max

2,245
2
23
42

answered Jan 16 '13 at 16:06

Celia

49
1

What version of PostgreSQL? – Tim Child Mar 21 '13 at 00:25

score 2 · Answer 4 · answered Sep 04 '14 at 08:20

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.

Or you can copy to /tmp. Worked for me. – dimuthu May 05 '15 at 06:02 — dimuthu, May 05 '15 at 06:02

score 2 · Answer 5 · answered Nov 14 '11 at 13:20

2

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

answered Nov 14 '11 at 13:20

a_horse_with_no_name

440,273
77
685
758

score 0 · Answer 6 · answered Sep 04 '14 at 08:17

0

The correct script for postgres (Ubuntu) is:

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

answered Sep 04 '14 at 08:17

Michael

299
6
10

psql -c "COPY (SELECT * FROM tbl) TO '/var/lib/postgres/myfile1.csv';" don't forget to add db, user and password if require. -c flag give you option to run command from ubuntu(linux) terminal. – Michael Oct 25 '16 at 16:09
I want to put quote around my string values. how can i? @Michael – AhmetMelihBaşbuğ Oct 26 '16 at 06:40
You can try ' ' or "\"" – Michael Oct 26 '16 at 07:58

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

6 Answers6

Linked