PostgreSQL 12: csv output format for psql

Getting data out of PostgreSQL in csv format is not a big issue. Using copy you can do that easily. PostgreSQL 12 will enhance psql so that you can directly return csv formatted output from a query.

Using “copy” you would do something like this:

postgres=# copy pg_database to '/var/tmp/aa.txt' with (format csv);
COPY 3
postgres=# ! cat /var/tmp/aa.txt
11788,postgres,10,6,en_US.utf8,en_US.utf8,f,t,-1,11787,598,1,1663,
1,template1,10,6,en_US.utf8,en_US.utf8,t,t,-1,11787,598,1,1663,"{=c/postgres,postgres=CTc/postgres}"
11787,template0,10,6,en_US.utf8,en_US.utf8,t,f,-1,11787,598,1,1663,"{=c/postgres,postgres=CTc/postgres}"
postgres=# 

What you can not do directly with copy is dumping a view:

postgres=# copy pg_settings to '/var/tmp/aa.txt' with (format csv);
ERROR:  cannot copy from view "pg_settings"
HINT:  Try the COPY (SELECT ...) TO variant.

Of course you can workaround that by doing it like this:

postgres=# copy (select * from pg_settings) to '/var/tmp/aa.txt' with (format csv);
COPY 309
postgres=# ! head -2 /var/tmp/aa.txt
allow_system_table_mods,off,,Developer Options,Allows modifications of the structure of system tables.,,postmaster,bool,default,,,,off,off,,,f
application_name,psql,,Reporting and Logging / What to Log,Sets the application name to be reported in statistics and logs.,,user,string,client,,,,"",psql,,,f

With PostgreSQL 12 there will be an easier way of doing that:

postgres=# pset format csv
Output format is csv.
postgres=# select * from pg_database;
oid,datname,datdba,encoding,datcollate,datctype,datistemplate,datallowconn,datconnlimit,datlastsysoid,datfrozenxid,datminmxid,dattablespace,datacl
11788,postgres,10,6,en_US.utf8,en_US.utf8,f,t,-1,11787,598,1,1663,
1,template1,10,6,en_US.utf8,en_US.utf8,t,t,-1,11787,598,1,1663,"{=c/postgres,postgres=CTc/postgres}"
11787,template0,10,6,en_US.utf8,en_US.utf8,t,f,-1,11787,598,1,1663,"{=c/postgres,postgres=CTc/postgres}"

… or directly when executing psql:

postgres@pgbox:/home/postgres/ [PGDEV] psql -c "select * from pg_database" --csv  postgres
oid,datname,datdba,encoding,datcollate,datctype,datistemplate,datallowconn,datconnlimit,datlastsysoid,datfrozenxid,datminmxid,dattablespace,datacl
11788,postgres,10,6,en_US.utf8,en_US.utf8,f,t,-1,11787,598,1,1663,NULL
1,template1,10,6,en_US.utf8,en_US.utf8,t,t,-1,11787,598,1,1663,"{=c/postgres,postgres=CTc/postgres}"
11787,template0,10,6,en_US.utf8,en_US.utf8,t,f,-1,11787,598,1,1663,"{=c/postgres,postgres=CTc/postgres}"
Time: 0.794 ms

This makes is very easy to pass the data to other programs for further processing. You even specify the field separator:

postgres@pgbox:/home/postgres/ [PGDEV] psql -c "select * from pg_database" --csv -P csv_fieldsep='#'  postgres
oid#datname#datdba#encoding#datcollate#datctype#datistemplate#datallowconn#datconnlimit#datlastsysoid#datfrozenxid#datminmxid#dattablespace#datacl
11788#postgres#10#6#en_US.utf8#en_US.utf8#f#t#-1#11787#598#1#1663#NULL
1#template1#10#6#en_US.utf8#en_US.utf8#t#t#-1#11787#598#1#1663#{=c/postgres,postgres=CTc/postgres}
11787#template0#10#6#en_US.utf8#en_US.utf8#t#f#-1#11787#598#1#1663#{=c/postgres,postgres=CTc/postgres}
(3 rows)
Time: 2.105 ms