Changing the Field Separator in VSQL: Quick Tip

Posted September 5, 2018 by Jim Knicely, Big Data Solutions Architect, Vertica

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/latest/HTML/index.htm#Authoring/ConnectingToVertica/vsql/Meta-Commands/psetNAMEVALUE.htm

Have fun!