MERGE causes unique constraint violation when source contains key multiple times

Question

Given two tables counter and cnt_source defined like this:

create temporary table counter (key bigint, count bigint);
create unique index counter_key_uniq on counter (key);

create temporary table cnt_source (key bigint, count bigint, add boolean);
insert into cnt_source values (1, 2, true);
insert into cnt_source values (1, 3, true);

I am using the MERGE statement to compile the cnt_source into the counter table:

merge into counter
using (
    select key, count, add
    from cnt_source
) as source on source.key = counter.key

when matched then update set
    count = (
        case when source.add
        then counter.count + source.count
        else counter.count - source.count
        end
    )

when not matched then insert
    values (source.key, source.count)
;

But this unexpectedly triggers ERROR: 23505: duplicate key value violates unique constraint:

CREATE TABLE
CREATE INDEX
CREATE TABLE
INSERT 0 1
INSERT 0 1
ERROR:  23505: duplicate key value violates unique constraint "counter_key_uniq"
DETAIL:  Key (key)=(1) already exists.
SCHEMA NAME:  pg_temp_56
TABLE NAME:  counter
CONSTRAINT NAME:  counter_key_uniq
LOCATION:  _bt_check_unique, nbtinsert.c:673

I would have expected MERGE to take care of this, but I cannot find any reference on how WHEN MATCHED is determined.

What would be the right approach here?

I have tried rewriting it to an INSERT-like statement with ON CONFLICT DO UPDATE SET but I could not get access to the add field there, which I do need in my updating logic:

insert into counter (key, count)
select key, count from cnt_source as source
on conflict (key) do update set
    count = (
        case when source.add
        then counter.count + source.count
        else counter.count - source.count
        end
    )
;

yields

ERROR:  42P01: missing FROM-clause entry for table "source"
LINE 5:         case when source.add
                          ^
LOCATION:  errorMissingRTE, parse_relation.c:3761

This is a MRE, the reality is even more complicated.

Charlieface · Accepted Answer · 2025-11-21 11:12:17Z

I would have expected MERGE to take care of this, but I cannot find any reference on how WHEN MATCHED is determined.

Not sure why you expected this. It's not documented, as you say, and not something that most DML deals with (even INSERT...ON CONFLICT... can't update a row multiple times).

The docs are clear:

You should ensure that the join produces at most one candidate change row for each target row. In other words, a target row shouldn't join to more than one data source row. If it does, then only one of the candidate change rows will be used to modify the target row; later attempts to modify the row will cause an error. This can also occur if row triggers make changes to the target table and the rows so modified are then subsequently also modified by MERGE. If the repeated action is an INSERT, this will cause a uniqueness violation, while a repeated UPDATE or DELETE will cause a cardinality violation; the latter behavior is required by the SQL standard. This differs from historical PostgreSQL behavior of joins in UPDATE and DELETE statements where second and subsequent attempts to modify the same row are simply ignored.

So yes, you need to ensure you get unique rows.

One method is to just aggregate:

merge into counter
using (
    select
      cs.key,
      sum(case when cs.add then cs.count else -cs.count end) as total_count
    from cnt_source cs
    group by cs.key
) as source_agg on source_agg.key = counter.key

when matched then update set
    count = counter.count + source_agg.total_count

when not matched then insert
    values (source_agg.key, source_agg.total_count)
;

You can also do this via INSERT...ON CONFLICT. You need to use the EXCLUDED pseudo-table to access the failing prospective insert.

with source_agg as (
    select
      cs.key,
      sum(case when cs.add then cs.count else -cs.count end) as total_count
    from cnt_source cs
    group by cs.key
)
insert into counter as c (key, count)
select
  s.key,
  s.total_count
from source_agg as s
on conflict (key) do update set
    count = c.count + EXCLUDED.count
;

"One method is to just aggregate" sadly this is not as trivial in the real case as this example. I will see if I can add an extra processing step to make sure the source table doesn't contain duplicates on the target constraint. --- "Not sure why you expected this" A very similar MySQL query does work in this situation, I would've expected PostgreSQL to behave the same, but that may be too naive of me. — DS-Charlie
– DS-Charlie, Commented Nov 21 at 10:03

Stack Exchange Network

MERGE causes unique constraint violation when source contains key multiple times

1 Answer 1

Your Answer

Hot Network Questions

MERGE causes unique constraint violation when source contains key multiple times

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions