There's been a kind of persistent myth regarding Postgres since I first started using it seriously over 20 years ago: "Postgres doesn't support user variables." This hasn't really been true since version 8.0 way back in 2005. Part of this stems from the fact it doesn't do things the same way as other common database engines.

Why don't we spend a little time exploring the functionality that time forgot?

What Everyone Else Is Doing

Before I delve into the Postgres approach, let's take a look at the competition. If anyone wants to switch to Postgres (as they should), they'll bring along plenty of assumptions.

Let's start with MySQL, the formerly undisputed database king of the LAMP stack. MySQL session variables merely prefix any name with @ to assign a value:

SET @user_id = 42;
SET @username = 'alice';

SELECT @user_id, @username;

 @user_id | @username
----------+-----------
       42 | alice

Simple, right? It's even possible to use them directly in queries:

SELECT * FROM orders WHERE customer_id = @user_id;

We don't have to get into the finer minutiae here, as the MySQL documentation on user-defined variables does that job splendidly. The point is that some users expect this level of compatibility and balk when it's missing.

When it comes to SQL Server, things are very similar to MySQL, though perhaps a bit more structured:

DECLARE @user_id INT = 42;
DECLARE @username NVARCHAR(100) = N'alice';

SELECT * FROM orders WHERE customer_id = @user_id;

Once again, the SQL Server documentation on variables is pretty clear about how these work. The primary caveat here is that these are limited to the current batch, making them somewhat tedious to work with in some cases.

The picture for Oracle is a bit different. Oracle calls them substitution variables, and prefixes using & rather than @: 

DEFINE user_id = 42
DEFINE username = alice

SELECT * FROM orders WHERE customer_id = &user_id;

This is also closer to a macro system than a true variable; the SQL*Plus or SQLcl clients substitute the values prior to sending statements to the server. It's not something other drivers or clients can use unless they added it themselves for compatibility purposes.

Postgres Has Entered the Chat

So where does Postgres fit into all of this?

If Oracle's &variable substitution is what you're accustomed to, Postgres actually has a direct equivalent. The psql client supports \set for defining client-side variables: 

\set user_id 42
SELECT * FROM orders WHERE customer_id = :user_id;

\set region us-east-1
SELECT * FROM orders WHERE region = :'region';

The psql tool has supported these practically since the beginning, but some users find them insufficient. As with Oracle client-side substitutions, they only work specifically in the psql client. This can be a major limitation when interacting with Postgres in any other manner.

The real answer lives server-side, and it's been hiding in plain sight since at least version 9.2 in 2012. It doesn't require any special syntax, extensions, or package declarations, and uses the same configuration parameter system that controls options like work_mem and statement_timeout. That's right, it's just the regular SET statement.

The trick is in the usage. Postgres treats any name containing a period as a custom parameter:

SET myapp.user_id = '42';
SET myapp.username = 'alice';
SET myapp.tenant_id = '100';

That's it. Those values now exist for the duration of the current session. Reading them back is just as straightforward with the SHOW statement: 

SHOW myapp.user_id;

 myapp.user_id
---------------
 42

SHOW myapp.username;

 myapp.username
----------------
 alice

And clearing them:

RESET myapp.user_id;
SHOW myapp.user_id;

 myapp.user_id
---------------
 (empty string)

The dot in the name is mandatory for custom parameters. The part before the dot acts as a namespace (like myapp, audit, or tenant). Postgres uses this to distinguish custom settings from its own built-in configuration parameters.

But SET and SHOW are SQL statements, which means it's not conventionally possible to embed them in expressions or subqueries. So how do we use these handy new parameters?

Getting and Setting

Postgres provides two system administration functions that make manipulating user variables easy and convenient.

Let's take a look at set_config first. It's the functional analog of the SET statement:

SELECT set_config('myapp.user_id', '42', false);

 set_config
------------
 42

The third parameter just means: "should this only be a local parameter?" Setting it to false allows the value to persist for the entire session, which is what most users may expect. Because set_config is a regular function, it works everywhere a SQL expression is valid.

Then we can use current_setting to retrieve that value:

SELECT current_setting('myapp.user_id');

 current_setting
-----------------
 42

As the functional equivalent of SHOW, it returns the current value of any configuration parameter as text.

The optional second parameter (missing_ok) is a small but important detail. By default, requesting an unset parameter raises an error:

SELECT current_setting('myapp.nonexistent');
ERROR:  unrecognized configuration parameter "myapp.nonexistent"

Rather than wrapping everything in an exception handler, it's possible to suppress the error and return NULL by setting the second parameter to true: 

SELECT current_setting('myapp.nonexistent', true);

 current_setting
-----------------
 (null)

Because set_config and current_setting are plain functions, they integrate naturally with any SQL statement. Perhaps we need to convert a username to a tenant ID:

SELECT set_config('myapp.tenant_id', id::text, false)
  FROM tenants
 WHERE username = CURRENT_USER;

Need to filter by the current tenant?

SELECT *
  FROM orders
 WHERE tenant_id = current_setting('myapp.tenant_id')::int;

Need to set context at connection time and have it flow through to every query automatically?

-- Application calls this when checking out a connection from the pool
SELECT set_config('myapp.user_id', '42', false),
       set_config('myapp.tenant_id', '100', false),
       set_config('myapp.role', 'analyst', false);

Need to capture the current user in an audit trigger?

CREATE OR REPLACE FUNCTION f_audit_stamp()
RETURNS TRIGGER AS
$$
BEGIN
  NEW.modified_by = current_setting('myapp.user_id', true);
  NEW.modified_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Allowing unset variables in the trigger is a nice safety net. If the application forgot to set the variable, the audit column gets NULL instead of aborting the insert. Whether that's the right behavior depends on the specific use case, but it's great to have options.

Properly Scoped

Remember that third parameter in set_config? When is_local is true, the value applies only for the current transaction. Once the transaction commits or rolls back, the value reverts to whatever the session-level setting was before. This is equivalent to the SET LOCAL SQL statement:

-- Session-level value
SELECT set_config('myapp.request_id', 'session-default', false);

BEGIN;
  -- Transaction-local override
  SELECT set_config('myapp.request_id', 'txn-abc-123', true);
  SELECT current_setting('myapp.request_id');

   current_setting
  -----------------
   txn-abc-123

COMMIT;

-- Back to the session-level value
SELECT current_setting('myapp.request_id');

 current_setting
-----------------
 session-default

The same behavior works with SET LOCAL: 

SET myapp.request_id = 'session-default';

BEGIN;
  SET LOCAL myapp.request_id = 'txn-abc-123';
  SHOW myapp.request_id;    -- txn-abc-123
COMMIT;

SHOW myapp.request_id;      -- session-default

Why is this useful? Consider a web application where each HTTP request maps to a single database transaction. The application sets the request context at the start of the transaction, and every query within that transaction can see it. After the transaction commits, the context is automatically cleaned up. No manual RESET needed, and no risk of context leaks between transactions.

This is especially valuable with connection poolers like PgBouncer, where a single database session handles requests from many different application users. Transaction-scoped variables guarantee that context from user A never bleeds into a query for user B, because each user's transaction carries its own isolated state.

Rollbacks work as expected, too:

SET myapp.mode = 'normal';

BEGIN;
  SET LOCAL myapp.mode = 'maintenance';
  SHOW myapp.mode;          -- maintenance
ROLLBACK;

SHOW myapp.mode;            -- normal

Local scope also applies when transactions rollback to a savepoint:

BEGIN;
  SET LOCAL myapp.step = 'one';

  SAVEPOINT sp1;
    SET LOCAL myapp.step = 'two';
    SHOW myapp.step;        -- two
  ROLLBACK TO sp1;

  SHOW myapp.step;          -- one
COMMIT;

A Practical Exercise

Let's build something a bit more operational. We'll set up a multi-tenant orders table with automatic audit stamping and row-level security, all driven by session variables.

First, the table:

CREATE TABLE orders (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id   INT NOT NULL,
  product     TEXT NOT NULL,
  quantity    INT NOT NULL,
  created_by  TEXT,
  created_at  TIMESTAMPTZ DEFAULT now()
);

Next, a trigger that automatically stamps every new row with the application user:

CREATE OR REPLACE FUNCTION f_stamp_order()
RETURNS TRIGGER AS
$$
BEGIN
  NEW.created_by = current_setting('app.current_user', true);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER t_stamp_order
BEFORE INSERT ON orders
   FOR EACH ROW EXECUTE FUNCTION f_stamp_order();

Now add a Row-Level Security policy that filters rows by the current tenant:

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.tenant_id', true)::int);

With this in place, the application sets context when checking a connection out of the pool:

SELECT set_config('app.current_user', 'alice', false),
       set_config('app.tenant_id', '1', false);

And then every operation automatically respects the tenant boundary:

INSERT INTO orders (tenant_id, product, quantity)
VALUES (1, 'Widget', 10);

SELECT * FROM orders;

 id | tenant_id | product | quantity | created_by |          created_at
----+-----------+---------+----------+------------+-------------------------------
  1 |         1 | Widget  |       10 | alice      | 2025-04-16 14:30:00.000000+00

If we switch tenants, the view changes automatically:

SELECT set_config('app.tenant_id', '2', false);
SELECT * FROM orders;

 id | tenant_id | product | quantity | created_by | created_at
----+-----------+---------+----------+------------+------------
(0 rows)

No explicit WHERE tenant_id = ... in the query or risk of forgetting the filter. The session variable drives the RLS policy, and the RLS policy enforces the boundary. This is the same pattern SQL Server achieves with RLS plus SESSION_CONTEXT, and what Oracle targets with Virtual Private Database plus SYS_CONTEXT (though Oracle requires considerably more setup to get there). And Postgres achieves it with far less ceremony than either.

For transaction-scoped isolation (ideal with connection poolers), swap false for true: 

BEGIN;
  SELECT set_config('app.current_user', 'bob', true),
         set_config('app.tenant_id', '2', true);

  INSERT INTO orders (tenant_id, product, quantity)
  VALUES (2, 'Gadget', 5);

  SELECT * FROM orders;
  -- Shows only tenant 2's orders, created_by = 'bob'
COMMIT;
-- Context automatically reverts; no stale state on the connection

A Few Caveats

Custom GUC parameters are powerful, but they come with some characteristics that deserve full disclosure.

Everything is a string. Postgres stores all custom parameter values as text. There is no type enforcement whatsoever. If you SET myapp.limit = 'banana', Postgres will utter nary a single complaint. You may have noticed the manual casting through all of the examples with current_setting(...)::int.

There is no access control. Any session can SET any custom parameter. RLS policies that rely on current_setting('app.tenant_id') are only as secure as the application's control over SQL execution. Users with direct access can trivially SET app.tenant_id = '999' and bypass the policy. A more reasonable RLS-based security system might leverage CURRENT_USER or SESSION_USER and a mapping table to prevent such escape attempts.

History lesson: custom_variable_classes. Before PostgreSQL 9.2, custom parameters required pre-registration. It was necessary to list extra namespaces in postgresql.conf: 

# postgresql.conf (pre-9.2 only)
custom_variable_classes = 'myapp,audit'

Without this declaration, SET myapp.anything would raise an error. PostgreSQL 9.2 removed this restriction, and thus the freestyle dot syntax was born. If you encounter old documentation or Stack Overflow answers referencing custom_variable_classes, they're describing behavior from over a decade ago. Sadly, that's more common than it should be. 

They integrate with the full GUC machinery. Custom parameters aren't some bolted-on feature. They participate in everything the built-in parameters do: 

-- Set a default for a specific database.
ALTER DATABASE mydb SET myapp.app_name = 'Cows R Us';

-- Set a default for a specific user
ALTER USER web_app SET myapp.log_level = 'verbose';

-- Set in postgresql.conf (loaded at server start or reload).
myapp.default_timeout = '30'

-- Set in postgresql.auto.conf and reload to apply to all sessions.
-- This only works in 17 and above thanks to ALTER SYSTEM restrictions.
ALTER SYSTEM SET myapp.enabled TO 'off';
SELECT pg_reload_conf();

These defaults then act as the baseline that SET and set_config override at the session level, and RESET reverts to. It's the same layered configuration model that governs work_mem, search_path, and other GUCs. Custom parameters are first-class citizens of the Postgres configuration system, especially in recent releases.

Reduce, Reuse, Recycle

Postgres didn't forget to implement session variables. Why invent a separate subsystem for functionality that was there from the beginning? The configuration parameter system, originally designed for things like work_mem and timezone, turned out to be a natural fit for arbitrary session state once the custom namespace restriction was lifted in 9.2.

Does it lack the syntactic sugar of MySQL or SQL Server's @variable approach? There's no denying that. But it also avoids MySQL's type ambiguity, Oracle's client-side-only limitation, and SQL Server's batch-scoping limitation. There's always a compromise and no approach is truly perfect; Postgres straddles the line between power and convenience.

Either way, the next time someone claims Postgres can't do session variables, point them at set_config and current_setting. They've been around for a long time, but recent versions have made them much more approachable. And if you haven't given them a try yet, there's no time like the present!