Jake Worth

• postgresql

RC files, thought to stand for “run command” files, are read by a program during startup. The file that configures the PostgreSQL REPL psql is called .psqlrc. In this post I’ll explain my personal psql configuration.

Note: many of these configurations came from my time at Hashrocket. We documented some these preferences via PG Casts.

Here’s my .psqlrc.

\set QUIET on
\pset null 'ø'
\x auto
\timing
\set HISTFILE ~/.psql_history-:DBNAME
\set HISTCONTROL ignoredups
\set QUIET off

Let’s look at each setting.

Silence Startup

I wrap all the commands in a setting/unsetting of QUIET. This quiets commands that print terminal output such as \x auto.

-- Silence this startup
\set QUIET on

-- Turn up the noise after startup
\set QUIET off

I prefer to opt out of seeing them printed each time I enter psql.

Printable Null Character

Next, I set null values to something readable, ø, rather than the default empty string:

-- Set null to something that can be read
\pset null 'ø'

In the past I’ve used other characters here, including emojis.

Automatic Display Mode

Next, I turn on automatic display.

-- Use automatic display
\x auto

This uses psql’s extended display in the following circumstances:

“In the auto setting, the expanded mode is used whenever the query output has more than one column and is wider than the screen; otherwise, the regular mode is used. The auto setting is only effective in the aligned and wrapped formats. In other formats, it always behaves as if the expanded mode is off.”

Thanks to Josh Branchaud for helping me better understand this setting.

Enable Timing

Timing prints a benchmark like Time: 3.733 ms after each query.

-- Show timing of queries
\timing

I can take or leave this information, but I think it helps me be mindful of performance. When I don’t need it, it’s easy to ignore.

Database-Specific History files

Like many programs, psql records a history of your commands. This next configuration creates a unique history for each database, so I’m only revisiting queries that are relevant to my current domain (work queries at work, side project queries after hours).

-- Let histories be specific to each databse
\set HISTFILE ~/.psql_history-:DBNAME

I first learned about this technique back in 2015 and wrote about it here: Know your Histfile.

Duplicate-Free History files

The next setting ignores history duplicates.

-- Ignore duplicate history entries
\set HISTCONTROL ignoredups

I don’t need to record that I’ve queried a table dozens of times; once is enough.

Wrapping Up

Some of these are nice, and some, like showing a printable null character, are must-haves. Customizing psql has helped me build an ergonomic workspace and feel more comfortable in the REPL.

What are your thoughts on this? Let me know!