Output Formatting Examples
By default, Vertica formats query output as follows:
=> SELECT DISTINCT category_description FROM product_dimension ORDER BY category_description; category_description ---------------------------------- Food Medical Misc Non-food (4 rows)
You can control the format of query output in various ways with the \pset
command—for example, change the border:
=> \pset border 2 Border style is 2. => SELECT DISTINCT category_description FROM product_dimension ORDER BY category_description; +----------------------------------+ | category_description | +----------------------------------+ | Food | | Medical | | Misc | | Non-food | +----------------------------------+ (4 rows)
=> \pset border 0 Border style is 0. => SELECT DISTINCT category_description FROM product_dimension ORDER BY category_description; category_description -------------------------------- Food Medical Misc Non-food (4 rows)
The following sequence of pset
commands change query output in several ways:
- Set border style to 1.
- Remove column alignment.
- Change the field separator to a comma.
- Remove column headings
=> \pset border 1 Border style is 1. => \pset format unaligned Output format is unaligned. => \pset fieldsep ',' Field separator is ",". => \pset tuples_only Showing only tuples. => SELECT product_key, product_description, category_description FROM product_dimension LIMIT 10; 1,Brand #2 bagels,Food 1,Brand #1 butter,Food 2,Brand #6 chicken noodle soup,Food 3,Brand #11 vanilla ice cream,Food 4,Brand #14 chocolate chip cookies,Food 4,Brand #12 rash ointment,Medical 6,Brand #18 bananas,Food 7,Brand #25 basketball,Misc 8,Brand #27 french bread,Food 9,Brand #32 clams,Food
The following example uses meta-commands to toggle output format—in this case, \a
(alignment), \t
(tuples only), and -x
(extended display):
=> \a \t \x Output format is aligned. Tuples only is off. Expanded display is off. => SELECT product_key, product_description, category_description FROM product_dimension LIMIT 10; product_key | product_description | category_description -------------+----------------------------------+---------------------------------- 1 | Brand #2 bagels | Food 1 | Brand #1 butter | Food 2 | Brand #6 chicken noodle soup | Food 3 | Brand #11 vanilla ice cream | Food 4 | Brand #14 chocolate chip cookies | Food 4 | Brand #12 rash ointment | Medical 6 | Brand #18 bananas | Food 7 | Brand #25 basketball | Misc 8 | Brand #27 french bread | Food 9 | Brand #32 clams | Food (10 rows)
The following example sets output format to HTML, so Vertica renders query results in HTML markup as a table:
=> \pset format html Output format is html. => \pset tableattr 'border="2" cellpadding="3"' Table attribute is "border="2" cellpadding="3"". => SELECT product_key, product_description, category_description FROM product_dimension LIMIT 2; <table border="1" border="2" cellpadding="3"> <tr> <th align="center">product_key</th> <th align="center">product_description</th> <th align="center">category_description</th> </tr> <tr valign="top"> <td align="right">1</td> <td align="left">Brand #2 bagels</td> <td align="left">Food </td> </tr> <tr valign="top"> <td align="right">1</td> <td align="left">Brand #1 butter</td> <td align="left">Food </td> </tr> </table> <p>(2 rows)<br /> </p>