# In the postgres console or pgAdmin
#-h: host
#-p: port
#-U: database user
#-d: database name
psql -h 127.0.0.1 -p 5432 -U database_user -d database_name
\COPY (SELECT * FROM mytable) TO E'/home/a/data/export.csv' CSV DELIMITER ',';
One Liner from the command line
# CSV
psql -h 127.0.0.1 -p 5432 -U database_user -d database_name -c "COPY (SELECT * FROM mytable) TO E'/home/a/data/export.csv' CSV DELIMITER ',';"
# Header
psql -h 127.0.0.1 -p 5432 -U database_user -d database_name -c "COPY (select column_a,column_b from mytable limit 10) TO STDOUT WITH CSV HEADER;" > home/a/data/export.csv
References
1. https://coderwall.com/p/jwtxjg