Is there a way to set the max width of a column when displaying query results in psql?

Question

The problem I'm having is that I have a table with 10 columns. 1 of those columns is HTML and, as a result, is really long. It's so long that it wraps around several lines and screws up the otherwise useful output. Normally, I've been selecting all of the columns except for that one. However, I'm starting to join with another table and have like 20 columns now.

Is there a way to specify the maximum column width or to truncate a column after a certain number of characters?

I tried \x. It kind of helped... but for the most part everything still looks scrambled.

Answer 1

you can specify columns width via \pset columns X

postgres=# select 'some longer text some longer text some longer text some longer text some longer text some longer text';
┌───────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                               ?column?                                                │
╞═══════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ some longer text some longer text some longer text some longer text some longer text some longer text │
└───────────────────────────────────────────────────────────────────────────────────────────────────────┘
(1 row)

postgres=# \pset format wrapped 
Output format is wrapped.
postgres=# \pset columns 20
Target width is 20.
postgres=# select 'some longer text some longer text some longer text some longer text some longer text some longer text';
┌──────────────────┐
│     ?column?     │
╞══════════════════╡
│ some longer text…│
│… some longer tex…│
│…t some longer te…│
│…xt some longer t…│
│…ext some longer …│
│…text some longer…│
│… text            │
└──────────────────┘
(1 row)

Be sure, so your pager is well configured - set global variables:

export PAGER=less
export LESS='-iMSx4 -RSFX -e'

Answer 2

The two psql options that affect the width of a column for screen output are
\pset format
\pset columns

You can execute those two commands to see their current values.

In my experience, to get the best output, set them to be:

=> \pset format wrapped
Output format is wrapped.
=> \pset columns 0     
Target width is unset.

wrapped is probably self-explanitory, but setting columns 0 is a "special" value that tells psql to set column widths based on the detected screen width *. In my experience, it does a nice dynamic width where columns with shorter data content are given narrower columns.

This will, however, still give you the full text of the value for that row/column, which the OP also wishes to be able to truncate. The most memorable function for me is
rpad(<column_name>,<max_width>).
If you want to keep the column name in the header, instead of "rpad" then use
rpad(<column_name>,<max_width>) as <column_name>