psql Tips and Tricks

Quick links:

• better defaults
• variables
• temporary tables

Why psql?

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

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

My short answer is that, sooner or later ¹, 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:

• (null)
• <null>
• or this 😄

\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:

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.

Variables

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
LIMIT 5;

The main advantages of using variables in psql:

• write your query once
• no painful text editing of the SQL
  (use the up arrow to rerun the same query)
• only change the variable value

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

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

DROP TABLE IF EXISTS results;
CREATE TEMPORARY TABLE results AS (
  SELECT ASIN FROM books WHERE pages < 75
);

-- then, later...

SELECT title
FROM books
WHERE ASIN in (SELECT * FROM results)
LIMIT 10;

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.

(reference)

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.

Addendum

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