Waiting for PostgreSQL 12 – Add CSV table output mode in psql.

On 26th of November 2018, Tom Lane committed patch:

Add CSV table output mode in psql.
 
"\pset format csv", or --csv, selects comma-separated values table format.
This is compliant with RFC 4180, except that we aren't too picky about
whether the record separator is LF or CRLF; also, the user may choose a
field separator other than comma.
 
This output format is directly compatible with the server's COPY CSV
format, and will also be useful as input to other programs.  It's
considerably safer for that purpose than the old recommendation to
use "unaligned" format, since the latter couldn't handle data
containing the field separator character.
 
Daniel Vérité, reviewed by Fabien Coelho and David Fetter, some
tweaking by me
 
Discussion: https://postgr.es/m/a8de371e-006f-4f92-ab72-2bbe3ee78f03@manitou-mail.org

This change is pretty simple, but it can definitely be useful.

As you perhaps know, one can select data in psql with many different output formattings:

  • aligned (this is the default)
  • asciidoc
  • html
  • latex
  • latex-longtable
  • troff-ms
  • unaligned
  • wrapped

Now, we got one more format, and a setting that is needed for it.

Let's see how it works, first the default:

=$ SELECT oid, datname FROM pg_database LIMIT 2;
  oid  |  datname  
-------+-----------
 10872 | postgres
     1 | template1
(2 ROWS)

and now test of the new formatting:

=$ \pset format csv
Output format IS csv.
 
=$ SELECT oid, datname FROM pg_database LIMIT 2;
oid,datname
10872,postgres
1,template1
 
=$ \pset csv_fieldsep ';'
FIELD separator FOR CSV IS ";".
 
=$ SELECT oid, datname FROM pg_database LIMIT 2;
oid;datname
10872;postgres
1;template1

What will happen if the value will contain new line characters?

=$ SELECT E'ab\ncd';
?COLUMN?
"ab
cd"

And what about quotes?

=$ SELECT E'ab\nc"d"e';
?COLUMN?
"ab
c""d""e"

Pretty cool. I can definitely see some uses for it. Thanks to all involved, as usual 🙂

2 thoughts on “Waiting for PostgreSQL 12 – Add CSV table output mode in psql.”

Comments are closed.