To export data from a table T (or any result of a query) in a PostgreSQL database use the following command:
$ psql -h localhost -U database_usr -d database_name -c "COPY (SELECT * FROM T) TO '/path/file.csv' DELIMITER ',' CSV HEADER;"
Notes:
- The query should be on one line; it is split here for readability.
- Make sure you have the correct permissions to write to the directory referenced. The command may need to be run with sudo.