Psql

Right, let’s talk about the four shortcuts that will save you more cumulative hours than any other feature in psql. These aren’t just commands; they’re workflow accelerants. You’ll wonder how you ever lived without them. The Magical Do-Over: \e You’ve just written a beautiful, complex, four-line query. You hit enter. A typo. Or you realize you need to add a WHERE clause. Your heart sinks. Do you press the up arrow and try to edit this behemoth in the terminal, hoping you don’t accidentally delete a parenthesis and summon a demon? No. You type \e.

Right, let’s talk about your .psqlrc file. This is your secret weapon, your personalized command center for psql. Think of it as the difference between a rental car with the seat all wrong and the radio tuned to polka, and your own car, where everything is exactly where you expect it. Without it, you’re just using psql. With a well-configured one, you are psql. This file lives in your home directory (~/.psqlrc on Linux/macOS, %APPDATA%\postgresql\psqlrc.conf on Windows—yes, the path is different, because of course it is). psql automatically reads and executes it every time it starts up. We use this power for good, not evil.

Right, let’s talk about giving your SQL a bit of a brain. You’re not just typing commands; you’re writing a script, and a script needs variables, logic, and a way to remember things. That’s where \set and \gset come in. They’re the duct tape and baling wire of psql scripting, and once you get the hang of them, you’ll wonder how you ever lived without them. The Basics: \set for Dumb Variables First, \set. This isn’t some fancy namespaced variable; it’s more like writing on a whiteboard. You use it to set a psql variable, which is really just a text macro. The syntax is \set [name] [value]. If you omit the value, it unsets the variable. Crucially, these variables are expanded before the command is sent to the server. The server itself has no idea they exist.

Right, so you’ve graduated from typing SQL directly into psql one line at a time. Welcome to the big leagues. We’re now going to talk about running entire files of SQL, which is how you do real work: loading data, applying schema changes, running complex reports. You have two main ways to do this, and while they seem similar, the devil—as always—is in the details. The In-Command (\i) and The Flag (-f) You’ll use \i (for “include”) when you’re already inside a psql session and you suddenly remember you were supposed to run that migration script. Its cousin is the -f flag, which you use when you launch psql from your normal shell prompt.

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.

Right, let’s talk about the real reason you’re here: the meta-commands. These are the magic spells that make psql so much more than just a dumb SQL terminal. They’re the shortcuts, the sanity checks, the “oh thank god I don’t have to query the system catalogs manually again” lifesavers. Forget the clunky information_schema queries for a moment. These commands are psql’s love letter to the DBA and developer who values their time. They all start with a backslash (\), which is your signal to psql that you’re not talking standard SQL; you’re talking its native language.

Right, let’s talk about getting in. The psql client is your primary teletype into the kingdom of Postgres. It’s how you’ll issue commands, panic when you see a query taking too long, and then frantically cancel it. But before you can do any of that, you need to connect. And Postgres, in its infinite wisdom, gives you approximately one million ways to do it. I’ll cover the ones you’ll actually use.