# How to turn on the expanded table formatting mode in PostgreSQL

***Note: The following was tested with PostgreSQL 13.1***

### Default setting: `\x off`

```sql
$ psql myblog_dev

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

The output format would be:

![](https://www.lynnbright.com/content/images/2021/12/psql-myblog_dev-2021-12-01-17-44-49.png align="left")

It was difficult to read the result.

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

```sql
$ 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:

![](https://www.lynnbright.com/content/images/2021/12/psql-myblog_dev-2021-12-01-17-49-47.png align="left")

It became more pretty and readable!

---

Switching the mode manually might be inconvenient. For example,

```sql
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:

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

However, the format I got looked like this:

```plaintext
-[ 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](https://www.postgresql.org/docs/9.2/app-psql.html))

```sql
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

* [https://www.postgresql.org/docs/9.2/app-psql.html](https://www.postgresql.org/docs/9.2/app-psql.html)
    
* [https://www.postgresql.org/docs/9.5/functions-json.html](https://www.postgresql.org/docs/9.5/functions-json.html)
    
* [https://stackoverflow.com/questions/9604723/alternate-output-format-for-psql-showing-one-column-per-line-with-column-name/16108898#16108898](https://stackoverflow.com/questions/9604723/alternate-output-format-for-psql-showing-one-column-per-line-with-column-name/16108898#16108898)