"RETURNING OLD and NEW in PostgreSQL 18: Audit Logs in a Single Query"
update returning old value postgres — PG18's enhanced RETURNING clause returns OLD and NEW row values, enabling atomic single-query audit logs without triggers.
Before PostgreSQL 18, getting the old value in an UPDATE meant an extra SELECT before the write (racy under concurrency) or an audit trigger (extra moving parts on every table). PostgreSQL 18's enhanced RETURNING clause ends the workaround era: UPDATE ... RETURNING OLD.*, NEW.* hands you both versions of the row in one atomic statement. That makes update returning old value postgres queries the foundation for single-query audit logging — no triggers, no read-modify-write races. This guide shows the syntax, the data-modifying-CTE audit pattern, change detection, and where triggers still belong. Last updated: October 4, 2026 What you'll learn The PG18 enhanced RETURNING syntax: OLD , NEW , and output aliases How to get the old value in an UPDATE without a prior SELECT The single-query audit log pattern with data-modifying CTEs Detecting whether an UPDATE actually changed anything Trigger-based audit vs RETURNING-based audit: which to choose PostgreSQL 18 RETURNING OLD and NEW: The Syntax The RETURNING clause previously exposed only the post-statement row. In PostgreSQL 18, INSERT , UPDATE , DELETE , and MERGE can reference both row versions: -- Balances table for the examples CREATE TABLE accounts ( id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY, owner text NOT NULL, balance numeric(12,2) NOT NULL DEFAULT 0 ); -- OLD.* = row before the update, NEW.* = row after UPDATE accounts SET balance = balance - 100 WHERE id = 42 RETURNING OLD.balance AS old_balance, NEW.balance AS new_balance; -- old_balance | new_balance -- -------------+------------- -- 500.00 | 400.00 Key syntax points: OLD and NEW are available in RETURNING for UPDATE and DELETE (both versions exist: for DELETE , NEW is null-ish/absent — use OLD.* ; for INSERT , use NEW.* ). You can mix them…