How to customise the PostgreSQL/psql prompt?

Question

How can I customize the prompt in the PostgreSQL command line tool psql (ideally in a per-user start-up script)?

In particular, I'd like to be able to change it while still including the character that indicates whether the command is multi-line (eg. =, -, ', etc.).

I'm running Ubuntu 10.04 (Lucid), PostgreSQL 8.4.4.

Lassiter

Answer 1

You can certainly customize the prompt.

From the documentation:

The prompts psql issues can be customized to your preference. The three variables PROMPT1, PROMPT2, and PROMPT3 contain strings and special escape sequences that describe the appearance of the prompt. Prompt 1 is the normal prompt that is issued when psql requests a new command. Prompt 2 is issued when more input is expected during command input because the command was not terminated with a semicolon or a quote was not closed. Prompt 3 is issued when you run an SQL COPY command and you are expected to type in the row values on the terminal.

If you want to set the prompt on a per user basis, you can add the \set commands to the user's .psqlrc file.

So, your $HOME/.psqlrc would be something like this:

\set PROMPT1 '(%n@%M:%>) %`date +%H:%M:%S` [%/] \n%x%# '

Britishism

Thanks! Looks like the multi-line indication character I mentioned is %R. – Lassiter

Answer 2

You can also customize the prompt at the command line, if you can't setup a $HOME/.psqlrc file (e.g shelling into a container you don't control)

$ PROMPT1="PROMPT1='%[%033[1;31;40m%]%/%R%[%033[0m%]%# '" 
$ psql -v $PROMPT1

Or if you are connecting to a container via kubectl, you can do something like so:

  POD=$(getPodThatHasPostgresCli)
  PSQL_PATH="./pgsql/bin/"
  PROMPT1="PROMPT1='%[%033[1;31;40m%]%/%R%[%033[0m%]%# '" 
  CMD="export PGPASSWORD='$PGPASS'; $PSQL_PATH/psql -v $PROMPT1 $PGURL -U $PGUSER $*"
  
  if ! kubectl exec -it "$POD" -- /bin/bash -c "$CMD"
  then
    echo "failed to connect to psql in pod"
    exit 1;
  fi

Kazmirci

Answer 3

I've made psql colored prompt for primary and standby PostgreSQL server. There's a lot of code, so you can see it my GitHub: description, source code.

Sago