PostgreSQL 18 OLD and NEW RETURNING: Cleaner Change Capture in SQL

PostgreSQL 18 makes a small SQL feature feel much more complete: RETURNING can now expose both the old and new version of changed rows across INSERT, UPDATE, DELETE, and MERGE. For application teams, that means fewer awkward follow-up queries when a write needs to return exactly what changed.

This is not a replacement for logical replication, triggers, or a full audit system. It is a practical improvement for the common path where an API, job, or migration updates rows and immediately needs a structured before-and-after result for logs, events, cache invalidation, or user feedback.

What changed in PostgreSQL 18

PostgreSQL has supported RETURNING for years, and many applications already depend on it to get generated IDs, defaults, computed columns, or updated values without a second round trip. PostgreSQL 18 extends that idea by making old and new row versions explicit in the RETURNING list. The release notes describe this as OLD and NEW support for RETURNING clauses in INSERT, UPDATE, DELETE, and MERGE.

The new syntax allows expressions such as old.price, new.price, old.*, and new.*. The command docs also allow aliases through RETURNING WITH (OLD AS o, NEW AS n) ..., which is useful when you want short names or need to avoid ambiguity in a larger statement.

The default behavior remains familiar. An UPDATE ... RETURNING price still returns the new value. A DELETE ... RETURNING * still returns the deleted row. The difference is that PostgreSQL can now return the other side of the change in the same statement when it exists.

Why this matters for production applications

The immediate win is correctness under concurrency. Before this feature, an application that wanted both old and new values often had to select the row, issue an update, and then return the updated row. That pattern can be safe when wrapped carefully in a transaction with the right locking, but it is easy to get wrong and easy to duplicate across services.

With PostgreSQL 18, the write statement itself can describe the change. For a pricing update, the database can return the product name, the prior price, the new price, and the computed delta as part of the UPDATE. That keeps the result tied to the rows the statement actually changed, not to a separate read that may drift from the write path.

This is especially useful in managed PostgreSQL environments where teams want simple application code and predictable database round trips. It can reduce custom trigger usage for lightweight change summaries, make migrations easier to verify, and help background workers emit precise events after bulk updates.

The table also shows the important limitation: old and new values depend on the action. PostgreSQL cannot return a new row for a plain delete because no new target row exists. It cannot return an old row for a plain insert because there was no previous target row. For upserts and MERGE, the details depend on which branch actually runs.

A practical UPDATE example

A common use case is returning a change summary from a single statement. PostgreSQL 18 documents this style directly:

UPDATE products
SET price = price * 1.10
WHERE price <= 99.99
RETURNING
  name,
  old.price AS old_price,
  new.price AS new_price,
  new.price - old.price AS price_change;

That result is compact enough for an application response, a migration log, or an internal event. It also avoids a fragile pattern where the application first reads candidate products, performs the update, and then tries to reconstruct which rows changed.

For larger statements, aliases can make the output clearer:

UPDATE subscriptions
SET plan_name = 'pro', updated_at = now()
WHERE plan_name = 'starter'
RETURNING WITH (OLD AS before, NEW AS after)
  after.id,
  before.plan_name AS previous_plan,
  after.plan_name AS current_plan,
  after.updated_at;

The best outputs are intentional. Returning old.* and new.* is convenient during development, but production code usually benefits from returning the fields that downstream systems actually need. That keeps network payloads smaller and avoids accidentally exposing columns that were not meant to leave the database layer.

MERGE gets more observable

MERGE is where this feature can be especially helpful because one statement may insert, update, delete, or do nothing depending on each source row. PostgreSQL 18's MERGE docs describe RETURNING expressions that can use source columns, target columns, old and new target values, and the merge_action() function.

That makes reconciliation jobs easier to inspect. A data sync can return whether a customer row was inserted or updated, which source identifier caused the change, and which target values changed. For operations teams, this is easier to reason about than a job that only reports a count at the end.

The main caution is payload size. The docs note that MERGE RETURNING * can duplicate a lot of data because it may include source columns followed by target columns. In most application code, a narrow explicit return list is better than returning every column from both sides.

Where this does not replace dedicated tooling

OLD and NEW RETURNING is a statement-level convenience, not a durable change data capture stream. If another system must consume every database change, logical replication, a transactional outbox, or a trigger-backed audit table may still be the right design. A client only receives RETURNING rows when it executes that statement and handles the result successfully.

It also does not remove the need for authorization and data-minimization checks. If an application role can update sensitive rows and return sensitive columns, the new syntax can make it easier to expose those values accidentally. Treat return lists as part of the API contract. Return the few columns needed for the caller, and keep privileged maintenance statements separate from user-facing write paths.

Finally, test driver and ORM support before adopting this broadly. Raw SQL paths can use the feature immediately once the database runs PostgreSQL 18, but query builders may need updates to model old, new, or RETURNING WITH syntax cleanly.

How to adopt it during a PostgreSQL 18 upgrade

Start with write paths that already use RETURNING and have awkward post-write reads. Good candidates are account settings changes, billing plan updates, inventory adjustments, and admin actions that need a clear audit entry. Replace the extra read with a narrow RETURNING list, then compare the application behavior in staging.

For migrations, use the feature as an observability aid rather than dumping every changed row. A migration that updates a small lookup table can return before-and-after values directly. A migration that touches millions of rows should usually aggregate validation separately, because returning every modified row can create unnecessary client memory and network pressure.

If you are planning a managed PostgreSQL upgrade, combine this with the normal PostgreSQL 18 checks: review extension compatibility, test application SQL against a staging clone, and verify pooling behavior. ArmorDB teams using PgBouncer should also keep result handling explicit; pooling changes connection reuse, not the semantics of one SQL statement, but large returned result sets can still affect application workers.

The related ArmorDB guide on PostgreSQL 18 pg_upgrade statistics is useful if you are planning a major-version upgrade and want to understand another PostgreSQL 18 change that reduces post-upgrade surprises.

Takeaway

PostgreSQL 18's OLD and NEW RETURNING support is a practical quality-of-life improvement for applications that care about precise write results. It keeps before-and-after values attached to the statement that changed them, makes MERGE easier to observe, and can remove extra queries from common API and job code.

Use it where a caller truly needs the change summary. Keep return lists narrow, test ORM support, and leave durable audit trails or cross-system change feeds to tools designed for that job.

Sources and further reading

• PostgreSQL 18 release notes
• PostgreSQL 18 announcement
• PostgreSQL documentation: returning data from modified rows
• PostgreSQL documentation: UPDATE
• PostgreSQL documentation: MERGE