Unlocking psql: Meta-Commands that Supercharge Your PostgreSQL CLI Work

Introduction

If you manage PostgreSQL from a terminal you already know psql, the interactive client that ships with every installation. Most developers use it for the basics—running SELECT statements, loading a .sql file, maybe poking around with \dt to see which tables exist.

Beneath that familiar surface, though, psql hides a rich toolbox of meta-commands. These commands, all prefixed with a backslash, live inside the client. They’re not SQL, they’re shortcuts built into psql itself, and they can make everyday tasks faster and far less error-prone.

Below are six of the most useful commands—features even experienced users often overlook—along with practical examples of when they shine.

---

1. \watch — Re-run Any Query on a Timer

When you need a real-time view of something—queue depth, lock counts, job status—psql can refresh the same result set over and over.

SELECT count(*) AS pending_orders
FROM   orders
WHERE  status = 'pending';
\watch 5   -- refresh every 5 seconds

The query runs once, then automatically re-executes every five seconds, clearing and rewriting the output in place. It’s the quickest way to turn a one-off query into a minimalist dashboard while you’re troubleshooting or demoing.

Tip: You can direct \watch to a file with \o /path/log.txt first, then cancel with Ctrl-C when you’ve captured enough samples.

---

2. \x — Expanded Display for Wide Rows

Long rows wrap badly in narrow terminals. Toggle expanded mode once and every subsequent result prints as a vertical list, one column per line—ideal for tables with JSON columns or dozens of attributes.

\x        -- turn it on
SELECT * FROM invoices WHERE id = 42;

Need the original layout back? Just run \x again to toggle off.

---

3. \df+ — Explore Functions in Detail

PostgreSQL’s catalog is packed with built-in and user-defined functions. To inspect them quickly:

\df+ *.*               -- every function in every schema
\df+ my_schema.*       -- everything in one schema
\df+ public.my_func    -- one specific function

The plus sign (+) adds verbose columns: argument list, return type, language, volatility, even the function body for SQL and PL/pgSQL routines. When you inherit an unfamiliar database, \df+ is the fastest way to map its procedural landscape.

---

4. \do — List All Operators

Custom operators such as @>, <@, or || can be pivotal to an application yet invisible in the schema browser. \do surfaces them all:

\do           -- show every operator the server knows
\do text.*    -- only operators defined for the text data type

Alongside the symbol you’ll see left and right argument types and the function that implements each operator’s logic, giving you a full audit trail of non-standard behavior.

---

5. Session Variables with \set and :name

Inside one psql session you can create lightweight variables and interpolate them into any SQL statement.

\set myid 123
SELECT * FROM users WHERE id = :myid;

\set country_code '''US'''
SELECT * FROM sales WHERE country = :country_code;

Variables persist until you close psql, making them perfect for ad-hoc scripts where you might otherwise edit the same constant in multiple places.

---

6. Shell Escapes (\!) — Drop to the OS Without Leaving psql

Need to check the date, tail a log, or clear the screen? Prefix any shell command with \!.

\! date
\! clear
\! tail -n 20 /var/log/postgresql/postgresql-16-main.log

Because the database connection stays open, you can hop between SQL and shell utilities in a single workflow—no extra terminal tab required.

---

Why Learn These Commands?

Meta-commands don’t change how SQL itself behaves, but they do change how you experience it. By folding editor features, monitoring tricks, and OS access into one prompt, psql becomes an interactive playground rather than a plain statement runner. Fewer context switches mean fewer typos, faster feedback, and a smoother cognitive flow.

The six commands above are only a starting point. Type \? in any psql session and you’ll get a full, scrollable list—everything from \e (edit the current query in $EDITOR) to \pset csv (output as comma-separated values). Spend a coffee break exploring, and chances are you’ll uncover something that cuts a recurring task in half.

Next time you open the PostgreSQL CLI, keep one finger on the backslash. Your future self will thank you.