Published: April 12, 2022 • 2 min read
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.
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.
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.
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.
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.
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.
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.
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!
Get better at programming by learning with me! Join my 100+ subscribers receiving weekly ideas, creations, and curated resources from across the world of programming.