Changing the Field Separator in VSQL

Jim_Knicely · September 2018

vsql is a character-based, interactive, front-end utility that lets you type SQL statements and see the results. It’s very common to want to export data in CSV (Comma-Separated Values) format. To do that you can change the default | (vertical bar) field separator to a comma via the fieldsep option of the pset meta-command.

Example:

dbadmin=> SELECT * FROM test;
   c1    | c2 |   c3
---------+----+---------
Vertica | is | Awesome
(1 row)

dbadmin=> \pset fieldsep ','
Field separator is ",".

dbadmin=> SELECT * FROM test;
   c1    | c2 |   c3
---------+----+---------
Vertica | is | Awesome
(1 row)

Wait a second! I changed the field separator but vsql is still displaying the default | (vertical bar)!

Turns out the fieldsep option is only valid when the output format is set to unaligned. How do I do that? With the format option!

dbadmin=> \pset format unaligned
Output format is unaligned.

dbadmin=> SELECT * FROM test;
c1,c2,c3
Vertica,is,Awesome
(1 row)

Helpful link:
https://my.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/ConnectingToVertica/vsql/Meta-Commands/psetNAMEVALUE.htm

Have fun!