Ian P. Christian's Personal Blog Random witterings from pookey

Outputting from Postgres to CSV

I can never remember how to output to a CSV file from postgres, and end up having to google it time and time again - so I'm making a note of it here mostly for my own use

\f ','
\a
\t
\o /tmp/moocow.csv
SELECT foo,bar FROM whatever;
\o
\q

If a field has newlines, this will break. You can do something like this instead.....

 SELECT foo, bar, '"' || REPLACE(REPLACE(field_with_newilne, '\n', '\\n'), '"', '""') || '"' FROM whatever;