Use 'copy' command to copy the output to a CSV file. Here is an example to create tab delimited CSV file.
\copy (SELECT * FROM table_name) to 'path_to_file/filename.csv' WITH DELIMITER E'\t' CSV HEADER QUOTE E'\b';