6May/087
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;
June 5th, 2008 - 10:57
After much pain with line endings:
COPY (SELECT foo,bar FROM whatever) TO ‘/tmp/dump.csv’ WITH CSV HEADER
February 27th, 2009 - 22:42
I also can never remember how to do this, and I always google it, and end up here. So, thank you for keeping this handy!!!
December 8th, 2009 - 17:13
Saves so much effort looking for a simple example!
December 15th, 2009 - 22:05
I run a lot of queries from the bash command line or from scripts, so here’s a tip:
If you run into this error: “ERROR: must be superuser to COPY to or from a file,” try this:
echo “COPY (SELECT foo from BAR) TO STDOUT with CSV HEADER” | psql -o filename.csv database_name
March 6th, 2010 - 12:03
@Ben,
Your solution works well in version 8.4.2 and it shows a syntax error in version 8.1
copy to file command in postgresql 8.1 does not accept a query . it can copy whole table but not a query
do u have any solution for 8.1?
April 7th, 2010 - 23:54
try creating a view and using it instead of the query. hope this helps.
May 24th, 2010 - 20:22
Your post was useful
Could you tell me how I can include the data in double quotes ?
\o /tmp/moocow.csv
SELECT foo,bar FROM whatever;
\o
and the output like
“foo”,”bar”
“fdata”,”bdata”
“fdata1″,”bdata2″
Appreciate your help !