Below is the command to export the result set of a select statement to a csv file with pipe delimiter and a header.
\copy (SELECT * from tablename) to 'C:\tmp\report1.csv' with CSV DELIMITER '|' HEADER
A file named report1.csv will be created on your local machine in the C:\tmp folder.
You can use any delimiter for the output. I have used ‘|’ in my above example.