How to turn on the expanded table formatting mode in PostgreSQL

·

2 min read

Note: The following was tested with PostgreSQL 13.1

Default setting: \x off

$ psql myblog_dev

myblog_dev=# SELECT "users".* FROM "users" WHERE "users"."role" = 10 LIMIT 1;

The output format would be:

It was difficult to read the result.

Turn on the expanded table formatting mode by \x on.

$ psql myblog_dev

myblog_dev=# \x on
Expanded display is on.

myblog_dev=# SELECT "users".* FROM "users" WHERE "users"."role" = 10 LIMIT 1;

The output format would be:

It became more pretty and readable!


Switching the mode manually might be inconvenient. For example,

myblog_dev=# \x on 

myblog_dev=# select * from json_each_text('{ "name": "Lynn", "size": "S", "gender": "Female"}');

I expected to got the format like this:

  key   | value
--------+--------
 name   | Lynn
 size   | S
 gender | Female
(3 rows)

However, the format I got looked like this:

-[ RECORD 1 ]-
key   | name
value | Lynn
-[ RECORD 2 ]-
key   | size
value | S
-[ RECORD 3 ]-
key   | gender
value | Female

It's simple to print the format I expect. All I need to do is switch \x on to \x off.

After PostgreSQL 9.2, we don't have to manually switch between the modes anymore. We can use \x auto and PostgreSQL fits records to the width of the screen automatically.

\x [ on | off | auto ] Sets or toggles expanded table formatting mode. As such it is equivalent to \pset expanded. (Source)

myblog_dev=# \x auto
Expanded display is used automatically.

myblog_dev=# select * from json_each_text('{ "name": "Lynn", "size": "S", "gender": "Female"}');
  key   | value
--------+--------
 name   | Lynn
 size   | S
 gender | Female
(3 rows)


myblog_dev=# SELECT "users".* FROM "users" WHERE "users"."role" = 10 LIMIT 1;
-[ RECORD 1 ]----------+-------------------------------------------------------------
id                     | 1
email                  | my_email
encrypted_password     | $secret
reset_password_token   |
reset_password_sent_at |
remember_created_at    |
sign_in_count          | 0
current_sign_in_at     |
last_sign_in_at        |
current_sign_in_ip     |
last_sign_in_ip        |
confirmation_token     |
confirmed_at           |
confirmation_sent_at   |
unconfirmed_email      |
failed_attempts        | 0
unlock_token           |
locked_at              |
created_at             | 2021-05-24 10:55:33.368015
updated_at             | 2021-05-24 12:02:59.605434
role                   | 10
name                   |
subscribe_edm          | {}

Reference