Saturday, January 14, 2012

PostgreSQL - export to CSV or any other character delimited files

Just a small tip.

Exporting output to CSV format.

pgbenchdb=# \copy (select * from pgbench_history limit 30) TO 'test.csv' CSV

pgbenchdb=# \! cat test.csv
98,3,256655,-2623,2012-01-11 17:28:15.200909,
43,2,94378,2717,2012-01-11 17:28:15.200909,
46,1,414961,1716,2012-01-11 17:28:15.201082,
45,8,246477,3341,2012-01-11 17:28:15.200927,
84,7,743181,-1348,2012-01-11 17:28:15.201073,
20,1,713970,4518,2012-01-11 17:28:15.20119,
76,8,778718,-2588,2012-01-11 17:28:15.200988,
9,2,683187,-1675,2012-01-11 17:28:15.201074,
71,3,793922,-748,2012-01-11 17:28:15.261927,
93,6,484349,1816,2012-01-11 17:28:15.261776,
2,4,47256,2383,2012-01-11 17:28:15.261615,
64,2,567480,-4884,2012-01-11 17:28:15.201201,
75,8,716626,-293,2012-01-11 17:28:15.2013,
82,9,74376,-4112,2012-01-11 17:28:15.284261,
74,5,13936,-2220,2012-01-11 17:28:15.284299,
70,3,365642,-4313,2012-01-11 17:28:15.284375,
81,4,82626,2974,2012-01-11 17:28:15.285328,
27,2,379620,3392,2012-01-11 17:28:15.261669,
87,8,561117,-153,2012-01-11 17:28:15.284997,
85,9,953844,-4951,2012-01-11 17:28:15.296038,
38,5,447869,-3591,2012-01-11 17:28:15.307329,
73,4,130721,4496,2012-01-11 17:28:15.307251,
1,7,200428,-4388,2012-01-11 17:28:15.307193,
72,2,907918,-4323,2012-01-11 17:28:15.216612,
30,1,76931,4226,2012-01-11 17:28:15.318388,
27,7,87543,3849,2012-01-11 17:28:15.307277,
5,9,963441,-2585,2012-01-11 17:28:15.307221,
19,4,584590,-2414,2012-01-11 17:28:15.331634,
78,5,543756,476,2012-01-11 17:28:15.333069,
99,10,867181,-2597,2012-01-11 17:28:15.332944,

About \copy:


\copy invokes COPY FROM STDIN or COPY TO STDOUT, and then fetches/stores the data in a file accessible to the psql client. Thus, file accessibility and access rights depend on the client rather than the server when \copy is used.

Or using any other delimiter instead of comma, '|' for instance which I regard as being more reliable as commas are sometimes used inside text fields.

pgbenchdb=# COPY (select * from pgbench_history limit 30) TO '/var/lib/pgsql/test.csv' WITH DELIMITER '|';
COPY 30
-- with this variant of the copy command only absolute paths are allowed

the another variant:

pgbenchdb=# \copy (select * from pgbench_history limit 30) TO 'test.csv' WITH DELIMITER '|';
pgbenchdb=# \! cat test.csv
98|3|256655|-2623|2012-01-11 17:28:15.200909|\N
43|2|94378|2717|2012-01-11 17:28:15.200909|\N
46|1|414961|1716|2012-01-11 17:28:15.201082|\N
45|8|246477|3341|2012-01-11 17:28:15.200927|\N
84|7|743181|-1348|2012-01-11 17:28:15.201073|\N
20|1|713970|4518|2012-01-11 17:28:15.20119|\N
76|8|778718|-2588|2012-01-11 17:28:15.200988|\N
9|2|683187|-1675|2012-01-11 17:28:15.201074|\N
71|3|793922|-748|2012-01-11 17:28:15.261927|\N
93|6|484349|1816|2012-01-11 17:28:15.261776|\N
2|4|47256|2383|2012-01-11 17:28:15.261615|\N
64|2|567480|-4884|2012-01-11 17:28:15.201201|\N
75|8|716626|-293|2012-01-11 17:28:15.2013|\N
82|9|74376|-4112|2012-01-11 17:28:15.284261|\N
74|5|13936|-2220|2012-01-11 17:28:15.284299|\N
70|3|365642|-4313|2012-01-11 17:28:15.284375|\N
81|4|82626|2974|2012-01-11 17:28:15.285328|\N
27|2|379620|3392|2012-01-11 17:28:15.261669|\N
87|8|561117|-153|2012-01-11 17:28:15.284997|\N
85|9|953844|-4951|2012-01-11 17:28:15.296038|\N
38|5|447869|-3591|2012-01-11 17:28:15.307329|\N
73|4|130721|4496|2012-01-11 17:28:15.307251|\N
1|7|200428|-4388|2012-01-11 17:28:15.307193|\N
72|2|907918|-4323|2012-01-11 17:28:15.216612|\N
30|1|76931|4226|2012-01-11 17:28:15.318388|\N
27|7|87543|3849|2012-01-11 17:28:15.307277|\N
5|9|963441|-2585|2012-01-11 17:28:15.307221|\N
19|4|584590|-2414|2012-01-11 17:28:15.331634|\N
78|5|543756|476|2012-01-11 17:28:15.333069|\N
99|10|867181|-2597|2012-01-11 17:28:15.332944|\N


You could also fire something like this up:


In order to pass the output to the client, you could do something like this:
rene@linux-2561:~> psql -h 10.0.0.113 -U postgres -c "COPY (SELECT * FROM pgbench_history limit 10) TO STDOUT WITH CSV;" pgbenchdb > test.csv
rene@linux-2561:~> cat test.csv
98,3,256655,-2623,2012-01-11 17:28:15.200909,
43,2,94378,2717,2012-01-11 17:28:15.200909,
46,1,414961,1716,2012-01-11 17:28:15.201082,
45,8,246477,3341,2012-01-11 17:28:15.200927,
84,7,743181,-1348,2012-01-11 17:28:15.201073,
20,1,713970,4518,2012-01-11 17:28:15.20119,
76,8,778718,-2588,2012-01-11 17:28:15.200988,
9,2,683187,-1675,2012-01-11 17:28:15.201074,
71,3,793922,-748,2012-01-11 17:28:15.261927,
93,6,484349,1816,2012-01-11 17:28:15.261776,


Now I'm curious about my statement of some text having embedded commas:
rene@linux-2561:~> psql -h 10.0.0.113 -U postgres -c "COPY (SELECT  'this, is, a,nasty, field') TO STDOUT WITH CSV;" pgbenchdb > test.csv
rene@linux-2561:~> cat test.csv 
"this, is, a,nasty, field"

Nevermind, if it does detect embedded commas it double quotes the field. Nice!


The "\! COMMAND" psql's internal command is great, I just figured it out.

No comments:

Post a Comment