Improving the Command-Line Postgres Experience
~/.psqlrc configuration file, its options, and
~/.psqlrc files makes working with command-line Postgres more
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%]%# '
%[..%]sets the default prompt color to a bold black.
%Mis "The full host name (with domain name) of the database server, or [local] if the connection is over a Unix domain socket".
%nis the database user name.
%/is the database name.
^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.
#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
PROMPT2 and waits.
Here's the rundown:
[more]is the literal string
PROMPT2has 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
psqlexpects 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;
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
If you found this useful, I recommend: