Export the content of a table to a CSV file
COPY table_name TO '/tmp/table.csv' DELIMITER ',' CSV HEADER;
Export the result of a query to a CSV file
COPY (SELECT field1, field2, field3 FROM table_name)
TO '/tmp/result.csv' DELIMITER ',' CSV HEADER;
The above commands save the CSV file on the server’s hard drive. If you don’t have read/write privileges on the server, you can do the export locally:
Export the result of a query to a CSV file on your local hard drive
# connect to the database
psql -p PORT -h HOSTNAME -U USER DATABASE
psql=# \copy (SELECT field1, field2, field3 FROM table_name)
to '/tmp/result.csv' with DELIMITER ',' CSV HEADER;