CODEWITHSUNDEEP
postgresqlcommand-linecustomization
Ian Mackinnon
Ian Mackinnon

Reputation: 14238

How to customise the PostgreSQL/psql prompt?

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.

Upvotes: 17

Views: 10288

Answers (3)

Rinat
Rinat

Reputation: 648

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.

Upvotes: 0

Brad Parks
Brad Parks

Reputation: 72001

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

Upvotes: 6

Steve K
Steve K

Reputation: 19586

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

Upvotes: 29

Related Questions