$ less ~/blog/dumping-csv-data-with-postgresql.txt
-rw-r--r-- cesar Jun 24 2008 dumping-csv-data-with-postgresql.txt
ARCHIVED · written in 2008. Kept as it was; some of it may be out of date.
Dumping CSV data with PostgreSQL
Many times you will need to quickly executa a query in PostgreSQL and dump
the results to comma delimited value (CSV) file. In this post I’ll show you
a quick way to do it with the psql command in PostgreSQL.
Just copy the following to a file called dump2csv.sql
-- ==================================================================
--
-- This script will execute and SQL query using the psql
-- command and dump the results to a file called /tmp/result.csv
--
-- ==================================================================
--
-- Uses the following psql options:
-- \a toggle between unaligned and aligned output mode
-- \t show only rows
-- \o [FILE] send query results to file or |pipe
--
\a
\t
\pset fieldsep ,
\o /tmp/result.csv
-- query
select ..
To dump the result of the query to a file do:
$ psql -h -U -d -W -f dump2csv.sql
the result of your query will be stored in /tmp/result.csv.