---
title: An Explained psqlrc
teaser:
tags: web,postgresql
author: Mike Burns
published_on: 2014-07-24
---
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 `\set` and `\pset`. `\pset`
is for changing the output format --- HTML, pager, field separator, and so on --- while `\set` is for
everything else.
## `ON_ERROR_ROLLBACK`
The `ON_ERROR_ROLLBACK` settings affects how psql(1) handles errors. The default
value is `off`.
When this setting is `on`, errors are effectively ignored at all times. So if
you have this script, `slint.sql`:
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 `David
Pajo`.
When it is set to `off`, the default, then you get nothing: no `members` table
and no `Brian McMahan`. It either all works or it doesn't, just like a
transaction should.
There is a third value: `interactive`. Under `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
## `COMP_KEYWORD_CASE`
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][tab-completion],
and psql(1) doesn't disappoint. However, there's a question of which case it
should use to complete keywords.
[tab-completion]: https://thoughtbot.com/blog/vim-you-complete-me
The straight-forward thing to do is to set it to `lower` or `upper`.
But even fancier are `preserve-lower` and `preserve-upper`, with
`preserve-upper` as the default. These preserve whatever case you were using,
falling back to lower (or upper). For example:
There, `up` was completed to `update` and `S` was completed to `SET`,
preserving the case as the user typed it; `n` was completed to `name`,
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.
## `HISTFILE`
Like any good shell, psql(1) will save the commands you have entered so you can
run them again (it's full [Readline][readline]; try a ^R some time).
By default it stores the history in `~/.psql_history`, but we can do better than
that.
[readline]: http://jan.tomka.name/sites/default/files/readline-commands.html
To start, let's introduce another psql(1) command: `\echo`
bands=# \echo hello
hello
bands=# \echo :DBNAME
bands
The variable `:DBNAME` is automatically set to the name of the database and
available to all psql(1) commands. There are other pre-set variables like
`:PORT`, `:HOST`, `:USER`, `: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 `desserts`
table won't make sense in the `zoology` database, for example), you can set
that up:
\set HISTFILE ~/.psql_history- :DBNAME
You can combine these as much as you please, such as:
\set HISTFILE ~/.psql_history- :USER - :HOST - :PORT - :DBNAME
## `pager`
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 `off`.
You can also change the pager itself by setting the `PAGER` environment
variable. For example:
export PAGER="/usr/local/bin/gvim -f -R -"
This will use gvim(1) as your pager.
## `null`
By default `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)
## And more
You can find all of this and more in the psql(1) manpage or in [the official
PostgreSQL Web documentation][psql]. We have also [written previously on this
topic][improving-command-line-psql].
As you read the documentation we'd love to see your favorite settings as pull
requests against the [`.psqlrc`][our-psqlrc] in [our dotfiles][dotfiles].
[psql]: http://www.postgresql.org/docs/9.3/static/app-psql.html
[improving-command-line-psql]: https://thoughtbot.com/blog/improving-the-command-line-postgres-experience
[dotfiles]: https://github.com/thoughtbot/dotfiles
[our-psqlrc]: https://github.com/thoughtbot/dotfiles/blob/master/psqlrc