How to export table as CSV with headings on Postgresql?

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;

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

as described in the manual.

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

From psql command line:

\COPY my_table TO 'filename' CSV HEADER

no semi-colon at the end.

Related