---
title: 'Postgres MERGE RETURNING: Old and New Values (2026)'
description: Postgres 18 lets MERGE RETURNING return old and new row values in one query. See merge_action(), the xmax alternative, and verified Node.js/TypeScript code.
url: https://nerdleveltech.com/postgres-merge-returning-old-new-values
language: en
date: '2026-07-02'
category: backend
tags:
  - postgresql
  - postgres 18
  - merge
  - upsert
  - sql
  - node-postgres
  - typescript
  - backend
markdown_url: https://nerdleveltech.com/postgres-merge-returning-old-new-values.md
attribution: 'Source: NerdLevelTech — https://nerdleveltech.com/postgres-merge-returning-old-new-values'
terms: https://nerdleveltech.com/llms.txt
---

# Postgres MERGE RETURNING: Old and New Values (2026)

Postgres MERGE gained a RETURNING clause in version 17, and version 18 added special OLD and NEW aliases that expose both the previous and current row values in one query — replacing the undocumented xmax = 0 trick for telling whether a row was inserted or updated.[^mergedoc][^release18]

## TL;DR

`MERGE ... RETURNING` has existed since Postgres 17. What's new in **Postgres 18** (released September 25, 2025, current minor 18.4) is the ability to write `OLD.column` and `NEW.column` — or `OLD.*` / `NEW.*` — inside that RETURNING clause, for `INSERT`, `UPDATE`, `DELETE`, *and* `MERGE`.[^versioning][^release18] Combined with `merge_action()`, which reports `'INSERT'`, `'UPDATE'`, or `'DELETE'` for each affected row, you can build a full upsert audit trail — old value, new value, and which action happened — in a single statement, with no triggers and no follow-up `SELECT`.[^mergedoc] Every query and error message in this post was run against a live, locally-installed PostgreSQL 18.4 instance rather than typed from memory.

## What you'll learn

- The real difference between `MERGE` and `INSERT ... ON CONFLICT` (upsert)
- Since which Postgres version `MERGE` supports `RETURNING` at all
- How to tell whether `MERGE` inserted or updated a row, using `merge_action()`
- How to return both the old and new values from one `MERGE` or upsert statement
- Whether you still need the `xmax = 0` trick in Postgres 18
- How to build a trigger-free audit trail from a single `MERGE` statement
- What error Postgres raises if your source data has duplicate keys
- How to consume `OLD`/`NEW` RETURNING columns from Node.js with `pg` and TypeScript

## What's the difference between Postgres MERGE and INSERT ... ON CONFLICT (upsert)?

`INSERT ... ON CONFLICT DO UPDATE` (the classic "upsert") always starts from an `INSERT`: if a unique constraint is violated, it falls back to an `UPDATE`. `MERGE` is more general — it joins a target table to a source using an arbitrary `ON` condition and lets you branch into `WHEN MATCHED`, `WHEN NOT MATCHED`, and (since Postgres 17) `WHEN NOT MATCHED BY SOURCE`, so it can insert, update, *and* delete in one statement.[^mergedoc] `MERGE` was added in Postgres 15 (released October 13, 2022); `INSERT ... ON CONFLICT` has existed since Postgres 9.5.[^pg15]

```sql
-- Classic upsert: always an INSERT, falls back to UPDATE on conflict
INSERT INTO webhook (id, data)
VALUES (1, 'payload')
ON CONFLICT (id) DO UPDATE SET data = EXCLUDED.data;

-- MERGE: an explicit join with independent branches per outcome
MERGE INTO products p
USING staging s ON p.product_code = s.product_code
WHEN MATCHED THEN
  UPDATE SET product_name = s.product_name, price = s.price, stock_quantity = s.stock_quantity
WHEN NOT MATCHED THEN
  INSERT (product_code, product_name, price, stock_quantity)
  VALUES (s.product_code, s.product_name, s.price, s.stock_quantity);
```

If you're syncing a whole staging table against a target table — the classic ETL/data-sync case — `MERGE` is usually the better fit, because the join condition can match on anything, not just a unique constraint.

## Does Postgres MERGE support a RETURNING clause?

Yes, since Postgres 17. `RETURNING` on `MERGE` computes its output from the source row plus the target row that was inserted, updated, or deleted, and it accepts the special `merge_action()` function to report which action ran.[^mergedoc] On Postgres 15 or 16, `MERGE` runs fine but `RETURNING` is not accepted — you'll get a syntax error and need a separate `SELECT` to see the result.

```sql
MERGE INTO products p
USING staging s ON p.product_code = s.product_code
WHEN MATCHED THEN
  UPDATE SET product_name = s.product_name, price = s.price, stock_quantity = s.stock_quantity
WHEN NOT MATCHED THEN
  INSERT (product_code, product_name, price, stock_quantity)
  VALUES (s.product_code, s.product_name, s.price, s.stock_quantity)
RETURNING merge_action() AS action, p.product_code, p.price;
```

Run against a live PostgreSQL 18.4 instance with one existing row (`LAPTOP-001`) and two staging rows (an update to `LAPTOP-001` and a brand-new `MONITOR-001`), this returns:

```text
 action | product_code | price
--------+--------------+---------
 UPDATE | LAPTOP-001   | 1099.99
 INSERT | MONITOR-001  |  399.99
```

## How do I know if MERGE performed an INSERT or an UPDATE?

Add `merge_action()` to the `RETURNING` list. It's a special function, only valid inside a `MERGE ... RETURNING` clause, that evaluates to the literal string `'INSERT'`, `'UPDATE'`, or `'DELETE'` depending on which branch fired for that row.[^mergedoc] This has been available since the same Postgres 17 release that added `RETURNING` to `MERGE` — you do not need Postgres 18 just to know which action ran; you need 18 to see the old and new *values* alongside it (next section).

```sql
RETURNING merge_action() AS action, p.product_code;
```

Tested output for the same run as above: `LAPTOP-001` reports `action = 'UPDATE'`, `MONITOR-001` reports `action = 'INSERT'` — exactly matching which branch of the `WHEN MATCHED` / `WHEN NOT MATCHED` clause executed for each row.

## How do I get both the old and new values from one upsert or MERGE query?

Use the `OLD` and `NEW` aliases inside `RETURNING`, added in Postgres 18. `OLD.column` (or `OLD.*`) returns the row's value *before* the statement ran; `NEW.column` (or `NEW.*`) returns it *after*. This works for plain `UPDATE`/`DELETE`/`INSERT` too, not just `MERGE` — and it works for classic `INSERT ... ON CONFLICT` upserts as well.[^release18]

```sql
MERGE INTO products p
USING staging s ON p.product_code = s.product_code
WHEN MATCHED THEN
  UPDATE SET product_name = s.product_name, price = s.price, stock_quantity = s.stock_quantity
WHEN NOT MATCHED THEN
  INSERT (product_code, product_name, price, stock_quantity)
  VALUES (s.product_code, s.product_name, s.price, s.stock_quantity)
RETURNING
  merge_action()                            AS action,
  COALESCE(new.product_code, old.product_code) AS product_code,
  old.price                                 AS old_price,
  new.price                                 AS new_price,
  (old.price IS DISTINCT FROM new.price)    AS price_changed;
```

Verified output:

```text
 action | product_code | old_price | new_price | price_changed
--------+--------------+-----------+-----------+---------------
 UPDATE | LAPTOP-001   |    999.99 |   1099.99 | t
 INSERT | MONITOR-001  |           |    399.99 | t
```

Note that `MONITOR-001` — a genuinely new row — has a blank (`NULL`) `old_price`, since there was no "before" state, but `price_changed` still comes back `t` (true), not `NULL`. That's because `IS DISTINCT FROM` is null-safe: unlike the plain `=` operator, which returns `NULL` (neither true nor false) whenever either side is `NULL`, `IS DISTINCT FROM` always returns a real boolean and treats `NULL` as distinct from any non-`NULL` value. So `NULL IS DISTINCT FROM 399.99` correctly evaluates to `true` — you get a clean "this row changed" signal for inserts too, with no `COALESCE` or extra branching required.

If your identifiers collide with `old`/`new` (or you're inside a trigger function where those names are already taken), rename the aliases:

```sql
RETURNING WITH (OLD AS o, NEW AS n) o.price AS old_price, n.price AS new_price;
```

Tested identically to the default aliases — this is purely a naming convenience.

## Do I still need the xmax trick to detect a new row in Postgres 18?

No, but it still works if you're stuck on an older version. Before Postgres 18, the common way to tell whether an upserted row was new was to check the internal `xmax` system column: `(xmax = 0) AS is_new`, since a freshly inserted row has no row lock and therefore `xmax = 0`.[^mergedoc] This works, but it relies on an internal system column that PostgreSQL's own documentation says applications "do not really need to be concerned about" — it's exposed for internal MVCC bookkeeping, not documented as a stable API for detecting new rows.[^syscols]

Both were run back-to-back against the same live database for this post, on the same table, to confirm they agree:

```sql
-- Old way (works on Postgres 9.5+, still works on 18)
INSERT INTO webhook (id, data) VALUES (3, 'xmax-test')
ON CONFLICT (id) DO UPDATE SET data = EXCLUDED.data
RETURNING id, (xmax = 0) AS is_new_xmax;
-- -> is_new_xmax: true (fresh row)

-- New way (Postgres 18+, no internal-column dependency)
INSERT INTO webhook (id, data) VALUES (2, 'brand-new')
ON CONFLICT (id) DO UPDATE SET data = EXCLUDED.data
RETURNING id, old.data AS old_data, (old IS NULL) AS is_new;
-- -> old_data: null, is_new: true
```

`(old IS NULL) AS is_new` is the Postgres 18 replacement: readable, documented, and it doesn't depend on how row-locking happens to be implemented internally.

## How do I build an audit trail from an upsert without triggers?

Wrap the `MERGE ... RETURNING` in a CTE and feed its output straight into an `INSERT` on your audit table — no trigger required, and it stays inside the same transaction as the original write.

```sql
CREATE TABLE product_audit (
  audit_id    SERIAL PRIMARY KEY,
  product_code VARCHAR(50),
  action      VARCHAR(10),
  old_values  JSONB,
  new_values  JSONB,
  changed_at  TIMESTAMPTZ DEFAULT now()
);

WITH merge_results AS (
  MERGE INTO products p
  USING staging s ON p.product_code = s.product_code
  WHEN MATCHED THEN
    UPDATE SET product_name = s.product_name, price = s.price, stock_quantity = s.stock_quantity
  WHEN NOT MATCHED THEN
    INSERT (product_code, product_name, price, stock_quantity)
    VALUES (s.product_code, s.product_name, s.price, s.stock_quantity)
  RETURNING
    merge_action() AS action,
    COALESCE(new.product_code, old.product_code) AS product_code,
    CASE WHEN old.product_code IS NOT NULL
      THEN jsonb_build_object('name', old.product_name, 'price', old.price, 'stock', old.stock_quantity)
    END AS old_values,
    CASE WHEN new.product_code IS NOT NULL
      THEN jsonb_build_object('name', new.product_name, 'price', new.price, 'stock', new.stock_quantity)
    END AS new_values
)
INSERT INTO product_audit (product_code, action, old_values, new_values)
SELECT product_code, action, old_values, new_values FROM merge_results
RETURNING product_code, action, old_values, new_values;
```

Verified result for the same two-row sync used throughout this post:

```json
[
  {
    "product_code": "LAPTOP-001",
    "action": "UPDATE",
    "old_values": { "name": "Premium Laptop", "price": 999.99, "stock": 50 },
    "new_values": { "name": "Premium Laptop Pro", "price": 1099.99, "stock": 45 }
  },
  {
    "product_code": "MONITOR-001",
    "action": "INSERT",
    "old_values": null,
    "new_values": { "name": "4K Monitor", "price": 399.99, "stock": 75 }
  }
]
```

`MONITOR-001` correctly shows `action: "INSERT"` with `old_values: null` — worth calling out explicitly, because a similar published example on a Postgres vendor's blog gets this exact case wrong, showing `"UPDATE"` for a row its own setup data proves was newly inserted.[^pgedge] Guard the `jsonb_build_object` calls with a `CASE WHEN ... IS NOT NULL` check as shown above; calling `jsonb_build_object` unconditionally on `NULL` source columns still produces a JSON object with `null` values inside it rather than a clean SQL `NULL`.

## Which Postgres version do I actually need for this?

There are three separate feature gates, and mixing them up is the most common source of "why doesn't this work" confusion:

| Feature | Minimum version | Released |
|---|---|---|
| `MERGE` statement itself | Postgres 15 | October 13, 2022[^pg15] |
| `RETURNING` clause on `MERGE` | Postgres 17 | September 26, 2024[^versioning] |
| `OLD`/`NEW` aliases in `RETURNING` (incl. on `MERGE`) | Postgres 18 | September 25, 2025[^versioning] |

If you're on Postgres 15 or 16, `MERGE` works but `RETURNING` does not — run a separate `SELECT` afterward if you need to see results. If you're on Postgres 17, `MERGE ... RETURNING` and `merge_action()` work, but `OLD`/`NEW` aliases do not exist yet — you're back to the `xmax` trick or a manual before/after `SELECT`. Postgres 18.4 (the current minor release as of this post) also fixed a `MERGE`-specific bug where concurrent updates under `REPEATABLE READ` or `SERIALIZABLE` isolation weren't correctly reported as serialization failures — worth knowing if you run `MERGE` under strict isolation levels.[^release18-4]

## What happens if my source data has duplicate keys matching the same target row?

Postgres raises an error rather than silently picking one. `MERGE` guarantees each target row is affected at most once per statement; if two or more source rows match the same target row, the statement fails.

```sql
CREATE TABLE src (id INT, val TEXT);
INSERT INTO src VALUES (1, 'x'), (1, 'y'); -- two source rows, same id

MERGE INTO tgt t USING src s ON t.id = s.id
WHEN MATCHED THEN UPDATE SET val = s.val
WHEN NOT MATCHED THEN INSERT (id, val) VALUES (s.id, s.val);
```

Tested error, verbatim from a live Postgres 18.4 instance:

```text
ERROR:  MERGE command cannot affect row a second time
```

Deduplicate your source (or add a tie-breaker to the join condition) before running `MERGE`. `INSERT ... ON CONFLICT DO UPDATE` has the identical restriction and raises an almost identical error — `ON CONFLICT DO UPDATE command cannot affect row a second time` — if two rows in the same `VALUES` list conflict on the same target row; confirmed by running both statements back-to-back against a live Postgres 18.4 instance. `ON CONFLICT DO NOTHING`, by contrast, does not error on duplicates — it silently applies the first matching row and skips the rest.

## How do I read this from Node.js and TypeScript with the pg driver?

Query it like any other statement with `pg` (`npm view pg` currently reports `8.22.0` as latest), but watch two gotchas that don't show up in pure-SQL examples: `NUMERIC` columns returned directly (not wrapped in JSON) come back as **strings**, and `merge_action()` needs a plain string type, not a Postgres enum.

```bash
npm install pg
npm install -D @types/pg typescript
```

```typescript
import { Client, type QueryResult } from 'pg';

interface MergeAuditRow {
  action: 'INSERT' | 'UPDATE' | 'DELETE';
  product_code: string;
  old_price: string | null; // NUMERIC comes back as a string from node-postgres
  new_price: string | null;
  price_changed: boolean; // IS DISTINCT FROM is null-safe — never SQL NULL
}

const client = new Client({ connectionString: process.env.DATABASE_URL });
await client.connect();

const result: QueryResult<MergeAuditRow> = await client.query(`
  MERGE INTO products p
  USING staging s ON p.product_code = s.product_code
  WHEN MATCHED THEN
    UPDATE SET product_name = s.product_name, price = s.price, stock_quantity = s.stock_quantity
  WHEN NOT MATCHED THEN
    INSERT (product_code, product_name, price, stock_quantity)
    VALUES (s.product_code, s.product_name, s.price, s.stock_quantity)
  RETURNING
    merge_action() AS action,
    COALESCE(new.product_code, old.product_code) AS product_code,
    old.price AS old_price,
    new.price AS new_price,
    (old.price IS DISTINCT FROM new.price) AS price_changed;
`);

for (const row of result.rows) {
  const oldPrice = row.old_price ? Number(row.old_price) : null;
  const newPrice = row.new_price ? Number(row.new_price) : null;
  console.log(row.action, row.product_code, oldPrice, '->', newPrice);
}
```

Confirmed directly against a live query: `old_price`/`new_price` selected as bare `NUMERIC` columns arrive as JSON strings like `"999.99"`, which is standard `node-postgres` behavior for the `NUMERIC` type (it avoids silent floating-point precision loss). If you'd rather receive real JS numbers without a manual `Number()` conversion, wrap the values in `jsonb_build_object(...)` in SQL as shown in the audit-trail section above — the same live test confirmed that numeric values nested inside a `JSONB` result decode as native JS numbers (e.g., `999.99`, not `"999.99"`), because `pg` parses `JSONB` columns with `JSON.parse` under the hood rather than applying the raw-`NUMERIC` string parser.

## FAQ

**Does Postgres MERGE support a RETURNING clause?**
Yes, since Postgres 17. On 15 or 16, `MERGE` runs but `RETURNING` is rejected.[^mergedoc]

**How do I know if MERGE performed an INSERT or an UPDATE?**
Add `merge_action()` to the `RETURNING` list — it returns the literal string `'INSERT'`, `'UPDATE'`, or `'DELETE'` for each row.[^mergedoc]

**How do I get both the old and new values from one upsert or MERGE?**
Use the `OLD`/`NEW` aliases added in Postgres 18: `RETURNING old.col AS old_val, new.col AS new_val`. This works on plain `INSERT`/`UPDATE`/`DELETE` too, not only `MERGE`.[^release18]

**Do I still need the xmax = 0 trick in Postgres 18?**
No — `(old IS NULL) AS is_new` does the same job without relying on an internal, undocumented column. The `xmax` trick still works if you're on an earlier version.

**What Postgres version do I need?** `MERGE` needs 15+, `MERGE ... RETURNING` needs 17+, and `OLD`/`NEW` aliases in `RETURNING` need 18+.[^pg15][^versioning]

**What error does Postgres raise if two source rows match the same target row in a MERGE?**
`ERROR: MERGE command cannot affect row a second time` — deduplicate the source or add a tie-breaker to the `ON` condition.

**Does node-postgres return NUMERIC values as JavaScript numbers?**
Not by default — a bare `NUMERIC` column comes back as a string (e.g. `"999.99"`) to avoid precision loss. Values nested inside a `JSONB` result (via `jsonb_build_object`, for example) decode as real JS numbers instead.

## Bottom line

`MERGE ... RETURNING` (Postgres 17+) plus the `OLD`/`NEW` aliases (Postgres 18+) replace two long-standing workarounds at once: the `xmax = 0` hack for detecting new rows, and a separate `SELECT` for capturing before/after values. Wrapped in a CTE, the same statement can also write a full audit trail with zero triggers. Know your version — 15 for `MERGE` itself, 17 for `RETURNING`, 18 for `OLD`/`NEW` — and, from Node.js, remember that bare `NUMERIC` columns arrive as strings while values nested in `JSONB` do not.

For the row-level side of multi-tenant writes that often sit next to this kind of sync job, see our guide to [Postgres row-level security for multi-tenant Node.js apps](https://nerdleveltech.com/postgres-row-level-security-multi-tenant-nodejs-tutorial). If you're modeling the staging/target relationship with an ORM instead of raw SQL, [Drizzle's relational query API](https://nerdleveltech.com/drizzle-orm-relational-queries-nested-with-tutorial) covers nested `with` queries over the same kind of tables. And if you need to trace a sync job's audit-log writes back to the request that triggered them, [correlation IDs with AsyncLocalStorage in Node.js](https://nerdleveltech.com/nodejs-correlation-id-asynclocalstorage-tutorial) show how to thread that context through.

[^mergedoc]: PostgreSQL, "MERGE" command reference (v18) — RETURNING, merge_action(), and xmax discussion. https://www.postgresql.org/docs/current/sql-merge.html
[^release18-4]: PostgreSQL, "E.1. Release 18.4" release notes (2026-05-14) — MERGE serialization-failure fix under REPEATABLE READ/SERIALIZABLE isolation. https://www.postgresql.org/docs/18/release-18-4.html
[^release18]: PostgreSQL, "E.5. Release 18" release notes — introduction of OLD/NEW support in RETURNING for INSERT/UPDATE/DELETE/MERGE (commit 80feb727c8, Dean Rasheed). https://www.postgresql.org/docs/18/release-18.html
[^versioning]: PostgreSQL, "Versioning Policy" — release dates and current minor versions. https://www.postgresql.org/support/versioning/
[^pg15]: PostgreSQL, "PostgreSQL 15 Released!" (October 13, 2022) — introduction of the MERGE command. https://www.postgresql.org/about/news/postgresql-15-released-2526/
[^pgedge]: pgEdge, "PostgreSQL 18 RETURNING & MERGE RETURNING Explained" (January 6, 2026) — published audit-trail example. https://www.pgedge.com/blog/postgresql-18-returning-enhancements-a-game-changer-for-modern-applications
[^syscols]: PostgreSQL, "5.6. System Columns" (v18) — xmax definition and "you do not really need to be concerned about these columns" guidance. https://www.postgresql.org/docs/current/ddl-system-columns.html