{"id":159,"date":"2007-10-13T16:53:20","date_gmt":"2007-10-13T23:53:20","guid":{"rendered":"http:\/\/www.chesnok.com\/daily\/2007\/10\/13\/psql-and-file-csv-exports\/"},"modified":"2012-03-26T02:57:11","modified_gmt":"2012-03-26T10:57:11","slug":"psql-and-file-csv-exports","status":"publish","type":"post","link":"https:\/\/www.chesnok.com\/daily\/2007\/10\/13\/psql-and-file-csv-exports\/","title":{"rendered":"psql and file, CSV exports"},"content":{"rendered":"<p>Gabrielle and I met to talk about some projects today. She brought up a couple questions that were raised about differences between MySQL and PostgreSQL syntax for data export.<br \/>\n<!--more--><br \/>\nShe showed me <code>\\pset fieldsep<\/code> and <code>\\pset format<\/code> for controlling interactive output from <code>SELECTS<\/code> (see <a href=\"http:\/\/www.postgresql.org\/docs\/current\/static\/app-psql.html\">psql documentation<\/a>).  You might say: <code>\\pset fieldsep ,<\/code> (although that wouldn&#8217;t be CSV.. but it&#8217;s quick and dirty). And <code>\\pset format<\/code> offers <code>unaligned, aligned, html, latex, or troff-ms<\/code>. There are several shortcuts available &#8211; <code>\\a<\/code> for aligned\/unaligned. A combination of <code>\\pset fieldsep<\/code> and <code>\\a<\/code> gets you nearly to CSV.<\/p>\n<p>Then we took a look at the <a href=\"http:\/\/www.postgresql.org\/docs\/8.2\/static\/sql-copy.html\">COPY<\/a> command and our options there. That&#8217;s when we discovered this: <\/p>\n<blockquote><p>\n<code><br \/>\nCOPY { tablename [ ( column [, ...] ) ] | ( <font color=\"red\">query<\/font> ) }<br \/>\n    TO { 'filename' | STDOUT }<br \/>\n<\/code>\n<\/p><\/blockquote>\n<p>See that <font color=\"red\">query<\/font>?  Yeah, super sweet. This feature was new in version 8.2. (<a href=\"http:\/\/www.postgresql.org\/developer\/beta\">8.3 beta<\/a> is out now!)<br \/>\n Now you can run a command like: <\/p>\n<blockquote><p>\n<code><br \/>\nCOPY (SELECT param1, param2, param3 from myview) TO STDOUT WITH CSV;<br \/>\n<\/code>\n<\/p><\/blockquote>\n<p>Or you can replace STDOUT with a file path. <code>\\copy<\/code> supports the same syntax. This is a reasonable alternative to MySQL&#8217;s <code>SELECT INTO OUTFILE<\/code>. And the feature has been there for at least a year.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Gabrielle and I met to talk about some projects today. She brought up a couple questions that were raised about differences between MySQL and PostgreSQL syntax for data export.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[97,9,8],"tags":[647],"class_list":["post-159","post","type-post","status-publish","format-standard","hentry","category-postgres","category-postgresql","category-sysadmin","tag-postgres"],"_links":{"self":[{"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/posts\/159","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/comments?post=159"}],"version-history":[{"count":2,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/posts\/159\/revisions"}],"predecessor-version":[{"id":4061,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/posts\/159\/revisions\/4061"}],"wp:attachment":[{"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/media?parent=159"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/categories?post=159"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/tags?post=159"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}