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.

-- Set a variable. Note the lack of quotes. This is key.
\set my_table_name my_cool_table

-- Use it. The colon is how you tell psql to expand the variable.
SELECT * FROM :my_table_name;

See what happened there? Psql saw :my_table_name, expanded it to my_cool_table, and sent SELECT * FROM my_cool_table; to the server. This is gloriously simple and dangerously dumb. Because the expansion happens client-side, you have to be painfully explicit about syntax.

-- This will fail spectacularly.
\set name Bruce Wayne
SELECT * FROM heroes WHERE name = ':name'; -- Server sees: WHERE name = 'Bruce Wayne'

-- This is what you actually meant to do. No quotes in the \set.
\set first_name Bruce
\set last_name Wayne
SELECT * FROM heroes WHERE first_name = :'first_name' AND last_name = :'last_name';

The :'variable_name' syntax is your friend here. It wraps the variable’s value in single quotes, making it a proper SQL string literal. For other data types, like integers, you’d just use :variable_name directly.

The Game Changer: \gset for Smart Variables

Now, \gset is where the real magic happens. It’s the reason you can build moderately intelligent scripts. While \set lets you set variables manually, \gset takes the results of the previous query and stuffs them into psql variables.

You use it by terminating a query with \gset instead of a semicolon. The query must return exactly one row. Each column in that row becomes a new psql variable, named after the column itself (case-sensitive!).

-- Get some stats and store them
SELECT count(*) AS num_users, max(id) AS max_user_id FROM users \gset

Boom. You now have two new variables: num_users and max_user_id. You can use them in your next command.

-- Print them out. The backslash-echo meta-command is useful here.
\echo The number of users is :num_users and the highest ID is :max_user_id

-- Use them in a subsequent, dynamic query
SELECT * FROM login_attempts WHERE user_id > :max_user_id - 100;

This is incredibly powerful for scripting. Need to archive all records older than the most recent 1000? Find the max ID, subtract 1000, and there’s your WHERE clause. It turns a multi-step manual process into a single script.

Pitfalls and the Art of Escaping

Of course, it’s not all roses. The main gotcha with \gset is the one-row requirement. If your query returns zero rows, \gset will unset all the variables you were expecting to set. If it returns two or more, it will unset them and use the values from the last row, which is almost certainly not what you want. Always test your \gset query in isolation first to ensure it returns exactly one row.

Another quirk: the variable names are case-sensitive and are created exactly as the column is aliased. SELECT count(*) ... \gset gives you a variable named count. SELECT count(*) as cnt ... \gset gives you cnt. I strongly recommend always using AS to give your columns sensible, lowercase, underscore-separated names for your variables. Your sanity will thank you.

You can also use \gset with a prefix to avoid variable name collisions, which is a fantastic practice for larger scripts: SELECT ... \gset prefix_. This will create variables like prefix_column1, prefix_column2.

Why This All Matters

You might be thinking, “Why not just do this in a PL/pgSQL function?” Sometimes you can’t. Sometimes you’re orchestrating things outside the database, like running batch jobs, generating reports, or building deployment scripts. This is psql’s native way of giving you state and logic. It’s a bit clunky, I’ll grant you. It feels like it’s from a simpler time, because it is. But it works, it’s everywhere PostgreSQL is, and understanding it lets you bend psql to your will instead of just typing commands into it.