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: <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?

Answer 1

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             |  

Answer 2

(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