Let’s walk through my short psqlrc(5) to see what I’ve set, and to inspire you to find your own configuration that fits into your workflow. Here is my complete psqlrc:
\set ON_ERROR_ROLLBACK interactive \set COMP_KEYWORD_CASE upper \set HISTFILE ~/.psql/history- :DBNAME \pset pager off \pset null '(null)'
PostgreSQL’s shell, psql(1), can be configured using
is for changing the output format — HTML, pager, field separator, and so on — while
\set is for
ON_ERROR_ROLLBACK settings affects how psql(1) handles errors. The default
When this setting is
on, errors are effectively ignored at all times. So if
you have this script,
BEGIN; CREATE TABLE members (id SERIAL, name TEXT); INSERT INTO member (name) VALUES ('David Pajo'); INSERT INTO members (name) VALUES ('Brian McMahan'); COMMIT;
And run it from the command line:
psql -f slint.sql
You would end up with a
members table with
Brian McMahan but without
When it is set to
off, the default, then you get nothing: no
Brian McMahan. It either all works or it doesn’t, just like a
There is a third value:
interactive, the above command
in which statements are piped into psql(1) non-interactively is treated like
off, but if you type them into the interactive prompt it is treated like
on. This gives you a chance to fix things without starting over:
$ psql bands=# BEGIN; BEGIN bands=# CREATE TABLE members (id SERIAL, name TEXT); CREATE TABLE bands=# INSERT INTO member (name) VALUES ('David Pajo'); ERROR: relation "member" does not exist LINE 1: INSERT INTO member (name) VALUES ('David Pajo'); bands=# INSERT INTO members (name) VALUES ('David Pajo'); INSERT 0 1 bands=# INSERT INTO members (name) VALUES ('Brian McMahan'); INSERT 0 1 bands=# COMMIT; COMMIT
Some people format their SQL with uppercase keywords; others go downcase. Some mix and match depending on their mood. psql(1) handles that!
Possibly the greatest feature of any shell is tab completion, and psql(1) doesn’t disappoint. However, there’s a question of which case it should use to complete keywords.
The straight-forward thing to do is to set it to
But even fancier are
preserve-upper as the default. These preserve whatever case you were using,
falling back to lower (or upper). For example:
up was completed to
S was completed to
preserving the case as the user typed it;
n was completed to
preserving the case of the object in the database; and the space after order
was completed to
BY, favoring uppercase when the user has typed nothing.
Like any good shell, psql(1) will save the commands you have entered so you can
run them again (it’s full Readline; try a ^R some time).
By default it stores the history in
~/.psql_history, but we can do better than
To start, let’s introduce another psql(1) command:
bands=# \echo hello hello bands=# \echo :DBNAME bands
:DBNAME is automatically set to the name of the database and
available to all psql(1) commands. There are other pre-set variables like
:ENCODING, and so on, but we’re going to use
:DBNAME to start.
It just so happens that psql(1) will concatenate strings for you, so if you
want different history for each database (the queries against the
table won’t make sense in the
zoology database, for example), you can set
\set HISTFILE ~/.psql_history- :DBNAME
You can combine these as much as you please, such as:
\set HISTFILE ~/.psql_history- :USER - :HOST - :PORT - :DBNAME
The pager is the program that paginates text. The classic is more(1), and the improvement is less(1). Puns.
The default value for the
pager setting is
on which — unlike the name
suggests — only uses the pager sometimes. A few lines are shown without a
pager, but 25 or more lines invoke pagination. (Specifically, if the text would
scroll off the screen, it invokes the pager.)
To always have a pager, use the value
always. To never use the pager —
useful inside a terminal multiplexer or terminal emulator with good scrolling
— use the value
You can also change the pager itself by setting the
variable. For example:
export PAGER="/usr/local/bin/gvim -f -R -"
This will use gvim(1) as your pager.
NULL values show as blank spaces. Also by default the empty string
shows as a blank space.
bands=# INSERT INTO members (name) VALUES (''); INSERT 0 1 bands=# INSERT INTO members (name) VALUES (NULL); INSERT 0 1 bands=# SELECT * FROM members; id | name ----+--------------- 1 | David Pajo 2 | Brian McMahan 3 | 4 | (4 rows)
To better distinguish
NULL values from empty strings, you can have psql(1)
show anything you want instead:
bands=# \pset null '(null)' Null display is "(null)". bands=# SELECT * FROM members; id | name ----+--------------- 1 | David Pajo 2 | Brian McMahan 3 | 4 | (null) (4 rows)