A better PostgreSQL CLI experience with few psqlrc tweaks

From psql man page:

psql attempts to read and execute commands from the system-wide startup file (psqlrc) and then the user's personal startup file (~/.psqlrc), after connecting to the database but before accepting normal commands. These files can be used to set up the client and/or the server to taste, typically with \set and SET commands.

Like with any dotfiles we can utilize psqlrc to customize psql behavior, look and feel. First, let’s create psqlrc. We have three options:

Create system-wide psqlrc

It should be located inside PostgreSQL’s system configuration directory, which could be found using pg_config tool:

$ pg_config --sysconfdir
/etc/postgresql-common

Location may vary depending on your PostgreSQL installation. I’m running it at Ubuntu 14.04 and configuration files are located at /etc/postgresql-common.

User’s personal psqlrc

Just create it in your home directory:

$ touch ~/.psqlrc

Version-specific psqlrc

Man pages are cool:

Both the system-wide startup file and the user's personal startup file can be made psql-version-specific by appending a dash and the PostgreSQL major or minor release number to the file name, for example ~/.psqlrc-9.2 or ~/.psqlrc-9.2.5. The most specific version-matching file will be read in preference to a non-version-specific file.

Depending on specific needs and preferences, choose location for psqlrc that suits your needs most. Let’s move forward for the actual configuration.

Actual configuration

Here is my psqlrc with comments for the settings:

\set QUIET 1

-- Errors are ignored in interactive sessions,
-- and not when reading script files.
\set ON_ERROR_ROLLBACK interactive

-- To have all queries display query times.
\timing

-- Verbose error reports.
\set VERBOSITY verbose

-- 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

-- 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

-- 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)'

\unset QUIET

Let’s take a look at some of the options in more details.

QUIET flag

At startup psql executes commands from psqlrc, which creates unnecessary output. In order to hide it, we set QUIET flag at the top of psqlrc and unset it at the bottom of the file.

ON_ERROR_ROLLBACK

Allows statements in a transaction to error without affecting the entire transaction. Valid values are on, interactive or off.

From the manual:

When set to on, if a statement in a transaction block generates an error, the error is ignored and the transaction continues.

When set to interactive, such errors are only ignored in interactive sessions, and not when reading script files.

When unset or set to off, a statement in a transaction block that generates an error aborts the entire transaction.

The error rollback mode works by issuing an implicit SAVEPOINT for you, just before each command that is in a transaction block, and then rolling back to the savepoint if the command fails.

I’m using the interactive mode because this gives a chance to fix things while in interactive session without starting over.

\timing

Conveniently shows how long each SQL statement takes, in milliseconds:

test=> SELECT * FROM weather
       WHERE prpc IS NOT NULL;
     city      | temp_lo | temp_hi | prpc |    date
---------------+---------+---------+------+------------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27
 San Francisco |      41 |      55 |    0 | 1994-11-29
(2 rows)

Time: 0.652 ms

Wrapping Up

Given psqlrc contains some basic tweaks for improving psql experience. A lot more could be found at the manpage or in the official PostgreSQL documentation. Use it to adapt your settings for specific needs or workflows.


Previous post: Simplifying and extending Jekyll CLI capabilities using Jtasks