October 26, 2004

PostgreSQL | Generating comma-separated files with psql

In more recent PostgreSQL versions, the command \copy can be used to generate CSV files.

psql is an excellent command line tool, but lacks the advanced report formatting facilities of, for example, Oracle's SQL*Plus. However it can be used to output results in several different formats including CSV.

To generate a comma-separated file (CSV), set the following format options:

\pset tuples_only
\pset format unaligned
\pset fieldsep ,
\pset recordsep '\n'

The results of any statements now issued will be in CSV format. To output text fields correctly, ensure they are wrapped in quotes, e.g.:

SELECT '"' || text_field  || '"' AS text_field,
  FROM report_view

To return to the usual output format it's probably easier to start a new psql session.

Posted at 5:23 AM