Colors in the psql prompt

Question

I get an trash after the prompt when I flick through the history when I add colors to my prompt.

I have tried the proposed color prompt command from the postgres doc:

\set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%]%# '

I have also tried a lot of other prompt examples that i found with google but with the same result.

So after setting the prompt I have a yellow prompt something like this:

ulf@mydb=#

I then run the query:

ulf@mydb=# select * from country;

Then I flick through the history with ↑↑↓. This would bring me the select-line ones more, but instead I get:

ulf@mydb=# \set PROMPT1 'select * from country;

Note the \set... before the selectwhich is a remnant from the initial promt-setting.

I also get similar result if I put the \set PROMPT1-command in .pslqrc

I get the same result in both xterm and gnome-terminal. I have my bash-prompt colored in a similar way but I have no problems with that prompt.

What have I done wrong?

Amari

On Mac OS, I experienced this problem (along with only the final color code being used) when psql was compiled and linked against whatever readline resolved to by default. Installing readline through Homebrew and manually compiling psql, passing the resulting paths as env CFLAGS='-I ...' LDFLAGS='-L ...' ./configure, gave me a psql that worked as expected, both for colors and navigation. – Journalize

Answer 1

Note: I have yet to find the root cause of why this is necessary sometimes, however, this should fix it for you.

There are two characters to indicate start/stop of non-printable characters in readline/libedit (?), 0x01 and 0x02.

So, if you add these before and after the non-printable sequences in your PROMPT1, it should work. The result in your case would be:

\set PROMPT1 '%001%[%033[1;33;40m%]%002%n@%/%R%001%[%033[0m%]%002%# '

Update: It would seem this should be handled in psql though, somewhere they must get lost some how. Maybe USE_READLINE isn't defined? See: Line 286 in src/bin/psql/prompt.c

Spikenard

I've upgraded my Postgres installation to version 9.3.5, and the issue seems to have been fixed; in fact, wrapping color codes in %001 and %002% now seems to exact problem that @Amari posted about. Your solution did work, however, on an older version. Maybe it's just a peculiarity of my setup. – Rickey

I recently had this problem in Postgres 10, and it turned out my readline config (~/.inputrc) was to blame. I had vi-ins-mode-string set to \033[0m and vi-cmd-mode-string to \033[7m — all wrapped in $if psql … $endif. (I do this to make the (first part of) my prompt inverse in vi command mode when using psql.) I fixed the issue by wrapping these values, i.e vi-ins-mode-string became \001\033[0m\002, and vi-cmd-mode-string is now set to \001\033[7m\002. (For the record, my psql PROMPT1 is '%[%033[1m%]%m%[%033[0m%]:%n%@%[%033[0;32m%]%~%[%033[0m%]%x%R%# '.) – Bookout

I still have the issue with postgresql version 11.0. Not even the %001 and %002hack solves it. – Comparator

It seems the problem is only noticeable if the length of the result prompt string (with formats and expansions applied) is smaller than the length of the original string set (including escapes and format codes), when you passed through a multiline command in history – Comparator

Answer 2

\set PROMPT1 '%001%[%033[1;32;40m%][%`hostname -i`] %002%n@%/%R%001%[%033[0m%]%002%# '

Selfassurance

Answer 3

psql uses readline library (unless you specify -n, --no-readline option). Readline control sequences provide colorization capabilites.

For instance, I want to see the following prompt: %R%n@%/%x%# . Description of percent-sequences you may find in the documentation. Here:

Sequence	Meaning
%n	username
@	as is
%/	current database
%R	input state
%x	transaction status
%#	prompt character
	as is

\set PROMPT1 '%R%n@%/%x%# '
\set PROMPT2 '%R%# '

psql now prints commands like:

=myuser@mydb# select
-# 1;

Now we add colors. It is good to start with escape-sequences from Bash:

'%R\[\e[0;32m\]%n\[\e[0m\]@\[\e[0;33m\]%/\[\e[0;31m\]%x\[\e[0m\]%# '
   ^^^^^^^^^^^^  ^^^^^^^^^ ^^^^^^^^^^^^  ^^^^^^^^^^^^  ^^^^^^^^^
   green         reset     yellow        red           reset

Then replace sequences with their octal equivalent:

Bash	PSQL
\[	%001
\]	%002
\e	%033

Step by step:

• replace \[ with %001

'%R%001\e[0;32m\]%n%001\e[0m\]@%001\e[0;33m\]%/%001\e[0;31m\]%x%001\e[0m\]%# '
   \[              \[          \[              \[              \[
   ^^^^^^^^^^^^^^  ^^^^^^^^^^^ ^^^^^^^^^^^^^^  ^^^^^^^^^^^^^^  ^^^^^^^^^^^
   green           reset       yellow          red             reset

• replace ][ with %002

'%R%001\e[0;32m%002%n%001\e[0m%002@%001\e[0;33m%002%/%001\e[0;31m%002%x%001\e[0m%002%# '
   \[          \]    \[       \]   \[          \]    \[          \]    \[       \]
   ^^^^^^^^^^^^^^^^  ^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^  ^^^^^^^^^^^^^^^^  ^^^^^^^^^^^^^
   green             reset         yellow            red               reset

• replace \e with %033

'%R%001%033[0;32m%002%n%001%033[0m%002@%001%033[0;33m%002%/%001%033[0;31m%002%x%001%033[0m%002%# '
   \[  \e        \]    \[  \e     \]   \[  \e        \]    \[  \e        \]    \[  \e     \]
   ^^^^^^^^^^^^^^^^^^  ^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^^  ^^^^^^^^^^^^^^^^^^  ^^^^^^^^^^^^^^^
   green               reset           yellow              red                 reset

Now we have got color prompt:

\set PROMPT1 '%R%001%033[0;32m%002%n%001%033[0m%002@%001%033[0;33m%002%/%001%033[0;31m%002%x%001%033[0m%002%# '
\set PROMPT2 :PROMPT1

It is pretty easy to make mistake if you start colorization directly with %xxx characters. Step by step process will help to avoid errors. In most cases unbalanced %001 and %002 characters can lead with incorrect visual representation of prompt when you scroll history: extra symbols appeared by you can't modify them and they don't affect you input.

At last, you may want to write your commands into ~/.psqlrc.

$ cat ~/.psqlrc
\set PROMPT1 '%R%001%033[0;32m%002%n%001%033[0m%002@%001%033[0;33m%002%/%001%033[0;31m%002%x%001%033[0m%002%# '
\set PROMPT2 '%001%[%033[1;33;40m%]%002%R%001%[%033[0m%]%002%# '

Damson

Answer 4

\set PROMPT1 '%[%033[1;93;1m%]%`date +%H:%M:%S`%[%033[0m%] %[%033[96m%]%n@%/%R %# %[%033[0m%]'

I found that you need to prefix with %[ and end with %] If you do that, the problem goes away, but you have to do it for EACH escape sequence

Villiers