How to create CSV file from SQL?

Question

I am wondering how to create or export a CSV file from SQL? Is there any function for that similar to pgsql2shp? I would appreciate your ideas, tip or solutions.

Answer 1

You can save a complete table as a file using this command:

COPY tablename TO STDOUT CSV

Ref: https://www.postgresql.org/docs/current/static/sql-copy.html

Answer 2

You can give this a try. But i believe there may be some syntax changes depending on the version.

COPY (SELECT foo,bar FROM whatever) TO ‘/tmp/dump.csv’ WITH CSV HEADER