Today at work I was asked to generate a report from some data we’ve been recording for the past month. The query I came up with ended up producing ~3000 rows. Far to many to copy and paste into a text file and email to anyone. I knew there had to be a way to produce a CSV from PSQL, but how? Googling returned a number of informative links, but I was still a bit confused.

I ended up asking my fellow devs, and received a few possible solutions.

The first was to start PSQL with the the following flags:

psql -A -F , *connection specifics*

The second was to enter PSQL like normal and then apply these two commands:

\a
\f ,

Both suggestions 1 and 2 require you to also run the following command before running your query:

\o filename.csv

The third, and by far most elegant solution came from one of our DBAs. He recommend entering PSQL like normal and then using the following command all in one fell swoop:

\copy (select statement) to '/my/file/name' with delimiter AS ',' NULL AS ''

It is worth noting that the previous command will only work in Postgres >= 8.2

I’m posting this here in hopes others searching for this will find this a clear succinct description, but also, for my own reference as I’m sure I’ll be asked to perform something similar again in the future.