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

Wednesday, September 12, 2012

psql -- now with a splash of color!

I was tired of looking at psql's dull grey output and decided to see if I could work in some color.   I was able to do this -- it's pretty hacky but seems to work well enough for me to publish here.  first, if you haven't already, please read the instructions here for a good primer for how to drastically improve psql data browsing through the less utility (this will work on windows, but only through cygwin based terminals like the excellent rxvt).

My .bashrc is now including this:

export YELLOW=`echo -e '\033[1;33m'`
export LIGHT_CYAN=`echo -e '\033[1;36m'`
export NOCOLOR=`echo -e '\033[0m'`

export LESS="-iMSx4 -FXR"

PAGER="sed \"s/\([[:space:]]\+[0-9.\-]\+\)$/${LIGHT_CYAN}\1$NOCOLOR/;" 
PAGER+="s/|/$YELLOW|$NOCOLOR/g;s/^\([-+]\+\)/$YELLOW\1$NOCOLOR/\" 2>/dev/null  | less"
export PAGER

My .psqlrc typically looks like this (you might want to get in the habit for disabling it with psql -X for output sensitive scripts):

\pset pager always

you should see output like this:

pretty cool, huh?  it works for table properties too:

relying on sed of course is pretty sketchy -- if just the right combination of characters come up you'll see garbled output -- but it seems to work pretty well.  of course, disabling the pager is always an option.  Ideally, we'd have tighter control over output formatting for rich color displays (like, having stderr ERROR messages in red for example).

edit: blogspot was garbling the string...breaking it up fixed it
edit2: see comments for method of non-global adjustment of LESS (in case other uses of LESS are impacted by the settings)


Cindy Wise said...

This is very cool.
I also like changing my prompt to include time, user, etc.:

Cindy Wise

Merlin Moncure said...

thanks cindy! good to hear from you.

Martin Atukunda said...

I tried this but it breaks other uses of less like the man command

Merlin Moncure said...


If you don't want to adjust LESS globally, try replacing this:

export LESS="-iMSx4 -FXR"

with this:

alias psql='LESS="-iMSx4 -FXR" psql'

in your .bashrc