Alternate output format for psql

Question

I am using PostgreSQL 8 4 on Ubuntu  I have a table with columns c1 through cN  The columns are wide enough that selecting all columns causes a row of query results to wrap multiple times  Consequently  the output is hard to read   When the query results constitute just a few rows  it would be convenient if I could view the query results such that each column of each row is on a separate line  e g    c1   lt value of row 1 s c1 gt   c2   lt value of row 1 s c1 gt        cN   lt value of row 1 s cN gt   ---- some kind of delimiter ----  c1   lt value of row 2 s c1 gt   etc    I am running these queries on a server where I would prefer not to install any additional software  Is there a psql setting that will let me do something like that

User · Answer

If you are looking for psql command-line mode like me  here is the syntax --pset expanded auto Quoted it here  psql command-line options   -P expanded auto  --pset expanded auto  -x  --expanded   Another 2nd way is -q option ref

User · Answer

One interesting thing is we can view the tables horizontally  without folding  we can use PAGER environment variable  psql makes use of it  you can set   export PAGER   usr bin less -S    or just less -S if its already availble in command line  if not with the proper location  -S to view unfolded lines  you can pass in any custom viewer or other options with it   I ve written more in Psql Horizontal Display

User · Answer

You have so many choices  how could you be confused  -    The main controls are       pset format    H    x    pset pager off   Each has options and interactions with the others   The most automatic options are      x off  pset format wrapped    x auto   The newer   x auto  option switches to line-by-line display only  if needed    -  RECORD 1  --------------- id            6 description   This is a gallery of oilve oil brands  authority     I love olive oil  and wanted to create a place for reviews and comments on various types  -  RECORD 2  --------------- id            19 description   XXX Test A  authority     Testing   The older   pset format wrapped  is similar in that it tries to fit the data neatly on screen  but falls back to unaligned if the headers won t fit   Here s an example of wrapped     id            description                        authority             ---- -------------------------------- ---------------------------------   6   This is a gallery of oilve       I love olive oil  and wanted to       oil brands                        create a place for reviews and                                         comments on various types   19   Test Test A                      Testing

User · Answer

New  Expanded Auto Mode   x auto  New for Postgresql 9 2  PSQL automatically fits records to the width of the screen  previously you only had expanded mode on or off and had to switch between the modes as necessary     If the record can fit into the width of the screen  psql uses normal formatting  If the record can not fit into the width of the screen  psql uses expanded mode    To get this use   x auto  Postgresql 9 5 Documentation on PSQL command     Wide screen  normal formatting    id   time          humanize time               value  ---- ------- --------------------------------- -------   1   09 30    Early Morning -  9 30 am           570   2   11 30    Late Morning -  11 30 am           690   3   13 30    Early Afternoon -  1 30pm          810   4   15 30    Late Afternoon -  3 30 pm          930  4 rows      Narrow screen  expanded formatting   -  RECORD 1  - --------------------------- id              1 time            09 30 humanize time   Early Morning -  9 30 am  value           570 -  RECORD 2  - --------------------------- id              2 time            11 30 humanize time   Late Morning -  11 30 am  value           690 -  RECORD 3  - --------------------------- id              3 time            13 30 humanize time   Early Afternoon -  1 30pm  value           810 -  RECORD 4  - --------------------------- id              4 time            15 30 humanize time   Late Afternoon -  3 30 pm  value           930     How to start psql with  x auto   Configure  x auto command on startup by adding it to  psqlrc in your home folder and restarting psql  Look under  Files  section in the psql doc for more info      psqlrc   x auto

User · Answer

Also be sure to check out  H  which toggles HTML output on off   Not necessarily easy to read at the console  but interesting for dumping into a file  see  o  or pasting into an editor browser window for viewing  especially with multiple rows of relatively complex data

User · Answer

you can use the zenity to displays the  query output as html table    first implement bash script with following code   cat     tmp sql op   zenity --text-info  --html --filename   tmp sql op    save it like mypager sh Then export the environment variable PAGER by set full path of the script as value    for example - export PAGER   path mypager sh  Then login to the psql program then execute the command  H And finally execute any query the tabled output will displayed in the zenity in html table format

User · Answer

I just needed to spend more time staring at the documentation   This command    x on   will do exactly what I wanted   Here is some sample output   select   from dda where u id 24 and dda is deleted  f   -  RECORD 1  ------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- dda id               1121 u id                 24 ab id                10304 dda type             CHECKING dda status           PENDING VERIFICATION dda is deleted       f dda verify op id     44938 version              2 created              2012-03-06 21 37 50 585845 modified             2012-03-06 21 37 50 593425 c id                  dda nickname          dda account name      cu id                1 abd id

User · Answer

pspg is a simple tool that offers advanced table formatting  horizontal scrolling  search and many more features   git clone https   github com okbob pspg git cd pspg   configure make make install   then make sure to update PAGER variable e g  in your    bashrc  export PAGER  pspg -s 6     where -s stands for color scheme  1-14   If you re using pgdg repositories simply install a package  on Debian-like distribution    sudo apt install pspg

[postgresql] Alternate output format for psql

Examples related to postgresql

Examples related to output-formatting

Examples related to psql