Colorizing The psql Prompt — A Guide

The shell can be a bland, boring thing to stare at day after day.

So we shell dwellers spend an inordinate amount of time customizing our text based world. And there is no shortage of examples of impressively pimped out shell prompts that abound on the Internet. Just take a stroll through this wiki on color bash prompts at Arch Linux, for example. Inspiring, isn't it! (If to you it's not, you might as well abandon reading this article now.)

Quite some time ago, I came across +Craig Kerstiens' outstanding intro (which I highly recommend) to customizing your psql prompt, Getting more out of psql (The PostgreSQL CLI). I promptly (no pun intended) applied pretty much all of his customizations to my .psqlrc file, but never quite understood the arcane language of symbols that are responsible for the colorized prompt. So I set about to deconstruct it that I might build my own. The following is a summary of the process used to arrive at the example shown on the left.

So, let's begin with the final product and break it down from there. (My format here is essentially an adaptation of a connection URI in libpq.)

\set PROMPT1 '\n%[%033[1;31;40m%] ➤ %[%033[32m%]psql:%[%033[36m%]//%n%[%033[34m%]@%[%033[36m%]%`hostname`:%>%[%033[33m%]/%/ %[%033[K%]%[%033[0m%]\n\n%[%033[1;33m%]%#%[%033[0m%] '

Yep.  Complete gibberish. Let's make things a bit clearer:

\set PROMPT1 '\n%[%033[1;31;40m%]%[%033[32m%]psql:%[%033[36m%]//%n%[%033[34m%]@%[%033[36m%]%`hostname`:%>%[%033[33m%]/%/ %[%033[K%]%[%033[0m%]\n\n%[%033[1;33m%]%#%[%033[0m%] '

What we've done here is to obscure what are referred to as 'terminal control characters', leaving only a combination of
  • actual characters that will be printed at the prompt ( ➤, psql://, @, \n , etc.)
  • psql specific commands and variables (\set PROMPT1)
  • psql substitution strings (%n, %#, etc.)

Taking it down just one more notch:

\set PROMPT1 '\n%[%033[1;31;40m%]%[%033[32m%]psql:%[%033[36m%]//%n%[%033[34m%]@%[%033[36m%]%`hostname`:%>%[%033[33m%]/%/ %[%033[K%]%[%033[0m%]\n\n%[%033[1;33m%]%#%[%033[0m%] '

And you'll find that we are left with sets of matching %[ ... ]% sequences. As described in the docs, these allow the use of terminal control characters that can, among other things, colorize our prompt. And that's what we're after. So let's turn this on it's head and look at what's within those brackets.

Inverting our highlighting somewhat:

\set PROMPT1 '\n%[%033[1;31;40m%] ➤ %[%033[32m%]psql:%[%033[36m%]//%n%[%033[34m%]@%[%033[36m%]%`hostname`:%>%[%033[33m%]/%/ %[%033[K%]%[%033[0m%]\n\n%[%033[1;33m%]%#%[%033[0m%] '

So what do we have here? These, my friends, are terminal control codes. And they are the secret sauce we're looking for.

Isolated like this, it's easy to quickly pick out a pattern. Everything begins with %033[. (This is one of the things that was killing me initially. I kept trying to pick out a closing ]. Well, there isn't one!) And everything (except that K, which I will address last) ends with m.  Lets decipher that…

There's no end to the references you can find for terminal control codes if you Google for them. Here's a handy one that I find clearly laid out and understandable:

Scrolling to the bottom, we have the display codes.  I've pasted them here for convenience:

Set Attribute Mode

Display Attribute

0 Reset ALL
1 Bright
2 Dim
4 Underscore
5 Blink
7 Reverse
8 Hidden
Foreground Colors

30 Black
31 Red
32 Green
33 Yellow
34 Blue
35 Magenta
36 Cyan
37 White
Background Colors

40 Black
41 Red
42 Green
43 Yellow
44 Blue
45 Magenta
46 Cyan
47 White

And based on this format, now we can see that %033 is an escape sequence equivalent to <ESC> and m is, well... m!

So the way this works is that when we set a display attribute, foreground color, and background color; they will remain in effect until we change them or reset everything. Stepping through my example prompt (and using a very light gray background in lieu of white for readbility's sake), we have —

 1;31;40 > bold red foreground on black background 
   32    > green foreground 
   36    > cyan foreground 
   34    > blue foreground 
   36    > cyan foreground 
   33    > yellow foreground 
 K       > ...erase to the end of the current line      
 0       > reset all attributes 
 1;33    > bold yellow foreground 
 0       > reset all attributes 

Or, more specifically...

 ➤ psql://database_user@hostname:port/database         


Okay, almost there... But you might be scratching your head at this point. These colors look not even remotely similar to those in my screenshot posted above.

Well, that's where the terminal emulator you're using and the color scheme (theme) you've chosen come in. Your theme will determine what colors the emulator you're using will substitute for the standard black, blue, red, green, and so on.

In my case, I am using iTerm2 on OS X with the 'arthur' color scheme.  You might prefer something such as Solarized or Tango.  But that's is where the fun and creativity of customizing something to your personal taste comes in.  And I hope you now have enough of an understanding of how colors are applied to the psql prompt to go forth and roll your own.