We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Changing the Field Separator in VSQL — Vertica Forum

Changing the Field Separator in VSQL

Jim_KnicelyJim_Knicely - Select Field - Administrator
edited September 2018 in Tips from the Team

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!

Sign In or Register to comment.