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

Solution 1

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

as described in the manual.

Solution 2

From psql command line:

\COPY my_table TO 'filename' CSV HEADER

no semi-colon at the end.

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

Solution 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

Solution 5

This works

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

View more solutions