🍿 Postgres's Expanded display mode
Snacks (🍿) are my collection of recipes for solving problems. Often recorded (and cleaned up) from actual discussions where I'm involved in helping others with technical problems. Not all solutions are mine but I started collecting them in one place because you never know when you need one.
If you have a lot of columns on your database and you make a query fetching them on terminal using psql, it's likely they will wrap and become unreadable because a row is no longer a row.
Here I'm using Lego database sample for example:
lego=# select * from lego_sets join lego_themes on lego_sets.theme_id = lego_themes.id;
set_num | name | year | theme_id |
num_parts | id | name | parent_id
------------------+-------------------------------------------------------------------+------+----------
+-----------+-----+-------------------------------+-----------
00-1 | Weetabix Castle | 1970 |
414 | 471 | 414 | Castle | 411
0011-2 | Town Mini-Figures | 1978 |
84 | 12 | 84 | Supplemental | 67
0011-3 | Castle 2 for 1 Bonus Offer | 1987 |
199 | 2 | 199 | Lion Knights | 186
0012-1 | Space Mini-Figures | 1979 |
143 | 12 | 143 | Supplemental | 126
0013-1 | Space Mini-Figures | 1979 |
143 | 12 | 143 | Supplemental | 126
0014-1 | Space Mini-Figures | 1979 |
143 | 12 | 143 | Supplemental | 126
0015-1 | Space Mini-Figures | 1979 |
143 | 18 | 143 | Supplemental | 126
0016-1 | Castle Mini Figures | 1978 |
186 | 15 | 186 | Castle |
00-2 | Weetabix Promotional House 1 | 1976 |
413 | 147 | 413 | Building | 411
00-3 | Weetabix Promotional House 2 | 1976 |
413 | 149 | 413 | Building | 411
00-4 | Weetabix Promotional Windmill | 1976 |
413 | 126 | 413 | Building | 411
I learned today, thanks to Richard Yen, that there's an extended display mode that turns rows into columns and makes the results readable.
To activate it, run \x in psql before running your query.
lego=# \x
lego=# select * from lego_sets join lego_themes on lego_sets.theme_id = lego_themes.id;
and the result is much more readable
-[ RECORD 1 ]----------------------------------------------------------------------------------------------
set_num | 00-1
name | Weetabix Castle
year | 1970
theme_id | 414
num_parts | 471
id | 414
name | Castle
parent_id | 411
-[ RECORD 2 ]----------------------------------------------------------------------------------------------
set_num | 0011-2
name | Town Mini-Figures
year | 1978
theme_id | 84
num_parts | 12
id | 84
name | Supplemental
parent_id | 67
-[ RECORD 3 ]----------------------------------------------------------------------------------------------
set_num | 0011-3
name | Castle 2 for 1 Bonus Offer
year | 1987
theme_id | 199
num_parts | 2
id | 199
name | Lion Knights
parent_id | 186
-[ RECORD 4 ]----------------------------------------------------------------------------------------------
set_num | 0012-1
name | Space Mini-Figures
year | 1979
theme_id | 143
num_parts | 12
id | 143
name | Supplemental
parent_id | 126
Once I shared this, I learned that you can also run
lego=# \gx
after running a query to get its output in the expanded mode so you don't even have to re-run the query manually if you notice it was hard to read.