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.

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%# '