Right, let’s talk about making the output from psql actually readable. Because by default, it’s… well, it’s functional. It’s the database equivalent of a plain tofu block: it gets the job done, but you wouldn’t want to serve it to guests. The designers gave us a Swiss Army knife of formatting options, and we’re going to learn how to use every useful blade on it.

Taming the Horizontal Beast with \x

The most immediate formatting problem you’ll hit is a wide table. You’ll run a simple SELECT * FROM users; and psql will try to print it all on one line, wrapping the text across your terminal in a chaotic, unreadable mess. It’s a train wreck of a presentation.

This is where \x comes in. It toggles “expanded display” mode. When on, instead of showing each row as a horizontal line, it displays each column of a row as a separate, vertical line. It’s a lifesaver for rows with a lot of columns or with wide text fields.

-- First, let's see the problem
SELECT * FROM products WHERE id = 1;

-- Yuck. A mess. Now toggle expanded display.
\x
-- Auto-expanded display is on. (Just so you know)

-- Run it again
SELECT * FROM products WHERE id = 1;
-[ RECORD 1 ]-------------------------------
id          | 1
name        | Absolutely Gigantic Industrial Widget
description | This widget is so large, so complex, and has so many features...
price       | 9999.99

See? Suddenly it’s readable. The \x command by itself is a toggle. I find the toggle behavior a bit annoying, because you can never remember what state it’s in. For sanity, use \x auto. This is brilliant because it lets psql decide. If the result fits neatly in your terminal width, it displays normally. If it’s too wide, it automatically switches to expanded format. It’s one of the first things I set in my .psqlrc file.

-- Set it and forget it to the sensible option
\x auto

Your Formatting Batbelt: \pset

If \x is the emergency brake, \pset (print set) is the entire dashboard. This command controls almost every aspect of how psql formats its output. Forget one of the silly GUI buttons; this is where the real power is.

Let’s say you’re generating a report. The default unicode border characters (-, |, +) might not paste well into an email or a document. You can change the entire format to something more portable.

-- Switch to a classic, ASCII-only format. Boring, but universal.
\pset format unaligned
\pset fieldsep ','
SELECT name, price FROM products LIMIT 3;
name,price
Small Widget,19.99
Medium Widget,49.99
Absolutely Gigantic Industrial Widget,9999.99

-- Whoops, that's basically CSV. Let's go back to something more readable.
\pset format aligned
\pset fieldsep '|'

The \pset command is incredibly powerful. You can control the title of your output (\pset title 'My Report'), the null display (\pset null '[NULL]' so you can actually see the darn nulls), and the style of the border lines.

Dealing with the Pager (Less vs. More)

Ah, the pager. This is the tool psql uses when a result is too long to fit on one screen. By default, it’s less, and this is a good choice. But sometimes it gets in the way.

The most common pitfall? You run a query, less opens, you view the output, and then you quit. But then your terminal’s messed up. The screen is cleared, and your previous commands are gone. This isn’t psql’s fault; it’s because less uses the terminal’s “alternate screen” by default. It’s infuriating.

You have two excellent options. First, you can tell psql to never use a pager.

-- Just give me everything, always. I'll scroll my terminal myself, thank you.
\pset pager off

The second, and my preferred method, is to fix less instead of ditching it. You can set the LESS environment variable to include -S (chop long lines) and -X (leave the screen alone after exit). I don’t do this in psql; I set it in my shell’s .bashrc or .zshrc:

# In your ~/.bashrc or similar
export LESS='-S -X'

This way, less behaves itself across your entire system. If you want to see wrapped long lines in a specific instance, you can always just type -S from within less to toggle line chopping.

The best practice is simple: use \x auto religiously, and configure your pager correctly at the system level. Tweak \pset for specific one-off reporting needs, but for your day-to-day work, set these sane defaults in your ~/.psqlrc file so you never have to think about them again.