Change Result Set Format

Moderator: NorbertKrupa

Post Reply
User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Change Result Set Format

Post by JimKnicely » Wed Feb 01, 2012 7:02 pm

The default output format used by vSQL isn't the best if need to cut and paste the result set of a query into another application. The \pset command can be used to change the formatting.

For example, I can get rid of the vertical bars with \pset border 0

Code: Select all

dbadmin=> select * from test_table;
 name  | age
-------+-----
 Pat   |  29
 Brian |  14
 Jim   |   9
 Tom   |  53
 Mark  | 100
(5 rows)

dbadmin=> \pset border 0
Border style is 0.
dbadmin=> select * from test_table;
name  age
----- ---
Pat    29
Brian  14
Jim     9
Tom    53
Mark  100
(5 rows)
And I can also format it nicely for a csv file, for instance:

Code: Select all

dbadmin=> \pset format unaligned
Output format is unaligned.
dbadmin=> \pset fieldsep ,
Field separator is ",".
dbadmin=> \pset tuples_only
Showing only tuples.
dbadmin=> select * from test_table;
Pat,29
Brian,14
Jim,9
Tom,53
Mark,100
dbadmin=>
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

pmangg
Newbie
Newbie
Posts: 1
Joined: Wed Jun 20, 2012 5:27 pm

Re: Change Result Set Format

Post by pmangg » Wed Jun 20, 2012 5:31 pm

Can this be done programmatically when vsql is invoked? I would like to get a csv out from a query by doing:

/opt/vertica/bin/vsql -d${dbname} -X -f test.sql

where test.sql contains:

select * from testtable;

Normally in postgres I would do copy to stdout, but it seems like this functionality doesn't exist in vertica.

Thanks!

User avatar
Jbaskin
Intermediate
Intermediate
Posts: 61
Joined: Sat Jan 28, 2012 1:21 pm

Re: Change Result Set Format

Post by Jbaskin » Wed Jun 20, 2012 8:03 pm

Hi,

A bunch of folks already mentiond some of the command line arguments for vsql.

Code: Select all

dbadmin=> select * from test;
 col1  | col2
-------+------
 test  |    1
 test2 |    2
 test3 |    3
(3 rows)

dbadmin=> \q
root$
root$ /opt/vertica/bin/vsql -o /usr/home/dbadmin/test.txt -F ',' -At -c "select * from test"
root$ cat /usr/home/dbadmin/test.txt
SET
test,1
test2,2
test3,3
But I can't figure out the command line argumentt to get rid the the SET response from Vertica in the out file!

jpcavanaugh
Intermediate
Intermediate
Posts: 149
Joined: Mon Apr 30, 2012 10:04 pm
Location: New York
Contact:

Re: Change Result Set Format

Post by jpcavanaugh » Wed Jun 20, 2012 9:15 pm

Can you try adding the '-q' argument to vsql?

User avatar
Jbaskin
Intermediate
Intermediate
Posts: 61
Joined: Sat Jan 28, 2012 1:21 pm

Re: Change Result Set Format

Post by Jbaskin » Thu Jun 21, 2012 12:35 pm

Can you try adding the '-q' argument to vsql?

Code: Select all

bash-3.2$ /opt/vertica/bin/vsql -o /usr/home/dbadmin/test.txt -F ',' -At -c "select * from test" -q
bash-3.2$ cat /usr/home/dbadmin/test.txt
test,1
test2,2
test3,3
bash-3.2$
That worked! Thanks, juniorfoo!

jpcavanaugh
Intermediate
Intermediate
Posts: 149
Joined: Mon Apr 30, 2012 10:04 pm
Location: New York
Contact:

Re: Change Result Set Format

Post by jpcavanaugh » Thu Jun 21, 2012 7:20 pm

Glad it worked for you!

Post Reply

Return to “vSQL”