Understanding the ~/.psqlrc configuration file, its options, and reading others’ ~/.psqlrc files makes working with command-line Postgres more enjoyable.

By default, the prompt is a little blah:

$ psql my_database my_database=#

Let’s jazz it up.

There are a lot of options for changing the prompt. You can add colors, domain names, the name of the current database, and more.

\set PROMPT1 '%[%033[1m%]%M %n@%/%R%[%033[0m%]%# '

The %[..%] sets the default prompt color to a bold black.

sets the default prompt color to a bold black. %M is “The full host name (with domain name) of the database server, or [local] if the connection is over a Unix domain socket”.

is “The full host name (with domain name) of the database server, or [local] if the connection is over a Unix domain socket”. %n is the database user name.

is the database user name. %/ is the database name.

is the database name. %R is “normally = , but ^ if in single-line mode, and ! if the session is disconnected from the database”. It’s nice to see when you’re disconnected.

is “normally , but if in single-line mode, and if the session is disconnected from the database”. It’s nice to see when you’re disconnected. The final %[...%] resets the color to non-bold black.

resets the color to non-bold black. %# is # if you’re a superuser, otherwise > .

Here’s how it looks on a local database:

Being the smart cookie you are, you’ve probably inferred that a setting called PROMPT1 implies that there’s a PROMPT2 . And you’re right! You go, Glen Coco. PROMPT2 for you:

\set PROMPT2 '[more] %R > '

PROMPT2 is used when psql is waiting for more input, like when you type SELECT * FROM then hit enter - you haven’t typed a semicolon, so psql patiently displays PROMPT2 and waits.

Here’s the rundown:

[more] is the literal string [more] .

is the literal string . %R in PROMPT2 has a different meaning than in PROMPT1 - “in prompt 2 the sequence is replaced by - , \* , a single quote, a double quote, or a dollar sign, depending on whether psql expects more input because the command wasn’t terminated yet, because you are inside a /\* ... \*/ comment, or because you are inside a quoted or dollar-escaped string.”

Here’s a contrived example:

[local] gabe@my_database=# SELECT [more] - > ' [more] ' > name [more] ' > ' [more] - > FROM users;

Nice.

Now your prompt is spiffed up, but your ~/.psqlrc can bring still more joy to your life. Here are some options I set, with comments:

-- By default, NULL displays as an empty space. Is it actually an empty -- string, or is it null? This makes that distinction visible. \pset null '[NULL]' -- Use table format (with headers across the top) by default, but switch to -- expanded table format when there's a lot of data, which makes it much -- easier to read. \x auto -- Verbose error reports. \set VERBOSITY verbose -- Use a separate history file per-database. \set HISTFILE ~/.psql_history- :DBNAME -- If a command is run more than once in a row, only store it once in the -- history. \set HISTCONTROL ignoredups -- Autocomplete keywords (like SELECT) in upper-case, even if you started -- typing them in lower case. \set COMP_KEYWORD_CASE upper

If you found this useful, I recommend: