Module 9 — The RETURNING Clause Evolved: OLD/NEW Aliases and Eliminating Race Conditions
What this module covers: PostgreSQL 18 introduces OLD and NEW aliases in the RETURNING clause of UPDATE and DELETE statements. This sounds like a minor syntactic addition. It is not. It eliminates entire classes of application-level race conditions that previously required either two round trips, advisory locks, or explicit table locks to handle safely. This module explains the problem, the pre-18 workarounds and their costs, and the precise semantics of OLD/NEW in RETURNING.
The Problem: Atomic Read-Modify-Write
A large fraction of application-database interactions follow this pattern:
Read the current value of a row
Compute a new value based on the old value
Write the new value back
The canonical example: decrementing an account balance.
sql
This is a classic read-modify-write race condition. Between the SELECT and the UPDATE, another transaction can modify the balance. Two concurrent withdrawals of $100 from an account with $150 can both read $150, both compute $50 as the new balance, and both write $50 — leaving the account with $50 instead of the correct $-50 (or a rejected second withdrawal).
The correct SQL-level solution is an atomic update:
sql
This executes atomically. No race condition. But it discards information: what was the balance before the update?
In many real-world cases, you need the previous value to make a decision in the application:
Was the balance sufficient? (if old balance < 100, reject the transaction)
What changed? (for audit logs, event sourcing, change feeds)
What was the previous state? (for optimistic concurrency control)
Did the row actually exist before? (to distinguish "updated" from "not found")
Pre-PG18 Workarounds
Workaround 1: Two Round Trips with Locking
sql
The cost:
Two database round trips (SELECT + UPDATE) instead of one
FOR UPDATE acquires a row-level exclusive lock — concurrent reads are not blocked, but concurrent writes on the same row queue up
Lock held for the full duration of application processing between the SELECT and UPDATE
Deadlock risk if multiple rows are locked in different orders by concurrent transactions
Workaround 2: CTE with Data-Modifying CTE
sql
This is one round trip, but you're computing the old value from the new value. For additive changes (balance - 100), this is trivial. For complex transformations, it becomes error-prone. And for non-invertible changes (setting a value to a computed result from another table), it is impossible.
Workaround 3: BEFORE Trigger to Capture Old Value
sql
The cost:
Extra table, extra trigger, extra INSERT on every update
The old value is in a separate table — application must join to retrieve it
Trigger maintenance overhead (documented in Module 4 — triggers prevent HOT updates)
Not usable for ad-hoc queries; requires schema setup in advance
Workaround 4: Subquery in RETURNING
sql
This does not work correctly. The subquery in RETURNING sees the post-update state of the row (in the same transaction). balance in the subquery returns the updated value, not the original. This is a commonly attempted workaround that silently produces wrong results.
PostgreSQL 18: OLD and NEW in RETURNING
PG18 solves this cleanly. UPDATE and DELETE statements can now use OLD.column and NEW.column aliases in the RETURNING clause to access both the pre-update and post-update values of any column in a single statement.
Basic Syntax
sql
One round trip. Atomic. No locks beyond what the UPDATE itself requires. No reverse-engineering the old value. No triggers.
DELETE with OLD
DELETE only has OLD — there is no NEW row after deletion.
sql
Returns every deleted row's pre-deletion state. Useful for:
Audit logging of what was deleted
Triggering downstream cleanup based on the deleted row's values
Confirming which specific rows were deleted (when the WHERE clause could match multiple)
Unqualified Columns in RETURNING
For backward compatibility, unqualified column names in RETURNING continue to refer to NEW for UPDATE and OLD for DELETE:
sql
Prefer explicit OLD. / NEW. in new code — it makes intent unambiguous and is self-documenting.
Patterns Enabled by OLD/NEW RETURNING
Pattern 1: Atomic Conditional Update with Decision
The most common use case: update a row only if a condition is met, and know in the application whether the condition was satisfied.
sql
Before PG18, distinguishing "row not found" from "condition failed" required either a separate existence check or careful result parsing. Now: zero rows means the condition failed (or the row doesn't exist), one row means success — and you have full before/after context.
Pattern 2: Optimistic Concurrency Control
Optimistic concurrency uses a version counter or timestamp to detect concurrent modifications without holding locks.
sql
The application gets confirmation of both the version it expected and the version that was written — useful for logging and debugging concurrent modification conflicts.
Pattern 3: State Machine Transitions
Many business objects have a state machine — pending → processing → completed. A common requirement: only transition if the current state is the expected state, and know what state it was in.
sql
Before PG18, this pattern required either a SELECT FOR UPDATE to check the current status before updating, or inferring success from update count alone (which doesn't tell you if the condition failed or the row was missing).
Pattern 4: Audit Log from the Update Itself
Instead of a trigger (which prevents HOT updates and adds schema complexity), generate an audit log entry from the RETURNING clause directly in the application:
sql
The entire operation — update the account, log the change — in a single transaction, with no trigger, no extra round trip, and no HOT update penalty. If the update fails (insufficient balance), no audit row is inserted. Atomicity is guaranteed by the transaction.
Pattern 5: Soft Delete with History
sql
The application receives the pre-deletion profile in one round trip, can write it to a data warehouse or archive table, without any additional reads.
What OLD/NEW Does NOT Cover
INSERT: Only NEW Exists
INSERT ... RETURNING only has NEW (the inserted row). There is no OLD for inserts — the row didn't exist before.
sql
UPSERT (INSERT ON CONFLICT): Full Access
INSERT ... ON CONFLICT DO UPDATE ... RETURNING gains the most from OLD/NEW:
sql
When the ON CONFLICT DO UPDATE branch executes, OLD contains the pre-upsert values. When the INSERT branch executes (no conflict), OLD columns are NULL — the row didn't exist before. The application can distinguish insert vs update from OLD.login_count IS NULL.
Triggers Still See OLD/NEW Separately
Row-level triggers have always had OLD and NEW records. PG18's RETURNING OLD/NEW is a separate feature — it gives the SQL layer the same access that trigger functions have always had internally. The two mechanisms are independent.
The Concurrency Guarantee
The OLD values returned by RETURNING OLD are the values that existed in the row at the moment the row lock was acquired for the UPDATE or DELETE, which is the same moment the update itself executes. This means:
OLD reflects the committed state of the row just before this transaction modified it
No other transaction can have modified the row between when OLD was captured and when NEW was written — the row lock prevents it
The old-to-new transition is atomic from every other transaction's perspective
This is a stronger guarantee than SELECT ... FOR UPDATE + UPDATE in two round trips, because with two round trips there is always the question of whether the application correctly used the locked value. With RETURNING OLD, the database captures and returns the old value as part of the single atomic operation — no application logic is in the critical path between read and write.
Summary
Scenario
Before PG18
After PG18
Get old value after UPDATE
SELECT FOR UPDATE + UPDATE (2 RTT)
RETURNING OLD.column (1 RTT)
Conditional update + decision
2 RTT + lock
1 RTT, 0 rows = condition failed
Detect concurrent modification
Advisory locks or version check loop
RETURNING OLD.version in one statement
Audit log without trigger
Trigger (prevents HOT)
CTE with RETURNING OLD into audit table
State machine transition
SELECT FOR UPDATE + check + UPDATE
WHERE status = 'pending' RETURNING OLD.status
Soft delete with profile
SELECT + UPDATE (2 RTT)
RETURNING OLD.* (1 RTT)
UPSERT: insert vs update
Check affected rows + re-query
RETURNING OLD.column IS NULL → was insert
The principle: any operation that previously required reading before writing to know what changed can now be expressed as a single atomic statement. The database captures the transition — both the before and after state — and returns it to the application as part of the write operation itself.
Module 10 closes the core architecture loop with replication and high availability — the CAP trade-offs, the consistency models of each replication mode, and the operational decisions that separate a database that survives incidents from one that doesn't.
Next: Module 10 — Replication, High Availability, and the CAP Trade-offs →
Knowledge Check
A financial application needs to atomically debit an account, but also requires the account's balance *before* and *after* the debit for auditing and to determine if the transaction was successful (i.e., sufficient funds existed). The application must minimize database round trips and avoid deadlocks in a high-concurrency environment. Which approach, leveraging PostgreSQL 18 features, provides the most robust and performant solution?
A Senior Software Engineer is designing a critical state machine transition for a jobs table, where a job can only move from pending to processing if it hasn't already been claimed by another worker. They need to ensure atomicity and retrieve the previous_status and current_status in a single database interaction. They are considering RETURNING OLD.status, NEW.status in their UPDATE statement. What is the fundamental concurrency guarantee provided by OLD values in this PostgreSQL 18 RETURNING clause?
An application uses an UPSERT statement (INSERT ... ON CONFLICT DO UPDATE ...) to track user login statistics, incrementing login_count and updating last_login. The application needs to know if a new user record was created or an existing one was updated, and in the latter case, retrieve the previous_login_count and current_login_count. Which RETURNING clause and application logic correctly achieves this, and what would be the expected output for a newly inserted user?
Test your knowledge with more question sets
Sign in to access a wider variety of questions and get notified when new practice sets are added to this module.
-- Application code (naive, broken under concurrency):SELECT balance FROM accounts WHERE id = $1;-- application computes: new_balance = balance - 100UPDATE accounts SET balance = $2WHERE id = $1;
UPDATE accounts SET balance = balance -100WHERE id = $1;
BEGIN;-- Lock the row so no one else can modify it between SELECT and UPDATESELECT balance FROM accounts WHERE id = $1FORUPDATE;-- Application checks: if balance < 100, ROLLBACK and return error-- Otherwise:UPDATE accounts SET balance = balance -100WHERE id = $1;COMMIT;
WITH updated AS(UPDATE accounts
SET balance = balance -100WHERE id = $1RETURNING id, balance
)SELECT balance AS new_balance, balance +100AS old_balance -- reverse-engineer the old valueFROM updated;
CREATETABLE balance_changes ( account_id BIGINT, old_balance NUMERIC, new_balance NUMERIC, changed_at TIMESTAMPTZ DEFAULTnow());CREATEFUNCTION capture_balance_change()RETURNSTRIGGERAS $$
BEGININSERTINTO balance_changes VALUES(OLD.id, OLD.balance, NEW.balance,now());RETURN NEW;END;$$ LANGUAGE plpgsql;CREATETRIGGER before_balance_update
BEFORE UPDATEON accounts
FOR EACH ROWEXECUTEFUNCTION capture_balance_change();
UPDATE accounts
SET balance = balance -100WHERE id = $1RETURNING balance AS new_balance,(SELECT balance FROM accounts WHERE id = $1)AS old_balance;
-- Return both old and new balance in one atomic UPDATEUPDATE accounts
SET balance = balance -100WHERE id = $1RETURNING OLD.balance AS balance_before, NEW.balance AS balance_after, NEW.id;-- Result:-- balance_before | balance_after | id-- ---------------+---------------+------ 1500 | 1400 | 42
DELETEFROM sessions
WHERE user_id = $1AND expires_at <now()RETURNING OLD.id AS deleted_session_id, OLD.created_at AS session_created, OLD.expires_at AS session_expired;
-- These are equivalent for UPDATE:RETURNING balance -- refers to NEW.balance (post-update)RETURNING NEW.balance -- explicit-- For DELETE:RETURNING balance -- refers to OLD.balance (pre-delete)RETURNING OLD.balance -- explicit
-- Debit an account only if balance is sufficient-- Return both old and new balance so the application can decide what happenedUPDATE accounts
SET balance = balance - $2WHERE id = $1AND balance >= $2RETURNING OLD.balance AS balance_before, NEW.balance AS balance_after;-- If the WHERE clause fails (balance < amount):-- → 0 rows returned → application knows: insufficient funds, no change made-- If it succeeds:-- → 1 row with balance_before=1500, balance_after=1400
-- Schema: version column incremented on every updateALTERTABLE documents ADDCOLUMN version BIGINTNOTNULLDEFAULT1;-- Application reads: document with version = 7-- Application modifies content-- Application writes: only update if version is still 7UPDATE documents
SET content = $1, version = version +1WHERE id = $2AND version = $3-- $3 = 7, the version the application readRETURNING OLD.version AS expected_version, NEW.version AS current_version, NEW.content;-- 0 rows returned → concurrent modification detected-- application must re-read and retry-- 1 row returned with NEW.version = 8 → success
-- Claim a job for processing (worker picks up a pending job)UPDATE jobs
SETstatus='processing', worker_id = $1, started_at =now()WHERE id = $2ANDstatus='pending'RETURNING OLD.statusAS previous_status, NEW.statusAS current_status, NEW.worker_id, NEW.started_at;-- 0 rows → job was already claimed by another worker, or doesn't exist-- 1 row with previous_status='pending' → this worker successfully claimed it
WITH balance_update AS(UPDATE accounts
SET balance = balance - $2WHERE id = $1AND balance >= $2RETURNING OLD.id, OLD.balance AS old_balance, NEW.balance AS new_balance,now()AS changed_at
)INSERTINTO audit_log (account_id, old_balance, new_balance, changed_at, operation)SELECT id, old_balance, new_balance, changed_at,'debit'FROM balance_update;
-- Soft delete: mark as deleted, return what was deleted for archivingUPDATE users
SET deleted_at =now(), deleted_by = $1WHERE id = $2AND deleted_at ISNULLRETURNING OLD.email AS email_before_delete, OLD.plan_tier AS plan_at_deletion, OLD.created_at AS account_created, NEW.deleted_at AS deleted_at, NEW.deleted_by AS deleted_by;
INSERTINTO accounts (user_id, balance)VALUES($1,0)RETURNING NEW.id, NEW.balance, NEW.created_at;-- or simply:RETURNING id, balance, created_at;-- unqualified = NEW for INSERT
INSERTINTO account_stats (user_id, login_count, last_login)VALUES($1,1,now())ON CONFLICT (user_id)DOUPDATESET login_count = account_stats.login_count +1, last_login =now()RETURNING OLD.login_count AS previous_count,-- NULL if this was an INSERT (no old row) NEW.login_count AS current_count, OLD.last_login AS previous_login, NEW.last_login AS current_login;