psql-tricks

Using psql properly

If you’ve only ever used psql to run SELECT * FROM users; and quit, you’re missing out. The Postgres CLI is genuinely one of the best database clients out there once you learn a handful of tricks. Here are the ones I reach for constantly.

1. \gx — pivot any query to expanded output

You know that pain when a SELECT * returns one row with 30 columns and wraps into an unreadable mess? End the query with \gx instead of ;:

SELECT * FROM pg_stat_activity WHERE pid = 12345 \gx

\gx runs the query in expanded mode (one column per line), no matter what \x is currently set to. There’s also plain \g which just re-runs the previous buffer, handy when you forget the semicolon.

2. Save queries as variables with \set

This is the :old_queries trick. You can stash any string in a psql variable and then interpolate it later:

\set old_queries 'SELECT pid, now() - query_start AS age, state, query FROM pg_stat_activity WHERE state != ''idle'' ORDER BY age DESC LIMIT 5;'

Now any time you type :old_queries (no quotes — psql substitutes it before sending), it runs:

:old_queries

Note that these auto-complete when you press TAB twice, so you don’t even have to type their whole name out.

Stick a bunch of these in your ~/.psqlrc and you’ve basically built yourself a personal toolkit:

\set active 'SELECT pid, age(clock_timestamp(), query_start), usename, query FROM pg_stat_activity WHERE state != ''idle'' ORDER BY query_start ASC;'
\set locks 'SELECT * FROM pg_locks WHERE NOT granted;'
\set bloat 'SELECT schemaname, relname, n_dead_tup, n_live_tup FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;'

For values you want to interpolate into a query (with proper quoting), use :'varname' for a quoted string and :"varname" for a quoted identifier:

\set uid 42
SELECT * FROM users WHERE id = :uid;

\set tbl users
SELECT * FROM :"tbl" LIMIT 5;

3. \watch — turn any query into a live dashboard

Append \watch 2 to re-run a query every 2 seconds:

SELECT count(*), state FROM pg_stat_activity GROUP BY state \watch 2

Combine it with the :active variable above and you have a live top for your database. Ctrl-C to stop.

4. \e — edit the last query in your $EDITOR

Typed out a 20-line query and noticed a typo on line 3? Just type \e. psql opens your editor with the query buffer; save and quit and it runs. You can also do \e filename.sql to edit and run a file. This single command has saved me hours of cursor-arrow-key suffering.

5. \timing — how long did that take?

\timing on

Now every query prints its execution time. Toggle it off with \timing again. Great for ad-hoc «is this index helping?» checks before you reach for EXPLAIN ANALYZE.

6. The \d family — your schema X-ray

Everyone learns \dt (list tables) eventually, but the family is huge:

• \d tablename — columns, indexes, constraints, triggers for one table
• \d+ tablename — same plus storage, stats, comments
• \di — indexes, \dv — views, \df — functions, \dn — schemas
• \dt *.* — tables across all schemas
• \dt user* — pattern matching works everywhere

Add + to almost any of them for more detail. \? shows the full list of backslash commands; \h CREATE INDEX shows SQL syntax help without leaving psql.

7. \copy — import/export CSV without superuser

COPY (the SQL command) reads files on the server, which usually requires superuser. \copy (the psql command) reads from your local machine and works for anyone:

\copy (SELECT * FROM orders WHERE created_at > '2026-01-01') TO 'orders.csv' CSV HEADER
\copy users FROM 'users.csv' CSV HEADER

It’s significantly faster than INSERT loops for bulk loads.

8. \ef and \sf — edit and show functions

Working with stored functions? \sf my_function prints the function definition. \ef my_function opens it in your editor. No more digging through pg_proc.

9. ~/.psqlrc — make it yours

Everything above gets better when it’s loaded automatically. A starter .psqlrc:

\set QUIET 1
\pset null '∅'
\pset linestyle unicode
\pset border 2
\timing on
\set HISTFILE ~/.psql_history-:DBNAME
\set HISTCONTROL ignoredups
\set COMP_KEYWORD_CASE upper
\set PROMPT1 '%[%033[1;32m%]%n@%/%[%033[0m%]%# '

-- saved queries
\set active 'SELECT pid, age(clock_timestamp(), query_start) AS age, usename, query FROM pg_stat_activity WHERE state != ''idle'' ORDER BY age DESC;'

\unset QUIET

The HISTFILE line gives you a separate command history per database, which is wonderful once you have it.

10. \! runs shell commands

\! ls -la
\! pg_dump mydb > backup.sql

Useful when you want to glance at a file or kick off a dump without dropping out of your session.

11. \crosstabview — instant pivot tables

Run a query that returns three columns (row, column, value) and append \crosstabview

SELECT state, date_trunc('month', created_at)::date AS month, count(*)
FROM orders
GROUP BY 1, 2
ORDER BY 1, 2 \crosstabview state month count

You get a real pivot table in the terminal. No spreadsheet required.

12. \gexec — use a query to generate and run more queries

This can provide a really useful shortcut when you want to run lots of commands based on table data. \gexec takes the result of a query and executes each returned string as SQL:

SELECT 'REINDEX TABLE ' || quote_ident(tablename) || ';'
FROM pg_tables WHERE schemaname = 'public' gexec

Here, every table in public reindexed. Great for one-off bulk DDL: dropping a bunch of temp tables, granting permissions across schemas, vacuuming a whitelist, etc.

13. \gset — capture query results into variables

The mirror of \set. Run a query and stash its columns as psql variables:

SELECT count(*) AS users, max(id) AS max_id FROM users \gset
\echo :users :max_id

Combine with :'var' interpolation to chain queries together in a script.

14. EXPLAIN (ANALYZE, BUFFERS) — and save it as a variable

EXPLAIN ANALYZE is well-known, but BUFFERS is the underrated companion — it shows how many pages came from cache vs disk, which is often the actual answer to «why is this slow.»

\set explain 'EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT) '
:explain SELECT * FROM orders WHERE user_id = 42;

15. \errverbose — get the full error after the fact

Got a cryptic error and want the full context (schema, constraint name, source file)? Just type:

\errverbose

It re-prints the most recent error with every field the server sent. Saves you from re-running the query with \set VERBOSITY verbose first.

16. \conninfo and \c — know where you are, jump elsewhere

\conninfo tells you which database, host, port, and user you’re connected as — invaluable before you run DROP TABLE and realise you were on prod. \c otherdb switches databases without quitting; \c - otheruser switches users; \c dbname user host port does it all.

Tip: put the database name in your PROMPT1 (see the .psqlrc above) so you can never forget.

17. psql -c and -f — one-liners from your shell

You don’t always need an interactive session:

psql -c "SELECT count(*) FROM users" mydb
psql -f migration.sql mydb
psql -At -c "SELECT email FROM users WHERE active" mydb > emails.txt

-A is unaligned, -t is tuples-only — together they give you clean output you can pipe into other tools. This is how you turn psql into a shell scripting building block.

psql is way more than a query runner. Spend ten minutes on your .psqlrc, learn \gx, \watch, and \e, and you’ll wonder why you ever opened a GUI client.

If you found this useful, I also put together a free PostgreSQL Maintenance Health Check Guide — and if you want structured learning around this kind of material, have a look at our PostgreSQL training courses.