Output as CSV

Print a query result as CSV with psql --csv, available since PostgreSQL 12. Values with commas are quoted and a NULL is written as an empty field.

1 min read

Summarize in AI

A query result has to go into a spreadsheet or another tool, and the default aligned table is not that format.

Shell

psql --csv -c "SELECT 1 AS id, 'a,b' AS name, NULL AS note, 'say \"hi\"' AS quote"
echo ---
psql --csv -P null='NULL' -c "SELECT 1 AS id, NULL AS note"

Text

id,name,note,quote
1,"a,b",,"say ""hi"""
---
id,note
1,NULL

--csv switches the output format to comma separated values with a header row, following the quoting rules of RFC 4180: a field with a comma or a quote is wrapped in double quotes, and a quote inside is doubled. The option is described in the psql reference. A NULL is an empty field, which cannot be told from an empty string, so -P null=... writes a visible marker.

The option needs psql 12 or later. The field separator is changed with -P csv_fieldsep=';', and a COPY ... TO STDOUT WITH CSV statement is the choice for large exports, as it streams from the server without the formatting layer. Spreadsheet programs often need a byte order mark for UTF-8.

Taggedcsv databases postgresql

More snippets

Databases

Dump one table

Dump one table with pg_dump -t name. The option takes a pattern, so -t orders does not match orders_archive but -t 'orders*' does, and quoting rules apply.

2 min read

Databases

Run SQL from a file

Run SQL from a file with psql -f. An error in the middle does not stop the file and the exit status stays 0, unless ON_ERROR_STOP is set to 1.

2 min read

Databases

Describe a table

Show a table's columns, indexes and foreign keys with the psql meta-command backslash d, and add a plus sign for storage and comments.

2 min read

More snippets

Dump one table

Run SQL from a file

Describe a table

Get new posts by email