How To Export A View To CSV In PostgreSQL

By Nicholas Brown.

Have you ever discovered something via a PostgreSQL query that you just have to show your boss, but don’t want to do it all over again in front of them?

Views are helpful in that regard, but you can also export a view to a CSV file, making it very easy to just throw a query into a spreadsheet and send it off to whoever you need to show it to. They won’t need to bring up the PostgreSQL shell, and they can even view it on their smartphone!

You can export a view to a CSV file using the COPY command (prefixed by a \ if you’re using the PSQL shell) by following these steps:

1. Create your view, it will be named cf22 in this case.
2. Use a ‘select’ query on the view in the following COPY command, which generates the desired CSV file, as shown below:

\COPY (SELECT * FROM cf22) TO '/home/nicholas/Desktop/22cfrefrigerators.csv' DELIMITER ',' CSV HEADER;

Tested On PostgreSQL versions:

• 10.1.