psql2csv

CSV Output from ‘psql’ – courtesy of Will R.

#!/bin/bash
FS="'|field_separator|'"
RS="'|record_separator|'"
HOST="localhost"
DB="core"
USER="core_write"
FILE=$1
psql -A -F$FS -R$RS -f$FILE -h$HOST -d$DB -U$USER \
| sed 's/"/""/g' \
| sed "s/$FS/\",\"/g" \
| sed "s/$RS/\"\n\"/g" \
| sed '1s/^\(.*\)$/"\1/' \
| grep -v "^\"([0123456789]\+ row[s]\?)$"

This script will properly escape commas, quotes, and newlines. It will also leave the headers, but remove the row counts. This is useful for generating true csv output from the command line for automated reports (without requiring python and psycopg).

Save the script (ie. psql2csv.sh), make it executable, place your query in a file (ie. query.sql), then run:

./psql2csv.sh query.sql > report.csv