Improving the Command-Line Postgres Experience

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

Changing the prompt

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.
• %M 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 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.
• The final %[...%] resets the color to non-bold black.
• %# is # if you’re a superuser, otherwise >.

Here’s how it looks on a local database:

Changing the prompt, again

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].
• %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.

There’s more to life than prompts

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

What’s next

If you found this useful, I recommend:

• The official ~/.psqlrc documentation
• Craig Kerstiens’ talk, How I Work with Postgres
• Harold Giménez’s ~/.psqlrc