516

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',';
Improve this question
3
  • Are you using a postgres >= 8.1? Commented Jul 13, 2009 at 16:09
  • 1
    I think I'll make a plan to upgrad to the newer version, will make life so much easier Commented Jul 15, 2009 at 6:13
  • 1
    See also stackoverflow.com/q/1517635/287948 Commented Mar 29, 2015 at 10:33

15 Answers 15

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

as described in the manual.

Improve this answer
Sign up to request clarification or add additional context in comments.

17 Comments

Be aware that the HEADER argument was not introduced until 8.1.
Which is, let's say, a bit rusty.
Note COPY requires administrator privileges. Use \COPY instead if you run into issues.
this can give you non-conformant output, better to use "FORMAT csv" than "DELIMITER ','". not sure what version that arrived in though
For v9.5, the command is now COPY products_273 TO '/tmp/products_199.csv' WITH (FORMAT CSV, HEADER);
|
276

From psql command line:

\COPY my_table TO 'filename' CSV HEADER

no semi-colon at the end.

Improve this answer

12 Comments

this version is by far the best as the COPY command requires admin access
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.
Much better, especially when saving to a directory where you have access but the postgres user doesn't.
@arilwan Use pg_dump -h remote | pg_restore -h localhost.
@arilwan psql -c "\COPY (SELECT * FROM mytable) TO STDOUT" > mytable.csv
|
152

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;
Improve this answer

2 Comments

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.
how the syntax change if I am copying from CSV to table for selected fields
147

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
Improve this answer

3 Comments

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).
I like this one because this works with sigularity.
This is exactly what I needed. I am very seldom in a position to remote into the database server to run an export.
36

This works 

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

5 Comments

Nice. Note that this doesn't seem to escape commas inside of fields that contain them.
I like this, without the -F ,, and use | as the separator. Thanks!
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
DANGER it is not for CSV format, not works for arrays or text with "," .. not do proper CSV-quotation. Use @Brian's answer.
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.
22

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

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

4 Comments

where Can i find that directory on my pc?
The results are saved under any path that comes after >.
I see no --csv option for my version: psql (PostgreSQL) 11.7 (Ubuntu 11.7-0ubuntu0.19.10.1)
I had trouble with the pager waiting for cmdline input to terminate. I had to add -P pager=off.
11

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 ',');"
Improve this answer

Comments

10

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

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

Comments

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.

Improve this answer

Comments

3

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);
Improve this answer

Comments

3

To save to a file on the server do this with any SQL client:

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

Warning: this requires you to be superuser or a member of the pg_write_server_files role.

See the full docs of this method here.

To save to a file on the client, do this with psql:

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

This method does not require any extra permissions.

See the full docs of this method here.

Improve this answer

Comments

0

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

Using this u can export data also.

Improve this answer

Comments

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.

Improve this answer

Comments

0

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

Improve this answer

Comments

-1

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.