How to export table as CSV with headings on Postgresql?

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

as described in the manual.

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

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

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

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

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.

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

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.