462

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: <value of row 1's c1>
 c2: <value of row 1's c1>
 ...
 cN: <value of row 1's cN>
 ---- some kind of delimiter ----
 c1: <value of row 2's c1>
 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?

Ciro Santilli OurBigBook.com
  • 347,512
  • 102
  • 1,199
  • 985
user100464
  • 17,331
  • 7
  • 32
  • 40

8 Answers8

765

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             |

See also:

StackzOfZtuff
  • 2,534
  • 1
  • 28
  • 25
user100464
  • 17,331
  • 7
  • 32
  • 40
  • 12
    You can also try \pset format wrapped (allowed formats are unaligned, aligned, wrapped, html, latex, troff-ms). If the display is narrow enough it will word wrap each column. – Bryce Apr 14 '13 at 23:05
  • 21
    `\x` alone will toggle the value, saving you three keystrokes! (Useful when you rapidly switch between wide and narrow outputs). – msanford Aug 27 '15 at 15:18
  • 7
    If you looking for equivalent to `\G` from Mysql, try append `\x\g\x` to the end of query or define shortcut in `~/.psqlrc` adding `\set G '\\set QUIET 1\\x\\g\\x\\set QUIET 0'`, then use on the end `:G`. (note lack of semicolons) – Sławomir Lenart Jul 18 '17 at 16:56
  • 5
    Is it possible to somehow pass "`\x on`" or the equivalent at the command line? It'd be nice if I could just save (for instance, by using a bash shell alias) `psql --something` so that I always have the option turned on by default. – machineghost Apr 01 '18 at 00:16
  • 4
    @machineghost Use `-P expanded=auto` or `--pset expanded=auto` – alecdwm Oct 02 '18 at 07:58
  • @alecdwm I am using pgcli 2.0.0 and this flag does not exists. I managed to solve this issue by going to https://www.pgcli.com/config and modifying my config file – Simon Bergot Dec 10 '18 at 11:24
  • 1
    @Simon - `pgcli` is not the same program as `psql` ;) – Glyph Dec 31 '18 at 22:38
  • How do you get it to display like that and not with html table element (``, `` etc.)? – aviya.developer Aug 16 '19 at 11:41
355

(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
StackzOfZtuff
  • 2,534
  • 1
  • 28
  • 25
notapatch
  • 6,569
  • 6
  • 41
  • 45
  • What option is available to fully enclose `normal formatted` tables with `dashes`? I like an outer enclosing box too, the way `MySQL` does it. – NYCeyes Apr 28 '21 at 14:31
94

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
Bryce
  • 8,313
  • 6
  • 55
  • 73
34

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

spazm
  • 4,399
  • 31
  • 30
Dineshkumar
  • 4,165
  • 5
  • 29
  • 43
28

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

pspg example

Tombart
  • 30,520
  • 16
  • 123
  • 136
11

If you are looking for psql command-line mode like me,

here is the syntax --pset expanded=auto

psql command-line options:
-P expanded=auto
--pset expanded=auto
-x
--expanded
...

Another way is -q option ref

Nam G VU
  • 33,193
  • 69
  • 233
  • 372
  • If you have multiple `-P` or `--pset` commands you can combine them like this. Here is a command I use to send to docker container `docker exec -it pgsql-container psql -P pager=off -P expanded=auto -U me -c "select * from main;"` – Dave May 09 '23 at 18:17
7

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.

Wexxor
  • 1,919
  • 1
  • 15
  • 17
1

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.

purushothaman poovai
  • 314
  • 1
  • 12