Written by 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
–
– 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.
