psql Tips and Tricks

October 29, 2023

Quick links:

Why psql?

psql is a terminal-based front-end to PostgreSQL. It looks like this:

screenshot of psql

Why you would use psql when there are so many other clients available? 🤔

My short answer is that, sooner or later 1, you can end up on the wrong side of an ssh session, and psql is going to be all you’ve got.

It also doesn’t hurt to learn a few tricks (or to bookmark this page).

This isn’t a general psql tutorial: you can easily find one elsewhere.

Why I use psql

I use psql because:

  1. I don’t have to install anything else, which aligns with my minimalistic sensibilities
  2. I combine it with vim-slime, which seems to give me the best of all worlds

Better defaults

Locally, I keep this config in my $HOME/.psqlrc

\pset pager off
\pset null NULL
\x auto

When I’m on a remote machine, nothing prevents me from copy-pasting these 3 lines directly in psql. What does it change?

\pset pager off

If a query scrolls off the page – which doesn’t take a lot – you end up in the pager with the : prompt. This allows you to scroll up/down … but you need to press q to return to the normal prompt. I hate this default behavior … please let it scroll; and pager off does exactly that.

\pset null NULL

By default, null values look blank. With it, null will be shown as NULL. If you need something more obvious, you can use a different string:

\x auto

If you SELECT and the result is too wide, psql will wrap the text. That usually ends up looking pretty bad.

So, most people end up using \x on, which shows up vertically:

going vertical with x on

But with \x on, even a SELECT that could fit will end up vertical (and harder to read) … with \x auto, you will get horizontal UNLESS it’s too wide.

back to horizontal with x auto


Variables in psql can be as useful as variables anywhere else you use them. For example:

\set pages 100

-- use it:

SELECT ASIN, LEFT (title, 60)
FROM books
WHERE pages < :pages
ORDER BY inserted_at DESC

SELECT with a variable

The main advantages of using variables in psql:

One gotcha for text variables: if your value needs to be “quoted”, there’s a special syntax for that: :'varname'

you need to quote text variables

Temporary Tables

If variables are useful, temporary tables are an extension of that. You can capture multiple values in a table and use them with the IN operator:

-- capture multiple values

  SELECT ASIN FROM books WHERE pages < 75

-- then, later...

SELECT title
FROM books

SELECT using a temporary table

I didn’t have many uses for TEMPORARY TABLE before I thought of this. But what’s the impact of creating these temporary tables? And when would they disappear?

A temporary table, as its name implied, is a short-lived table that exists for the duration of a database session. PostgreSQL automatically drops the temporary tables at the end of a session or a transaction.


So, your temporary tables will garbage collect themselves when you quit psql. They also live in a separate schema, so they won’t be visible to other users.


I put down More psql Tips and Tricks since this article.

  1. Especially when things won’t be going well… 

Discuss on Twitter