vsql - pset options unavailable

Navin_CNavin_C Vertica Customer



I am trying to see my output of all columns in one row, when using vsql prompt.


Normally when we have more columns then the width of screen the records go in multiple lines.


With postgreSQL psql prompt, there is an option of setting the pager to wrapped style


\pset format wrapped



But when tried with vsql, it shows this :


nnani=>\pset format wrapped
\pset: allowed formats are unaligned, aligned, html, latex.



Is there any other way I can achieve this in vsql, I don't want the outpout in horizontal format, I want it in vertical format and all the columns should we fitted to width of screen.

Can we put a feature request if this option in not available


  • Hi Navin_C,


    Unfortunately, vsql doesn't support this option.


    However, there are workarounds. You can set the program vsql uses to display its output by using the PAGER environment variable. You can also change the output format around by using the \pset commands. Using these two features in conjunction can let you get some of the functionality you want.


    Disclaimer: this isn't an official suggestion... this is just a hacky workaround I found. Use at your own risk.


    For example, you can have vsql format its query output as HTML tables using \pset format html. There are some tools under Linux that can format HTML to be displayed as text on the terminal. One such program available for CentOS is html2text. If you have root access, you can install it using this command as root:


    yum install html2text



    It seems pretty standard, so it should be available for other Linux flavors as well.


    So, without tweaking, we have output like this:


    dbadmin=> select * from messages limit 10;
    messageid | userid | text | timestamp
    1 | 78542 | sociis orci Curae nulla congue Curae Lorem orci vitae Proin eros a consectetuer torquent felis | 2015-04-02 00:00:00
    2 | 10364 | pede vel Sed nec adipiscing ad Fusce boo tincidunt amet nascetur senectus nostra neque dolor | 2015-04-02 00:00:00
    3 | 76263 | orci Vivamus lacinia Ut montes diam Duis nulla lorem elementum facilisi Aliquam elit sociis venenatis elit libero | 2015-04-02 00:00:00
    4 | 66561 | quam risus mollis vestibulum rutrum neque Proin tortor mollis placerat vulputate amet | 2015-04-02 00:00:00
    5 | 98308 | facilisi erat cubilia commodo amet senectus felis justo Nullam pretium at semper diam nulla est Sed viverra | 2015-04-02 00:00:00
    6 | 38230 | sapien Phasellus ligula mattis ad libero euismod tellus at cubilia congue | 2015-04-02 00:00:00
    7 | 93004 | per dis luctus iaculis Praesent Pellentesque Aliquam | 2015-04-02 00:00:00
    8 | 77991 | elit sociis ultrices pede Vivamus velit lobortis quam molestie porttitor non elementum natoque | 2015-04-02 00:00:01
    9 | 86680 | a nunc venenatis semper fermentum eros dis imperdiet nibh mattis massa pulvinar ante Donec lectus ad mi | 2015-04-02 00:00:01
    10 | 75612 | ut mi massa primis tincidunt tempus Curae ut dui in Sed | 2015-04-02 00:00:01
    (10 rows)




    The first step is setting the PAGER environment variable in the Linux shell to the html2text program:


    [dbadmin@docd01 ~]$ export PAGER=html2text



    (Personally, I wouldn't hard-code this environment variable anyplace. I believe PAGER is used by other Linux commands as well, such as man. Instead I would just manually set it before running vsql, or set it in a script that calls vsql.)


    Then run vsql and set the output format to HTML. I also set the pager setting to always, to force vsql to always pipe the output through the pager, even if it thinks it won't scroll off the screen.


    [dbadmin@docd01 ~]$ vsql
    Welcome to vsql, the Vertica Analytic Database interactive terminal.

    Type:  \h or \? for help with vsql commands
           \g or terminate with semicolon to execute query
           \q to quit

    dbadmin=> \pset format html
    Output format is html.
    dbadmin=> \pset pager always
    Pager is always used.
    dbadmin=> select * from messages limit 10;
    |        1| 78542|sociis orci Curae nulla congue Curae    |2015-04-02 00:00:00|
    |         |      |Lorem orci vitae Proin eros a           |                   |
    |        2| 10364|pede vel Sed nec adipiscing ad Fusce boo|2015-04-02 00:00:00|
    |         |      |tincidunt amet nascetur senectus nostra |                   |
    |        3| 76263|orci Vivamus lacinia Ut montes diam Duis|2015-04-02 00:00:00|
    |         |      |nulla lorem elementum facilisi Aliquam  |                   |
    |        4| 66561|quam risus mollis vestibulum rutrum     |2015-04-02 00:00:00|
    |         |      |neque Proin tortor mollis placerat      |                   |
    |        5| 98308|facilisi erat cubilia commodo amet      |2015-04-02 00:00:00|
    |         |      |senectus felis justo Nullam pretium at  |                   |
    |        6| 38230|sapien Phasellus ligula mattis ad libero|2015-04-02 00:00:00|
    |        7| 93004|per dis luctus iaculis Praesent         |2015-04-02 00:00:00|
    |        8| 77991|elit sociis ultrices pede Vivamus velit |2015-04-02 00:00:01|
    |         |      |lobortis quam molestie porttitor non    |                   |
    |        9| 86680|a nunc venenatis semper fermentum eros  |2015-04-02 00:00:01|
    |         |      |dis imperdiet nibh mattis massa pulvinar|                   |
    |       10| 75612|ut mi massa primis tincidunt tempus     |2015-04-02 00:00:01|
    (10 rows)



    (Edit: the code box ironically messes up code output... grrr.)


    Much nicer output.


    I've only tried this on Linux. I'm not sure whether it works with the vsql client on Windows, for example.

  • Oh, and also note that you can pipe html2text's output through a pager program such as more, so it pauses every screenful, like so:


     $export PAGER='html2text | more'


Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file