Right, let’s talk about your .psqlrc file. This is your secret weapon, your personalized command center for psql. Think of it as the difference between a rental car with the seat all wrong and the radio tuned to polka, and your own car, where everything is exactly where you expect it. Without it, you’re just using psql. With a well-configured one, you are psql.

This file lives in your home directory (~/.psqlrc on Linux/macOS, %APPDATA%\postgresql\psqlrc.conf on Windows—yes, the path is different, because of course it is). psql automatically reads and executes it every time it starts up. We use this power for good, not evil.

The \set Stalwart: Your psql Environment

The \set meta-command is your workhorse here. It lets you define psql variables, which are different from PostgreSQL variables—think of them as local shell variables for your psql session. The most important one? PROMPT1, which controls your main prompt.

The default prompt is fine if you enjoy constantly typing \c to remember what database you’re in. I don’t. Let’s fix it.

-- A prompt that actually tells you things. Revolutionary.
\set PROMPT1 '%/%R%# '
-- %n : database name
-- %/ : current database
-- %R : mode (blank=normal, *=transaction, !=error)
-- %# : becomes '#' for superuser, '>' otherwise

For a much more detailed, almost comically informative prompt:

\set PROMPT1 '%[%033[1;32m%]%n@%/%[%033[0m%] %R%# '

This gives you a bold green username@dbname followed by a normal mode/symbol. Yes, those are ANSI escape codes. It looks terrifying, but it’s worth it for a color-coded, at-a-glance status update.

You can also set variables to hold commonly used values, saving your poor fingers.

\set MYDB my_awesome_database
\echo :MYDB
-- Later, you can use it with a colon for variable interpolation
\c :MYDB

\pset: Making Output Less Ugly

The \pset command configures how your result sets are printed. Setting this in your .psqlrc means you never have to look at that miserably wrapped, hard-to-read default output again.

-- The three pillars of readable output:
\pset border 2        -- Boxed borders are so much easier to read. 2 is the good one.
\pset format wrapped  -- 'aligned' is the default, 'wrapped' is often better for wide tables.
\pset null [NULL]     -- Because an empty string and a NULL should look different.
-- Also incredibly useful:
\pset pager always    -- Or 'on' for long output. Spare your scrollback buffer.
\pset tuples_only on  -- For when you *just* want the data, no headers or footers. Flip it off with 'off'.

The \e Trick: Your Default Editor

This one is simple but life-altering. By default, \e brings up the last command in your system’s default editor (usually vi, because the 1970s called). If you, like a sane person, prefer something else (like code for VS Code or nano for simplicity), set the EDITOR environment variable in your shell, not here. psql respects that. But you can set a different one just for psql if you’re a masochist who wants different editors for different contexts.

# Do this in your .bashrc/.zshrc, not .psqlrc
export EDITOR="code --wait"

Now \e opens the query buffer in VS Code. Magic.

Autostart Functions and Common Queries

Here’s where we get clever. You can use \set with a bit of magic to pre-define useful snippets. Since psql variables can hold multiple lines, you can effectively create your own meta-commands.

-- Create a variable that holds a common query. Note the single quotes.
\set activity_check 'SELECT pid, usename, application_name, state, backend_start FROM pg_stat_activity;'
-- Now, to run it, just use :'variablename' and pipe it to \gexec
-- :'activity_check' \gexec

-- Or, be even fancier and make it a one-liner.
-- This sets a variable that, when evaluated, runs the command.
\set activity_check '\\''SELECT * FROM pg_stat_activity;\\'' \\! less'
-- This gets messy. Honestly, it's often cleaner to just type the query.

A word of warning: don’t go overboard here. The syntax for complex commands gets gnarly fast, and you’ll spend more time debugging your .psqlrc than using it.

The Cardinal Rule: Don’t Break Everything

This is the most important part. Your .psqlrc is executed on every connection. If you put a command in there that fails, your entire psql session fails to start. It’s incredibly frustrating. So, the golden rule: Test your .psqlrc changes in a session before you save them to the file.

If you’ve already borked it and psql won’t start, you can bypass the .psqlrc file entirely with:

psql -X

The -X flag is your emergency brake. It tells psql “I know I messed up my config, just ignore it and let me in.”

So, go forth. Craft a .psqlrc that makes you smile. Make it yours. Just don’t \set PROMPT1 'DROP DATABASE production;' and blame me. I warned you.