PostgreSQL – Understanding psql Prompts

postgresqlpsql

What is the difference between postgres=# and postgres-#? I tried googling it and looking on the official site but could not get a hit for the symbols.

Best Answer

psql has three different prompts, source:

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 entry, for example because the command was not terminated with a semicolon or a quote was not closed.

  • Prompt 3 is issued when you are running an SQL COPY FROM STDIN command and you need to type in a row value on the terminal.

You can see the defaults with \set in PSQL, or looking here in the source code

#define DEFAULT_PROMPT1 "%/%R%# "
#define DEFAULT_PROMPT2 "%/%R%# "
#define DEFAULT_PROMPT3 ">> "

These are all format strings. They render different in different prompt levels, from the source above describing %R,

%R

  • In prompt 1 normally =, but @ if the session is in an inactive branch of a conditional block, or ^ if in single-line mode, or ! if the session is disconnected from the database (which can happen if \connect fails).
  • In prompt 2 %R is replaced by a character that depends on why psql expects more input: - if the command simply wasn't terminated yet, but * if there is an unfinished /* ... */ comment, a single quote if there is an unfinished quoted string, a double quote if there is an unfinished quoted identifier, a dollar sign if there is an unfinished dollar-quoted string, or ( if there is an unmatched left parenthesis.
  • In prompt 3 %R doesn't produce anything.

So there ya have it, %R changes based on the PROMPT level.

Related Question