How to export table as CSV with headings on Postgresql?

Question

I'm trying to export a PostgreSQL table with headings to a CSV file via command line, however I get it to export to CSV file, but without headings.

My code looks as follows:

COPY products_273 to '/tmp/products_199.csv' delimiters',';

I think I'll make a plan to upgrad to the newer version, will make life so much easier — Elitmiar, Jul 15 '09 at 06:13

score 679 · Accepted Answer · edited Nov 28 '18 at 14:20

679

COPY products_273 TO '/tmp/products_199.csv' WITH (FORMAT CSV, HEADER);

as described in the manual.

edited Nov 28 '18 at 14:20

ANeves

6,219
3
39
63

answered Jul 13 '09 at 16:04

Milen A. Radev

60,241
22
105
110

9

Be aware that the HEADER argument was not introduced until 8.1. – Dana the Sane Jul 13 '09 at 16:08
7

Which is, let's say, a bit rusty. – Milen A. Radev Jul 13 '09 at 16:13
Thx for the response running an ancient version 7 ;-( – Elitmiar Jul 14 '09 at 05:42
Although 7.4 is still supported (v7.4.25 is the latest right now) I would recommend an upgrade to 8.3 ASAP. I don't want to be in your shoes if you're still using something older than 7.4. – Milen A. Radev Jul 14 '09 at 09:18
Unfortunately PostgreSQL 7.3.4 is the version I currently use ;-( – Elitmiar Jul 14 '09 at 15:17
Example in windows format COPY products_273 TO 'D:\\products_199.csv' DELIMITER ',' CSV HEADER; – Kunal Jha Jan 03 '14 at 10:46
77

Note `COPY` requires administrator privileges. Use `\COPY` instead if you run into issues. – fny Mar 02 '14 at 20:56
5

this can give you non-conformant output, better to use "FORMAT csv" than "DELIMITER ','". not sure what version that arrived in though – grahamrhay Sep 23 '15 at 11:09
39

For v9.5, the command is now `COPY products_273 TO '/tmp/products_199.csv' WITH (FORMAT CSV, HEADER);` – Shubham Goyal Apr 20 '16 at 10:02
@faraz I get this error: syntax error at or near "\" – Ismail Sahin Sep 11 '16 at 12:44
Weirdly, with **pgcli**, the only way it worked for me was using `\copy` (lowercase!) – friederbluemle Oct 10 '16 at 11:29
I have a complex SELECT in an external file. Any suggestion for the COPY syntax ? – Massimo Oct 12 '16 at 14:27
**ERROR:** cannot copy from materialized view. HINT: Try the COPY (SELECT ...) TO variant. – Peter Krauss Mar 30 '17 at 16:29
I didn't realize that the path referred to a path on the *server*. If you want to copy data from a remote server, you could do `psql -c "COPY products_273 TO STDOUT WITH (FORMAT CSV, HEADER)" > /tmp/products_199.csv` – bonh May 04 '17 at 20:04
Hi all, I used this and database is copied using `COPY my_table TO '/tmp/environment.csv' WITH (FORMAT CSV, HEADER);` This shows `COPY 1123620` `postgres=#` But I'm not sure where the file is located. `$ls /tmp/` `yum_save_tx.2019-05-24.16-58.rz76Yg.yumtx` no such file – arilwan May 26 '19 at 14:18
if its failed dont forget to use `\COPY` instead just `COPY` – buncis Sep 04 '21 at 17:49

score 261 · Answer 2 · edited Jul 24 '18 at 15:41

261

From psql command line:

\COPY my_table TO 'filename' CSV HEADER

no semi-colon at the end.

edited Jul 24 '18 at 15:41

Arturo Herrero

12,772
11
42
73

answered Jun 19 '12 at 13:06

Laurent Debricon

4,307
2
24
26

25

this version is by far the best as the `COPY` command requires admin access – Matthew O'Riordan Nov 17 '12 at 12:51
2

Also with the `psql` approach, one can save the output anywhere one has access to. I just used the `psql` approach to get data from a remote server into a local file. Very slick. – Ian Gow Jul 17 '14 at 18:15
Much better, especially when saving to a directory where you have access but the postgres user doesn't. – Steve Bennett Apr 07 '15 at 07:28
Do I have to first create the csv file or will it be automatically generated? – Avi Dec 09 '16 at 05:41
@ismail - the highlighted command above is meant to be run after entering the `psql` command line, not from PgAdmin for example – Don Cheadle Apr 07 '17 at 19:56
The difference between \copy and copy is that the first one pipes the data through the client and the second one saves the data to the server. This means you can get the data from remote database to the local machine by using \COPY. – Juha Palomäki Jul 11 '18 at 11:38
@IanGow I want to do same operation, copy from remote to localhost, how to do achieve that please? – arilwan May 26 '19 at 14:26
@MatthewO'Riordan can you please show how to copy from remote to localhost? – arilwan May 26 '19 at 14:28
2

@arilwan Use `pg_dump -h remote | pg_restore -h localhost`. – Ian Gow May 26 '19 at 23:53
4

@arilwan `psql -c "\COPY (SELECT * FROM mytable) TO STDOUT" > mytable.csv` – Juha Palomäki Jun 06 '19 at 16:00
Perfect, much better than using exporting from `pgAdmin` for large files. – arilwan Jun 30 '20 at 09:30

Dhruvil Thaker · Answer 3 · 2015-11-02T16:39:15.110

139

instead of just table name, you can also write a query for getting only selected column data.

COPY (select id,name from tablename) TO 'filepath/aa.csv' DELIMITER ',' CSV HEADER;

with admin privilege

\COPY (select id,name from tablename) TO 'filepath/aa.csv' DELIMITER ',' CSV HEADER;

edited Nov 02 '15 at 16:39

answered Oct 09 '15 at 10:49

Dhruvil Thaker

1,950
1
15
25

I don't believe you need the terminating semicolon in the psql version of the command (`\COPY ...`). And at least in my version of psql (9.5.2) I didn't need to specify 'DELIMITER'; the default was a comma. – user1071847 Sep 06 '16 at 15:36
how the syntax change if I am copying from CSV to table for selected fields – user269867 Jul 10 '17 at 21:25

score 133 · Answer 4 · answered Dec 01 '16 at 17:32

133

When I don't have permission to write a file out from Postgres I find that I can run the query from the command line.

psql -U user -d db_name -c "Copy (Select * From foo_table LIMIT 10) To STDOUT With CSV HEADER DELIMITER ',';" > foo_data.csv

answered Dec 01 '16 at 17:32

Brian

1,408
1
9
6

10

The best for "any environment". Best for **1.** Not need special permissions at Postgresql or at client; **2.** can use relative path; and **3.** is secure for real CSV format (secure quotations). – Peter Krauss Mar 30 '17 at 16:46
I like this one because this works with sigularity. – Vasantha Ganesh Sep 09 '20 at 14:31

score 36 · Answer 5 · edited Jan 13 '12 at 06:17

36

This works

psql dbname -F , --no-align -c "SELECT * FROM TABLE"

edited Jan 13 '12 at 06:17

Mark Hall

53,938
9
94
111

answered Jan 13 '12 at 06:03

jordg

425
4
2

12

Nice. Note that this doesn't seem to escape commas inside of fields that contain them. – Lucas Wiman Dec 04 '14 at 22:35
I like this, without the `-F ,`, and use `|` as the separator. Thanks! – dsummersl Nov 17 '16 at 19:41
2

This is not what is commonly considered as an Export feature, just controlled data display. The difference is light, but important: this is more intended to be read by a human than the `COPY` statement which creates a file to be reused – Romain G Jan 27 '17 at 10:06
11

**DANGER** it is not for CSV format, not works for arrays or text with "," .. not do proper CSV-quotation. Use @Brian's answer. – Peter Krauss Mar 30 '17 at 16:43
I like this form because it works if you are redirecting input from a commands file. I use a tab delimiter instead so that e.g., `gnumeric out.tsv` works. – hoffmanc Nov 11 '20 at 18:48

score 16 · Answer 6 · answered Oct 24 '19 at 15:38

16

The simplest way (using psql) seems to be by using --csv flag:

psql --csv -c "SELECT * FROM products_273" > '/tmp/products_199.csv'

answered Oct 24 '19 at 15:38

Yan Foto

10,850
6
57
88

where Can i find that directory on my pc? – Oct 15 '20 at 06:03
The results are saved under any path that comes after `>`. – Yan Foto Oct 15 '20 at 12:10
3

I see no `--csv` option for my version: `psql (PostgreSQL) 11.7 (Ubuntu 11.7-0ubuntu0.19.10.1)` – hoffmanc Nov 11 '20 at 18:50
I had trouble with the pager waiting for cmdline input to terminate. I had to add `-P pager=off`. – thayne Nov 30 '21 at 17:12

Maytham Fahmi · Answer 7 · 2017-08-13T00:26:04.043

10

For version 9.5 I use, it would be like this:

COPY products_273 TO '/tmp/products_199.csv' WITH (FORMAT CSV, HEADER);

edited Aug 13 '17 at 00:26

answered Apr 09 '17 at 19:06

Maytham Fahmi

31,138
14
118
137

score 10 · Answer 8 · answered Jun 04 '18 at 15:43

10

This solution worked for me using \copy.

psql -h <host> -U <user> -d <dbname> -c "\copy <table_name> FROM '<path to csvfile/file.csv>' with (format csv,header true, delimiter ',');"

answered Jun 04 '18 at 15:43

Atihska

4,803
10
56
98

score 4 · Answer 9 · answered Nov 26 '16 at 19:31

Heres how I got it working power shell using pgsl connnect to a Heroku PG database:

I had to first change the client encoding to utf8 like this: \encoding UTF8

Then dumped the data to a CSV file this:

\copy (SELECT * FROM my_table) TO  C://wamp64/www/spider/chebi2/dump.csv CSV DELIMITER '~'

I used ~ as the delimiter because I don't like CSV files, I usually use TSV files, but it won't let me add '\t' as the delimiter, so I used ~ because its a rarely used characeter.

score 2 · Answer 10 · answered Dec 17 '20 at 18:16

The COPY command isn't what is restricted. What is restricted is directing the output from the TO to anywhere except to STDOUT. However, there is no restriction on specifying the output file via the \o command.

\o '/tmp/products_199.csv';
COPY products_273 TO STDOUT WITH (FORMAT CSV, HEADER);

score 0 · Answer 11 · answered May 10 '16 at 06:44

0

copy (anysql query datawanttoexport) to 'fileablsoutepathwihname' delimiter ',' csv header;

Using this u can export data also.

answered May 10 '16 at 06:44

Devendra Mishra

77
3
10

score 0 · Answer 12 · answered Jun 27 '19 at 10:27

I am posting this answer because none of the other answers given here actually worked for me. I could not use COPY from within Postgres, because I did not have the correct permissions. So I chose "Export grid rows" and saved the output as UTF-8.

The psql version given by @Brian also did not work for me, for a different reason. The reason it did not work is that apparently the Windows command prompt (I was using Windows) was meddling around with the encoding on its own. I kept getting this error:

ERROR: character with byte sequence 0x81 in encoding "WIN1252" has no equivalent in encoding "UTF8"

The solution I ended up using was to write a short JDBC script (Java) which read the CSV file and issued insert statements directly into my Postgres table. This worked, but the command prompt also would have worked had it not been altering the encoding.

Mohit Singh · Answer 13 · 2019-12-30T08:18:36.733

0

Try this: "COPY products_273 FROM '\tmp\products_199.csv' DELIMITER ',' CSV HEADER"

edited Dec 30 '19 at 08:18

answered Dec 30 '19 at 07:31

Mohit Singh

33
4

score 0 · Answer 14 · answered Feb 03 '22 at 15:15

In pgAdmin, highlight your query statement just like when you use F5 to execute and press F9 - this will open the file browser so you can pick where you save your CSV.

If you are using Azure Data Studio, the instruction are here: Azure Data Studio: Save As CSV.

I know this isn't a universal solution, but most of the time you just want to grab the file by hand.

How to export table as CSV with headings on Postgresql?

14 Answers14

Linked

Related