psql

If you work with PostgreSQL, you already have one of the best database tools installed. psql is the interactive terminal that ships with Postgres, and it’s seriously powerful once you learn a few tricks.

Exploring

The \d family is your best friend for navigating a database. \d just lists all tables, specify an additional name and will tell you all about that named object:

• \d — list all tables
• \d users — show columns, indexes and constraints for a table
• \d users_id_seq — describe a sequence

I mostly have all I need with these.

But there’s more: \dt — tables only, \di — indexes only, \df — functions, \dn — schemas, \du — roles…

Add a + for extra detail: \d+ users gives you storage info and column descriptions too.

Formatting output

By default psql output can be hard to read. But it’s very customizable!

A few settings for better output:

\pset border 2
\pset linestyle unicode
\pset format wrapped

And for wide tables with many columns, \x toggles expanded (vertical) display: one row per field. You can also set \x auto to let psql decide based on width.

Running and editing queries

Here’s where it gets good. \e opens your $EDITOR with the last query you ran. Edit it, save, quit. psql runs it immediately.

You’re writing SQL in vim/vscode (or whatever you use) with syntax highlighting, proper editing, and all your keybindings. Way better than wrestling with a multi-line query at the prompt, or fiddling with any GUI.

You can take this further. Write your queries in .sql files and run them with \i:

\i queries/monthly_report.sql

Keep a folder of useful queries, version them, reuse them. If you’re in vim, you can even pipe a selection straight to psql!

The inverse is also true. You can just save the results of whatever you want to run into a file. Just open the file with \o file_name, do your thing, and close the writing with another \o:

\o user_counts
SELECT count(*) FROM users;
SELECT count(*) FROM user_tokens;
\o

More friends

\timing shows how long each query takes. Simple but invaluable when you’re optimizing.

\watch 2 reruns the last query every 2 seconds. Great for monitoring a table while something is running, like watching a job queue drain in real time.

\copy is awesome. It imports/exports CSV without needing superpowers. Quick and dirty data dumps when you need them.

The .psqlrc

You don’t need to type all those tweaks every time. Put them in ~/.psqlrc and they load automatically on every session.

And there's much more.

Split history per database. No more mixing production queries with local dev noise. Bump the size. Skip duplicates.

Customize your prompt. The default just shows the database name. Useless when 3 tabs say postgres=>. Show user, host, port, database, transaction status. Add color. Always know where you are.

ON_ERROR_ROLLBACK interactive. A query fails inside a transaction? Normally the whole thing is toast. This creates implicit savepoints. Only the failed statement rolls back. Fix your typo, keep going.

COMP_KEYWORD_CASE upper makes tab completion uppercase keywords. sel<TAB> becomes SELECT. I am the only one still doing uppercases like this?

FETCH_COUNT 1000 fetches in batches. No more frozen terminal from an accidental SELECT * on millions of rows.

PAGER set to less -SFX chops long lines, skips the pager for short output, doesn't clear the screen on quit.

Wrap everything between \set QUIET 1 and \set QUIET 0. No setup noise. End with \conninfo for a connection summary on startup.

If you want my .psqlrc, hit me up. Happy to share.

So…

Give a proper chance to psql. You might stop reaching for that GUI.