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.¹²

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.³² 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.¹ 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.¹ MERGE was added in Postgres 15 (released October 13, 2022); INSERT ... ON CONFLICT has existed since Postgres 9.5.⁴

-- 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.¹ 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.

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:

 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.¹ 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).

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.²

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:

 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:

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.¹ 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.⁵

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

-- 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.

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:

[
  {
    "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.⁶ 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, 20224
RETURNING clause on MERGE	Postgres 17	September 26, 20243
OLD/NEW aliases in RETURNING (incl. on MERGE)	Postgres 18	September 25, 20253

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.²

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.

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:

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.

npm install pg
npm install -D @types/pg 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.

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. If you're modeling the staging/target relationship with an ORM instead of raw SQL, Drizzle's relational query API 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 show how to thread that context through.

Footnotes

1. PostgreSQL, "MERGE" command reference (v18) — RETURNING, merge_action(), and xmax discussion. https://www.postgresql.org/docs/current/sql-merge.html ↩ ↩² ↩³ ↩⁴ ↩⁵ ↩⁶ ↩⁷ ↩⁸

2. PostgreSQL, "E.1. Release 18.4" release notes (2026-05-14) — OLD/NEW RETURNING aliases and MERGE serialization-failure fix. https://www.postgresql.org/docs/18/release-18-4.html ↩ ↩² ↩³ ↩⁴ ↩⁵

3. PostgreSQL, "Versioning Policy" — release dates and current minor versions. https://www.postgresql.org/support/versioning/ ↩ ↩² ↩³ ↩⁴

4. PostgreSQL, "PostgreSQL 15 Released!" (October 13, 2022) — introduction of the MERGE command. https://www.postgresql.org/about/news/postgresql-15-released-2526/ ↩ ↩² ↩³

5. 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 ↩

6. 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 ↩