An Explained psqlrc

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, 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 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; try a ^R some time). By default it stores the history in ~/.psql_history, but we can do better than that.

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. We have also written previously on this topic.

As you read the documentation we’d love to see your favorite settings as pull requests against the .psqlrc in our dotfiles.