psql coloured prompt misbehaving

In theory, there is a possibility to use coloured prompts in psql. I decided (not the first time :) that I need them, so I thought I would give them a try. So I have in my .psqlrc the following:

\set prompt_colour '%[%033[1;33m%]'
\set reset_colour '%[%033[0m%]'

\set PROMPT1 :prompt_colour'%n@%/%R%#%':reset_colour

This is very nice, I have a fancy yellow prompt. Everything is good until I don't start walking back in the command history and hit a long enough command (in my experiments, 37 is OK, 38 is too long and multiline commands of any length do the same) - when I do, the Home button does not go back to the end of the row, it stops (in this case) 11 characters to the right. If now I try to edit the command, it gets edited in the wrong positions.

I try to depict this:

# SELECT 123456789012345678 FROM a_table;
[here is an error, of course, but it doesn't count]
# SELECT 123456789012345678 FROM a_table;
[ cursor is  ^ here after pressing Home ]
[ hitting Enter gives the error ]
ERROR:  syntax error at or near "ELECT"
LINE 1: ELECT 123456789012345678 FROM a_table;

(From the documentation it looks like this version should also work but here the control characters aren't parsed, so I have no coloured prompt by the two expressions echoed:

\set PROMPT1 "%:prompt_colour:%n@%/%R%#%:reset_colour:"

but this results in

%[%033[1;33m%]postgres@postgres=#%[%033[0m%]

)

So does anybody has any idea how to do this properly?

Somehow I managed to omit the versions so here they are:

• ubuntu 12.10

• psql 9.1.9 (as far as I know, it is installed from a standard ubuntu package)

• both libreadline5 and 6 are installed, also libedit2

My findings:

Following Peter's advice, I tried modifying the pg_wrapper script to link against libreadline.so.6 - in itself it works but the error is still there. The same with libreadline.so.5 (which is the default one, I think). With libeditline.so.0 the situation is much worse.

I have experience this exact same thing in the past. It usually happens because you have linked psql against an inferior readline-like library. If you are using libedit, use libreadline instead. If you are using libreadline, use a different one. Details depend on your operating system and method of installation.

StackExchange Database Administrators Q#42353, answer score: 3