The setup

MERGE is one of SQL's genuinely portable-looking statements. Update matching rows, insert the rest, in one atomic operation. The syntax is close enough across Postgres, DuckDB, Snowflake, SQL Server, and Oracle that you can copy a MERGE block between them without a rewrite. Right up until your source data has a duplicate in it.

That happens more often than it sounds. A nightly stock-sync file gets written twice for the same SKU by two different warehouse feeds. A CDC stream re-emits an event before the previous one settles. A join upstream fans out a row it shouldn't have. None of that is exotic. It's the kind of thing that shows up in a real pipeline within the first few months.

I wanted to know, precisely, what each engine actually does when it happens: not what the docs imply, what actually runs.

The setup, made concrete

A small inventory table, one real row:

CREATE TABLE inventory (sku TEXT PRIMARY KEY, stock_count INT);
INSERT INTO inventory VALUES ('WIDGET-1', 100);

And an incoming batch with a duplicate: the same SKU reported twice, with two different counts.

CREATE TABLE incoming_batch (sku TEXT, stock_count INT);
INSERT INTO incoming_batch VALUES ('WIDGET-1', 80), ('WIDGET-1', 95);

Then the obvious MERGE:

MERGE INTO inventory AS t
USING incoming_batch AS s
ON t.sku = s.sku
WHEN MATCHED THEN UPDATE SET stock_count = s.stock_count
WHEN NOT MATCHED THEN INSERT (sku, stock_count) VALUES (s.sku, s.stock_count);

Postgres: a real, hard stop

Run against Postgres 18:

psycopg.errors.CardinalityViolation: MERGE command cannot affect row a second time

Genuine failure, not a warning. Postgres detected that the target row WIDGET-1 would be touched by two different source rows in the same MERGE, and refused to proceed at all. Nothing gets written. The whole statement rolls back.

DuckDB: a real, silent success

Same table shapes, same duplicate, run against DuckDB:

>>> con.execute(merge_sql)
>>> con.execute("SELECT * FROM inventory").fetchall()
[('WIDGET-1', 80)]

No error. stock_count is now 80, one of the two source values, chosen without complaint.

Two real engines. Identical MERGE statement. Identical duplicate. One refuses to run at all; the other picks a value and moves on.

Is "picks a value" at least predictable?

Worth checking directly rather than assuming. I reversed the insertion order of the two duplicate rows and reran it three times:

insert order (80, 95) → result: 80
insert order (95, 80) → result: 95
insert order (95, 80) → result: 95
insert order (95, 80) → result: 95

Consistent, in this simple case: the row that comes first in the source's scan order wins, every time. But "consistent right now, in this exact query" is a different claim from "safe to depend on." Nothing in the MERGE syntax declares which row should win on a duplicate match. The result you get is a side effect of DuckDB's current scan order, not a documented contract. Add an index, change the query plan, upgrade the engine, and that ordering is free to change without it counting as a bug anywhere.

What this actually means for a real pipeline

If your source data is guaranteed duplicate-free (a well-designed CDC stream, a properly deduplicated staging table), this never fires and the distinction is academic. The moment that guarantee isn't airtight (and in practice, it rarely stays airtight forever), the two behaviors diverge hard:

  • On Postgres, a duplicate in the source is a loud failure. Your pipeline breaks, you get paged, you fix the upstream dedup bug before bad data lands anywhere.
  • On DuckDB, a duplicate in the source is silent. The MERGE succeeds, the row lands with a value, and nothing tells you two conflicting updates just collided. You find out weeks later, from a number that doesn't add up.

The second failure mode is strictly worse, not because DuckDB is "wrong" (its behavior is internally consistent, just undocumented as a contract), but because a silent partial success is harder to catch than a loud one.

The actual fix

Don't rely on either engine's tie-breaking behavior. Deduplicate the source explicitly, before the MERGE ever sees it. It's tempting to reach for QUALIFY here: DuckDB and Snowflake both support it, and it reads cleanly. But that's a real, worth-knowing portability trap of its own. Postgres doesn't have it at all, and using it quietly locks the fix to only some of the engines this article is about. A plain subquery works everywhere:

MERGE INTO inventory AS t
USING (
    SELECT sku, stock_count FROM (
        SELECT sku, stock_count,
               ROW_NUMBER() OVER (PARTITION BY sku ORDER BY stock_count DESC) AS rn
        FROM incoming_batch
    ) ranked
    WHERE rn = 1
) AS s
ON t.sku = s.sku
WHEN MATCHED THEN UPDATE SET stock_count = s.stock_count
WHEN NOT MATCHED THEN INSERT (sku, stock_count) VALUES (s.sku, s.stock_count);

Confirmed running unmodified on both Postgres 18 and DuckDB. Both land on 95, the higher of the two conflicting values, because that's now what ORDER BY stock_count DESC explicitly says to keep. The "which value wins on a duplicate" decision is a visible business rule instead of an accident of scan order, and it makes Postgres's real hard-stop error the thing that only ever fires when the dedup step itself has a bug, which is exactly when you want it to.

The takeaway

MERGE's syntax is portable. Its behavior on messy real-world data is not, and the gap between the two is exactly where duplicate source rows live. Test the failure case directly against whatever engine you're actually shipping to. The difference between a loud error and a silent wrong answer is the difference between a five-minute fix and a week of "why don't these numbers match."