Customize the psql Prompt and .psqlrc
Chat2DB TeamThe default psql prompt is postgres=#. It tells you the database name and whether you are a superuser, and nothing else. That is not much when you have five terminals open, one of them on production, and you are about to run a DELETE. Which server is this? Am I inside a transaction? Did the last statement fail and leave the transaction aborted? Is this the primary or a replica?
All of that can live in the prompt. This guide explains how psql prompts work, every escape you are likely to use, how to add colors without breaking line editing, the two new escapes in psql 19 (%S for search_path and %i for primary or standby), and a complete .psqlrc you can copy. Every prompt shown here was captured from real psql 19 RC2 and 18.7 sessions.
PROMPT1, PROMPT2 and PROMPT3
psql has three prompt variables:
- PROMPT1 is the normal prompt, shown when psql waits for a new command.
- PROMPT2 is shown when the command is not finished yet: no semicolon, an open quote, an open parenthesis.
- PROMPT3 is shown while you type data for
COPY ... FROM STDIN.
The default for both PROMPT1 and PROMPT2 is '%/%R%x%# ', which renders as postgres=# on the first line and postgres-# on continuation lines. You change a prompt with \set:
postgres=# \set PROMPT1 '%n@%m:%>/%/%R%x%# '
postgres@[local]:5432/postgres=#The Escapes
Everything in the prompt is printed literally except % followed by a character. These are the escapes, with example values from a local test server whose socket directory was not the default:
| Escape | Meaning | Example |
|---|---|---|
%n | session user name | postgres |
%/ | current database | postgres |
%~ | database, or ~ if it is your default database | ~ |
%m | server host name up to the first dot, [local] for a Unix socket | [local] |
%M | full host name, [local:/dir] for a socket outside the default directory | [local:/tmp/pg] |
%> | port | 5519 |
%# | # for a superuser, > otherwise | # |
%R | in PROMPT1: = normally, ^ in single-line mode, ! when disconnected, @ in an inactive \if branch | = |
%x | transaction status: empty, * in a transaction, ! in a failed transaction | * |
%p | backend process ID | 99672 |
%s | service name from pg_service.conf | |
%l | line number inside the current statement | 1 |
%P | pipeline status: off, on or abort (psql 18+) | off |
%S | current search_path (psql 19+) | "$user", public |
%i | primary or standby (psql 19+) | primary |
%:name: | value of a psql variable | 19rc2 for %:VERSION_NAME: |
%`cmd` | output of a shell command | |
%033 | character with that octal code (used for colors) | |
%[ ... %] | marks invisible characters for readline | |
%w | spaces as wide as the last PROMPT1 (for PROMPT2) | |
%% | a literal % | % |
%R in PROMPT2
In PROMPT2, %R tells you why psql wants more input. That makes it the most useful thing on a continuation line:
| Character | Meaning |
|---|---|
- | the statement is not terminated yet |
' | inside a single-quoted string |
" | inside a quoted identifier |
$ | inside a dollar-quoted string |
( | an unmatched left parenthesis |
* | inside a /* ... */ comment |
In our test, SELECT ' followed by Enter gave the PROMPT2 ', and SELECT ( gave (. When psql seems to ignore your semicolon, look at that character. You have probably left a quote open.
%x Is the Most Underrated Escape
%x turns the prompt into a transaction indicator. With the default prompt:
postgres=# BEGIN;
postgres=*# SELECT 1/0;
ERROR: division by zero
postgres=!# ROLLBACK;
postgres=#=*# means an open transaction, so nothing is committed yet. =!# means the transaction has failed, and every further statement will be rejected with current transaction is aborted, commands ignored until end of transaction block until you roll back. Since %x is in the default prompt, you already have this indicator. Just keep it when you customize.
New in psql 19: %S and %i
%S shows the current search_path. It answers "which schema will an unqualified table name resolve to?". It changes immediately when you run SET search_path:
postgres=# \set PROMPT1 '%/ [%S]%R%x%# '
postgres ["$user", public]=# SET search_path = app, "My Schema", public;
postgres [app, "My Schema", public]=#%i shows primary or standby, based on whether the server is in hot standby. If you connect to replicas regularly, this is the escape to add. You will never again run a long report on the primary by mistake, or wonder why an UPDATE fails with cannot execute UPDATE in a read-only transaction. In our tests it showed standby on a streaming replica. After SELECT pg_promote(); in that same session, the next prompt switched to primary.
Both escapes rely on values that the server reports to the client automatically. That has version consequences:
| psql | Server | %S shows | %i shows |
|---|---|---|---|
| 19 | 18 or 19 | the search_path | primary/standby |
| 19 | 14 to 17 | ? | primary/standby |
| 19 | 13 or older | ? | ? |
| 18 or older | any | a plain S | a plain i |
We confirmed the first two rows and the last one directly. psql 19 against a PostgreSQL 17 server printed ? for %S, and psql 18.7 printed the letters S and i. Unknown escapes are not an error in psql: the % is dropped and the letter is printed. So a prompt written for psql 19 still works in older clients, it just looks odd.
Adding Colors the Right Way
Terminal colors are ANSI escape sequences, such as ESC[1;32m for bold green and ESC[0m for reset. In a prompt you write the ESC character as %033 (octal 33). The critical part is wrapping each sequence in %[ and %]:
\set PROMPT1 '%[%033[1;32m%]%n@%m%[%033[0m%]:%[%033[1;34m%]%/%[%033[0m%]%R%x%# 'This gives a green user@host, a blue database name, and default color for the rest. Without %[ %], readline counts the invisible escape characters as part of the prompt width. Long commands then wrap at the wrong column, and recalled history lines draw on top of each other. The %[ and %] markers tell readline that the enclosed text takes no space on screen.
Common color codes:
| Code | Color | Code | Color |
|---|---|---|---|
31 | red | 35 | magenta |
32 | green | 36 | cyan |
33 | yellow | 90 | gray |
34 | blue | 1; prefix | bold |
A popular safety trick is a red prompt for production, for example with a separate psqlrc on the production jump hosts.
PROMPT2 Ideas
Two good options for continuation lines:
\set PROMPT2 '%R%x%# 'This shows only the reason character. Or align continuation lines under the first line with %w:
\set PROMPT2 '%w'%w prints as many spaces as the visible width of the last PROMPT1, so multi-line statements line up. The width calculation skips text inside %[ %], which is one more reason to wrap color codes.
Making It Permanent: .psqlrc
psql runs ~/.psqlrc at startup (on Windows, %APPDATA%\postgresql\psqlrc.conf) unless you start it with -X. A practical file:
\set QUIET 1
\set PROMPT1 '%[%033[1;32m%]%n@%m%[%033[0m%]:%[%033[1;34m%]%/%[%033[0m%]%R%x%# '
\set PROMPT2 '%R%x%# '
\pset null '(null)'
\x auto
\timing on
\set ON_ERROR_ROLLBACK interactive
\set COMP_KEYWORD_CASE upper
\set HISTFILE ~/.psql_history- :DBNAME
\set HISTCONTROL ignoredups
\set QUIET 0What each line does:
QUIET 1at the start andQUIET 0at the end hide the confirmation messages the other commands would print.\pset null '(null)'makes NULL visible. By default NULL and an empty string look identical.\x autoswitches to expanded output only when rows are too wide for the terminal.\timing onprints the run time after every statement.ON_ERROR_ROLLBACK interactivewraps each statement in a savepoint in interactive sessions, so a typo does not abort your whole transaction. It does not apply to scripts.COMP_KEYWORD_CASE uppermakes tab completion write keywords in upper case.HISTFILE ~/.psql_history- :DBNAMEkeeps a separate command history per database. In our test\echo :HISTFILEprinted~/.psql_history-postgres.
Version-Specific Settings
psql 19 adds \pset display_true and \pset display_false, which control how booleans print:
\pset display_true 'yes'
\pset display_false 'no'With them, SELECT true prints yes instead of t. Older clients do not know the option. When we loaded the same file with psql 18.7, it printed \pset: unknown option: display_true and carried on. Two clean ways to avoid that:
- Version-specific file names. psql looks for
~/.psqlrc-19rc2, then~/.psqlrc-19, then~/.psqlrc, and reads the most specific one that exists instead of the generic file. Put\i ~/.psqlrcat the top of~/.psqlrc-19to share the common settings. - A conditional block using the
VERSION_NUMvariable:
SELECT :VERSION_NUM >= 190000 AS psql19 \gset
\if :psql19
\pset display_true 'yes'
\pset display_false 'no'
\endifHere the comparison runs on the server, so it needs a connection, but it lets one file serve every version. Both psql 18.7 and 19 RC2 loaded it without errors in our test.
Quoting Rules in \set
The value after \set PROMPT1 is a psql argument, not SQL. Inside single quotes, psql processes \n (newline), \t (tab), \\ (backslash) and octal or hex escapes, and a single quote is written as two quotes (''). This lets you build a two-line prompt:
\set PROMPT1 '%n@%M:%> %/ [%S]\n%x%# 'There are now two different escape layers: \n is handled when psql reads the \set command, and %033 is handled every time the prompt is drawn. Mixing them up is a common reason for prompts that print literal % signs or backslashes.
Build and Preview Without Trial and Error
Tuning a prompt by trial and error means restarting psql over and over. The free psql Prompt Builder (opens in a new tab) renders PROMPT1 and PROMPT2 in color for an idle session, an open transaction and a failed transaction. You can switch the psql and server versions to see how %S and %i degrade, it warns about color codes that are not wrapped in %[ %], and it writes the escaped \set lines for your .psqlrc. Its renderer follows psql's prompt.c and was compared byte by byte with real psql 19 and 18 output. For more on everyday psql commands, see the psql meta-commands cheat sheet.
If you spend more time in a GUI than in a terminal, Chat2DB (opens in a new tab) shows the active connection, database and schema in the editor tabs, which covers the same "where am I?" problem.
Summary
The psql prompt is set with \set PROMPT1 (and PROMPT2 for continuation lines) using % escapes. Keep %x for the transaction indicator (* open, ! failed), add %n@%m and %> to see where you are connected, and use %R in PROMPT2 to see why psql wants more input. psql 19 adds %S for the search_path and %i for primary or standby. Older clients print the bare letter, and servers before 18 (for %S) or 14 (for %i) show ?. Wrap every color code in %[ %], and put the result in ~/.psqlrc, with version-specific options in ~/.psqlrc-19 or behind an \if.
