Postgres/Vertica : Outputting Various Delimited Formats

by bitznbitez

A walkup question today focused on how to output the results of a query to a file with comma, tab, or pipe delimited format in Vertica 5.x.  This also applies to Postgres.

\a
\pset fieldsep ','
\o | gzip > foo.gz
select * from sometable;

The \a is unaligned output so the file isn’t padded with whitespace.  The \pset command sets the charachter you will seperate the fields with.   If you want to use tab delimited it would be “\pset fieldsep ‘\t'”.  Finally the \o will send output to a file, or a pipe command.  In this case a pipe to gzip the output and send it to a file foo.gz

It really is that easy….

Advertisements