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
inPROMPT2
has a different meaning than inPROMPT1
- “in prompt 2 the sequence is replaced by-
,\*
, a single quote, a double quote, or a dollar sign, depending on whetherpsql
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