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

Ayers

Are you using a postgres >= 8.1? – Dhobi

I think I'll make a plan to upgrad to the newer version, will make life so much easier – Ayers

See also https://mcmap.net/q/45638/-save-pl-pgsql-output-from-postgresql-to-a-csv-file/287948 – Aussie

Answer 1

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

as described in the manual.

Frightened

Be aware that the HEADER argument was not introduced until 8.1. – Dhobi

Which is, let's say, a bit rusty. – Frightened

Thx for the response running an ancient version 7 ;-( – Ayers

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. – Frightened

Unfortunately PostgreSQL 7.3.4 is the version I currently use ;-( – Ayers

Example in windows format COPY products_273 TO 'D:\\products_199.csv' DELIMITER ',' CSV HEADER; – Witch

Note COPY requires administrator privileges. Use \COPY instead if you run into issues. – Refraction

this can give you non-conformant output, better to use "FORMAT csv" than "DELIMITER ','". not sure what version that arrived in though – Dacha

For v9.5, the command is now COPY products_273 TO '/tmp/products_199.csv' WITH (FORMAT CSV, HEADER); – Sosna

@faraz I get this error: syntax error at or near "\" – Maressa

Weirdly, with pgcli, the only way it worked for me was using \copy (lowercase!) – Batting

I have a complex SELECT in an external file. Any suggestion for the COPY syntax ? – Rna

ERROR: cannot copy from materialized view. HINT: Try the COPY (SELECT ...) TO variant. – Aussie

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 – Uniparous

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 – Vanderhoek

if its failed dont forget to use \COPY instead just COPY – Weedy

Answer 2

From psql command line:

\COPY my_table TO 'filename' CSV HEADER

no semi-colon at the end.

Steger

this version is by far the best as the COPY command requires admin access – Multipurpose

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. – Ovolo

Much better, especially when saving to a directory where you have access but the postgres user doesn't. – Becerra

Do I have to first create the csv file or will it be automatically generated? – Clio

@ismail - the highlighted command above is meant to be run after entering the psql command line, not from PgAdmin for example – Greaser

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. – Aquiver

@IanGow I want to do same operation, copy from remote to localhost, how to do achieve that please? – Vanderhoek

@MatthewO'Riordan can you please show how to copy from remote to localhost? – Vanderhoek

@Vanderhoek Use pg_dump -h remote | pg_restore -h localhost. – Ovolo

@Vanderhoek psql -c "\COPY (SELECT * FROM mytable) TO STDOUT" > mytable.csv – Aquiver

Perfect, much better than using exporting from pgAdmin for large files. – Vanderhoek

Answer 3

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;

Gundry

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. – Stockroom

how the syntax change if I am copying from CSV to table for selected fields – Jefferson

Answer 4

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

Rapallo

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). – Aussie

I like this one because this works with sigularity. – Nazareth

Answer 5

This works

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

Morgue

Nice. Note that this doesn't seem to escape commas inside of fields that contain them. – Lorica

I like this, without the -F ,, and use | as the separator. Thanks! – Geosphere

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 – Epicureanism

DANGER it is not for CSV format, not works for arrays or text with "," .. not do proper CSV-quotation. Use @Brian's answer. – Aussie

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. – File

Answer 6

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

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

Logsdon

where Can i find that directory on my pc? – Andante

The results are saved under any path that comes after >. – Logsdon

I see no --csv option for my version: psql (PostgreSQL) 11.7 (Ubuntu 11.7-0ubuntu0.19.10.1) – File

I had trouble with the pager waiting for cmdline input to terminate. I had to add -P pager=off. – Rihana

Answer 7

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

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

Fenwick

Answer 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 ',');"

Japonica

Answer 9

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.

Pascia

Answer 10

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);

Ear

Answer 11

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

Using this u can export data also.

Conversant

Answer 12

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.

Thayne

Answer 13

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

Damnation

Answer 14

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.

Romanov