How would you use PostgreSQL 18 OLD and NEW in RETURNING?

Problem and context

PostgreSQL 18 lets RETURNING explicitly expose old and new row values for INSERT, UPDATE, DELETE, and MERGE. The result is produced by the same data-changing statement, which can avoid a follow-up query that races with another writer. The old or new side can be NULL when that side does not exist for an operation.

Assume audit events need the exact values changed by one statement, retries must be idempotent, and the application cannot afford a second read between mutation and audit emission.

What interviewers evaluate

Interviewers look for statement-level atomicity, correct operation semantics, and a plan for transaction boundaries and retries. Strong answers distinguish INSERT, UPDATE, DELETE, ON CONFLICT, and MERGE, and explain where audit delivery belongs relative to commit.

An ordinary answer selects the row again after the write. A strong answer uses RETURNING as the mutation output, preserves operation identity, and avoids publishing an event that the transaction later rolls back.

Questions to clarify first

• Must the audit event be emitted only after commit, or is an outbox row sufficient?
• Can one statement change many rows, and how are event IDs assigned?
• What does “old” mean for an upsert conflict and for each MERGE action?
• How will retries avoid duplicate audit events?
• Are there columns that must be redacted before leaving the database?

If downstream delivery must follow commit, insert an outbox row in the same transaction and publish asynchronously. If the audit is only for an internal SQL report, RETURNING can be consumed directly by the caller.

A 30-second answer

“I would make the mutation statement return an explicit operation, old values, new values, and a stable event key. For multi-row writes I would insert those results into an outbox in the same transaction, then publish after commit. I would test the NULL side for inserts and deletes, define upsert and MERGE semantics, redact sensitive columns, and make retries idempotent using the event key.”

Step-by-step design

1. Define the event contract. Include table identity, primary key, operation, old projection, new projection, transaction or request ID, and an idempotency key.
2. Use explicit aliases. Write RETURNING WITH (OLD AS old_row, NEW AS new_row) or the equivalent documented syntax instead of relying on RETURNING *.
3. Handle operation semantics. Inserts normally have no old row; deletes normally have no new row. Upserts and MERGE need a branch-specific operation value.
4. Persist atomically. Insert the returned records into an outbox within the same transaction. A separate read or external publish before commit can observe state that later rolls back.
5. Protect data and retries. Redact fields, hash sensitive values where appropriate, and enforce a unique event key so a retried statement does not duplicate delivery.
6. Verify concurrency. Run concurrent writers, conflicts, rollbacks, multi-row statements, and partial MERGE branches. Compare audit rows with committed table state.

Alternatives include triggers for central enforcement, logical decoding for database-wide capture, or application events for domain semantics. RETURNING is strongest when the mutation already owns the precise row-level change.

Example answer

“The application update returns an explicit operation, primary key, old projection, new projection, and event key. For an upsert conflict I label the result as update; for MERGE, each branch supplies its own operation. The transaction inserts every returned row into an outbox and commits once. A worker publishes after commit with a unique event key and retries safely. Deletes have a null new projection, inserts a null old projection, and sensitive columns are removed before the event leaves the database.”

Common mistakes

• Error: Re-querying after the mutation → Why it fails: another writer can change the row → Fix: consume RETURNING from the same statement.
• Error: Publishing before commit → Why it fails: an audit event can describe rolled-back data → Fix: use a transactional outbox.
• Error: Treating every upsert as insert → Why it fails: conflict updates need different semantics → Fix: emit an explicit operation.
• Error: Returning all columns blindly → Why it fails: secrets or personal data leak → Fix: use an allow-list projection and redaction.

Follow-up questions and responses

What does OLD contain for an INSERT?

Normally no prior row exists, so the old side is NULL. The event contract should model that absence rather than inventing a default row.

How do you capture a multi-row MERGE?

Consume one RETURNING result per affected row, include the branch operation, and insert each event into the same outbox transaction.

What if the outbox insert fails?

The transaction should fail and roll back the mutation. Do not acknowledge the business write while silently dropping its audit record.

When would you prefer logical decoding?

Use logical decoding for broad database-wide change capture or systems that cannot modify statements. Prefer RETURNING when the application needs domain-aware projections and precise per-statement semantics.