438

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',';
ivanleoncz
  • 5,662
  • 3
  • 49
  • 42
Elitmiar
  • 30,202
  • 72
  • 172
  • 224

13 Answers13

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

as described in the manual.

ANeves thinks SE is evil
  • 5,681
  • 3
  • 35
  • 60
Milen A. Radev
  • 54,001
  • 19
  • 99
  • 105
  • 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
  • 71
    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
  • 38
    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
234

From psql command line:

\COPY my_table TO 'filename' CSV HEADER

no semi-colon at the end.

Arturo Herrero
  • 11,642
  • 8
  • 38
  • 71
Laurent Debricon
  • 3,729
  • 2
  • 22
  • 24
  • 23
    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
129

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;
Dhruvil Thaker
  • 1,754
  • 1
  • 13
  • 23
  • 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
108

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
Brian
  • 1,176
  • 1
  • 7
  • 6
  • 8
    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
35

This works 

psql dbname -F , --no-align -c "SELECT * FROM TABLE"
Mark Hall
  • 51,582
  • 8
  • 87
  • 106
jordg
  • 399
  • 3
  • 2
  • 10
    Nice. Note that this doesn't seem to escape commas inside of fields that contain them. – RecursivelyIronic 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
  • 7
    **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
9

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

COPY products_273 TO '/tmp/products_199.csv' WITH (FORMAT CSV, HEADER);
maytham-ɯɐɥʇʎɐɯ
  • 21,551
  • 10
  • 85
  • 103
8

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 ',');"
Atihska
  • 3,271
  • 3
  • 39
  • 63
7

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

psql --csv -c "SELECT * FROM products_273" > '/tmp/products_199.csv'
Yan Foto
  • 8,951
  • 4
  • 45
  • 79
4

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.

Horse O'Houlihan
  • 1,469
  • 4
  • 9
  • 24
0

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

Using this u can export data also.

user3767321
  • 107
  • 2
  • 10
0

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.

Tim Biegeleisen
  • 387,723
  • 20
  • 200
  • 263
0

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

Mohit Singh
  • 15
  • 4
0

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);
A. Rick
  • 369
  • 2
  • 7