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$
  1. I prefer to do it this way, to ease scripting:

    psql -c “COPY ( SELECT * FROM snapshots ) TO stdout WITH (FORMAT csv, HEADER true, DELIMITER ‘,’)”

    http://www.postgresql.org/docs/current/static/sql-copy.html

    The important bit is “TO stdout”. If you want the output to live on the database server, you can name a file, but I have never encountered a need for that. psql also has a \copy mode which is functionally identical to COPY TO ‘file’, but dumps to a named file locally. psql probably uses COPY TO stdout internally, and redirects to the file. Saves a few steps.

    Hope that helps!

  2. JoeJoe07-29-2013

    It\’s much easier to use \copy, e.g.,\copy snapshots to \’filename.data` csv

  3. RossRoss07-30-2013

    The general strategy of making the output look how you want it, then dumping to file is useful in some cases. I like to use “\g filename” (in place of the ; to terminate the statement) because it redirects just the one query output.

  4. DineshDinesh07-31-2013

    Copy requires superuser privileges, so the example you gave is helpful if you do not have superuser privs.

  5. order_viagraorder_viagra04-06-2014

    Hello!

  6. genericgeneric04-11-2014

    Hello!

  7. cialiscialis04-13-2014

    Hello!

  8. viagraviagra04-18-2014

    Hello!

  1. Simple CSV dumps from your Postgres database - ...08-04-13
  2. ubergamez.com09-18-13
  3. Springfield Missouri Private Investigator09-18-13
  4. www.ecotechnologia.net09-19-13
  5. valvesoftware09-20-13
  6. discount vouchers09-21-13
  7. Breakfree herpes relief reviews09-21-13
  8. how do I get rid of bed bugs09-21-13
  9. paula garces sexy09-21-13
  10. smokeless cigarettes free trial09-21-13
  11. safari4us09-22-13
  12. diet pills09-22-13
  13. Singapore Pools Winning Secrets09-23-13
  14. Search Rex09-23-13
  15. Charleston SC09-24-13
  16. Electrician Durham NC09-24-13
  17. home page09-24-13
  18. click here09-24-13
  19. investing news09-25-13
  20. payday loans09-25-13
  21. Discover More09-25-13
  22. go now09-25-13
  23. Carpet Pet Urine Removal09-25-13
  24. make money from home09-26-13
  25. Locksmiths09-26-13
  26. Chris09-26-13
  27. garcinia cambogia, about garcinia cambogia, all natural garcinia cambogia, all natural garcinia cambogia extract, all natural weight loss, all natural weight loss supplement, amazon garcinia cambogia extract, amazon garcinia cambogia extract pure, appetit09-26-13
  28. offerte lavoro da casa milano09-26-13
  29. tree removal service atlanta09-26-13
  30. marietta movers - more info09-26-13
  31. more info09-27-13
  32. floor & decor kennesaw ga09-27-13
  33. raspberry ketones reviews09-27-13
  34. los angeles cardiologists09-27-13
  35. green coffee bean extract09-27-13
  36. casino online aams con bonus senza deposito09-27-13
  37. los angeles cardiology associates09-27-13
  38. Homes Rent Bethlehem PA09-27-13
  39. hcg reviews09-28-13
  40. free smokeless cigarettes starter kit09-28-13
  41. kasino penzing09-28-13
  42. e cig free trial09-28-13
  43. pure garcinia cambogia09-28-13
  44. garcinia cambogia extract09-28-13
  45. water ionizer scam09-29-13
  46. garcinia cambogia side effects09-29-13
  47. hemoroizi interni09-29-13
  48. water ionizer09-30-13
  49. tabela fipe carros jornal valor economico09-30-13
  50. Blog lesen09-30-13
  51. http://saffronextracts.info/09-30-13
  52. jennifer lawrence sexiest09-30-13
  53. tumblr09-30-13
  54. Breaking Bad10-02-13
  55. chicago double decker bus tour10-02-13
  56. busscroll on acrylic10-03-13
  57. http://ZaufanaStrona.org10-05-13
  58. her comment is here10-06-13
  59. check these guys out10-07-13
  60. this contact form10-07-13
  61. click this link here now10-07-13
  62. facebook video advertising service10-08-13
  63. blu cigs review10-08-13
  64. video slot gratis 3D da bar10-08-13
  65. meratol reviews10-09-13
  66. rencontre gratuit10-10-13
  67. how to make android apps10-12-13
  68. e cig coupon10-14-13
  69. biznesso10-15-13

Leave a Reply

Please type the characters of this captcha image in the input box

Please write the answer to the math question of this captcha image in the input box

/* ]]> */