Simple CSV dumps from your Postgres database

Recently, I’ve spent a lot of time doing data shuffling.  Some of it for me and my own ( mostly ) malevolent purposes, but mostly, the kind of stuff that data analysts want to slice and dice.  I typically prefer to push analysts to the web based reporting setup I’ve built them, but a lot of times they are looking for some new angle that requires an aggregate we’re not already computing or a slightly different take on a report that already exists.  I usually end up building a web-based BIRT report that lets them self-service the CSV output for the data’s inevitable import into M$ office products.  There are times however, when it’s just simpler to build a quick CSV and email it over.  It makes them feel happy and it makes me feel happy.  The question is, what’s the best way to do this straight out of Postgres?

PostgreSQL provides a number of ways to output CSV files:

Copy-Paste quick-n’ dirty

This is, frankly, my favorite trick, I don’t recommend it if you have a large number of rows, but if you have something that fits in say, 80 terminal lines or less, this is perfect

Login to the database with psql and look at your table:

$ psql monitoring
psql (8.4.14)
Type "help" for help.

monitoring=# select * from snapshots;
 id |            dttm            | snapcomplete
----+----------------------------+--------------
  1 | 2013-01-17 17:49:13.04061  |
  2 | 2013-01-17 17:50:15.99624  |
  3 | 2013-01-17 17:50:44.349106 |
(3 rows)

monitoring=#

Now, its only got three rows, but if I wanted to dump that to a spreadsheet, I’d have to copy-paste and then do search and replace a bunch.  To make this simpler, I can use some psql special features:  \a ( aligned output mode) and \f ( field separator )

monitoring=# \a
Output format is unaligned.
monitoring=# \f ,
Field separator is ",".
monitoring=# select * from snapshots;
id,dttm,snapcomplete
1,2013-01-17 17:49:13.04061,
2,2013-01-17 17:50:15.99624,
3,2013-01-17 17:50:44.349106,
(3 rows)
monitoring=#

Quick-n’ dirty client-side CSV file

Sometimes, a copy-paste isn’t a good idea ( too many rows ), you can use \o <filename> to dump this to a file on your client.

monitoring=# \a
Output format is unaligned.
monitoring=# \f ,
Field separator is ",".
monitoring=# \o snapshots_20130729.csv
monitoring=# select * from snapshots;
monitoring=# \q
scottmacprort:oscgfiles scottm$ cat snapshots_20130729.csv
id,dttm,snapcomplete
1,2013-01-17 17:49:13.04061,
2,2013-01-17 17:50:15.99624,
3,2013-01-17 17:50:44.349106,
(3 rows)
scottmacprort:oscgfiles scottm$