The writings of Merlin Moncure, professional database developer, about work, life, family, and everything else.

Friday, October 19, 2007

A better psql with less

psql is a great tool, but not very good at browsing data. Or is it? The following settings will make psql much more usable.

in your user profile:
export PAGER=less
export LESS="-iMSx4 -FX"

in your .psqlrc (make it if it's not already there)

\timing
\pset pager always

try it!! now wide query results will paginate much better and searching is much easier.

update: fixed quotes for LESS, thanks depesz

16 comments:

Anonymous said...

In psql for windows, is there a way to use VIew as a pager in side of psql? Are there any other pagers like less for windows that have a similar behavior that can be used?

Merlin Moncure said...

iirc (it's been a while since i worked on windows), win should follow the same rules...so, you can set environment variables (in the system control panel) for the same effect. windows doesn't ship with less, only more, so you will have to find that as well...try the gnu page, cygwin, or mingw.

Gary Chambers said...

I would also suggest adding to the LESS environment the "-e" switch. I have long found it annoying to have to explicitly quit the pager, so this will allow quitting by pressing a key a second time when at the end of the output.

Anonymous said...

May I just add an , 'A-men' to the -e (minus eeee) suggestion to this post. That is _much_ more intuitive to use under man pages or whatever. Thank you, I'm adding that nugget to my standard post-install notebook.

NickBurnsSNL ;)

Anonymous said...

Exactly what I was looking for, thanks for pointing it out!

Anonymous said...

People should read this.

Anonymous said...

thanks guys, that really makes the difference :)

raptor said...

Thanks .. perfect .. it is life saver

massimo said...

thank you for yuor tip

Anonymous said...

Sweet

Theo said...

Gorgeous!

Steve said...

psql-specific LESS options can be set in .psqlrc using \setenv, so if your preferred options in PostgreSQL conflict with your normal LESS options, you can put them there.

Merlin Moncure said...

Steve, you just made my day

Anonymous said...

Note that if you put the less flags in your .psqlrc the syntax is slightly different:

\timing
\pset pager always
\setenv LESS '-iMSx4 -FX'

Merlin Moncure said...

anon,
\setenv trick is defintely the way to go. thanks for the heads up!

Anonymous said...

I used this LESS configuration and it messed up my `git log --oneline --decorate --graph` output. But if you add an 'R' flag, colors will also work.

I.E.

export LESS="-iMSx4 -FXR"