[PostgreSQL] How to Output Tables to CSV

Tadashi Shigeoka ·  Mon, December 26, 2011

I looked up how to output table data to CSV in PostgreSQL, so here’s a memo.

$ psql dbname

dbname=> \\o tablenams.csv
dbname=> \\a
Output format is unaligned.
dbname=> \\pset fieldsep ','
Field separator is ','.
dbname=> select * from tablename;
dbname=> \\o

\\o to specify output destination
\\a to disable column alignment
\\pset fieldsep to specify separator
Tab-delimited is '\  '
Extract target data with select statement.
The final \\o returns the output destination.

If you want to do this from the command line, there’s the following method:

$ psql -c 'SELECT * FROM tablename;' -A -F,

-A disable column alignment
-F specify separator (-F, for comma, -F\   for tab)

Reference Information

Postgres でテーブルを csv 出力 (CSV Output of Tables in Postgres)

PostgreSQL のデータをCSV出力する - IT革命~ (Outputting PostgreSQL Data to CSV - IT Revolution~)

That’s all from the Gemba.