CODEWITHSUNDEEP
stringpostgresqlpsqlvarchar
Timur Shtatland
Timur Shtatland

Reputation: 12377

How do I set maximum column width in psql?

How do I truncate multiple long columns in psql? I have multiple columns, some of which are long, and I would like to fit them all easily in one terminal window. \pset format wrapped and \pset columns COLUMNS appear to work for some, but not all cases. I mostly care about character varying columns, and it does not seem to limit their length or wrap them properly. It works for a simple case of 1 string only.

I am aware of substr() and of \x, but these are suboptimal. I have many columns, so substr() on each of them is too repetitive. And tabular output works best for me here, so \x is not as easy to view.

Example:

Set pager properly and run psql:

export PAGER=less
export LESS='-iMSx4 -RSFX -e'
psql -d db_foo

Settings work for the simplest case only:

db_foo=# \pset format wrapped
Output format is wrapped.
db_foo=# \pset columns 5
Target width is 5.
db_foo=# select '01234456789 01234456789 01234456789' as foo;
 foo 
-----
 012.
.344.
.567.
.89 .
.012.
...
(1 row)

 -- OK, output is truncated.

db_foo=# select baz from table_baz limit 2;       
             baz              
-----------------------------------
 Salmon Lightweight, Galaxy v1.0.0
 Salmon quant Galaxy v0.14.1.2
(2 rows)

-- not OK, output is not truncated.

db_foo=# select '01234456789 01234456789 01234456789' as foo, baz from table_baz limit 2;
                 foo                 |             baz              
-------------------------------------+-----------------------------------
 01234456789 01234456789 01234456789 | Salmon Lightweight, Galaxy v1.0.0
 01234456789 01234456789 01234456789 | Salmon quant Galaxy v0.14.1.2
(2 rows)

-- not OK, output is not truncated - even the strings 
-- that were truncated before.

I am running macOS 10.14.6, using:

psql --version
psql (PostgreSQL) 12.3

less --version
less 487 (POSIX regular expressions)

RELATED:

How to limit the maximum display length of a column in PostgreSQL
How do I change the max column width in PostgreSQL?
Is there a way to set the max width of a column when displaying JSONB results in psql?
Is there a way to set the max width of a column when displaying query results in psql?

SEE ALSO:

PostgreSQL: Documentation: psql

columns
Sets the target width for the wrapped format, and also the width limit for determining whether output is wide enough to require the pager or switch to the vertical display in expanded auto mode. Zero (the default) causes the target width to be controlled by the environment variable COLUMNS, or the detected screen width if COLUMNS is not set. In addition, if columns is zero then the wrapped format only affects screen output. If columns is nonzero then file and pipe output is wrapped to that width as well.

Upvotes: 0

Views: 1878

Answers (1)

Adrian Klaver
Adrian Klaver

Reputation: 19664

The issue is from \pset wrapped(described here psql):

...Note that psql will not attempt to wrap column header titles; therefore, wrapped format behaves the same as aligned if the total width needed for column headers exceeds the target.

Set the columns value to one that exceeds the header title widths.

Upvotes: 1

Related Questions