Hi, On 2023-01-25 18:45:12 +0300, Aleksander Alekseev wrote: > Currently we allow self-conflicting inserts for ON CONFLICT DO NOTHING: > > ``` > CREATE TABLE t (a INT UNIQUE, b INT); > INSERT INTO t VALUES (1,1), (1,2) ON CONFLICT DO NOTHING; > -- succeeds, inserting the first row and ignoring the second > ``` > ... but not for ON CONFLICT .. DO UPDATE: > > ``` > INSERT INTO t VALUES (1,1), (1,2) ON CONFLICT (a) DO UPDATE SET b = 0; > ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time > HINT: Ensure that no rows proposed for insertion within the same > command have duplicate constrained values. > ``` > > Tom pointed out in 2016 that this is actually a bug [1] and I agree.
I don't think I agree with this being a bug. We can't sensible implement updating a row twice within a statement - hence erroring out for ON CONFLICT DO UPDATE affecting a row twice. But what's the justification for erroring out in the DO NOTHING case? ISTM that it's useful to be able to handle such duplicates, and I don't immediately see what semantic confusion or implementation difficulty we avoid by erroring out. It seems somewhat likely that a behavioural change will cause trouble for some of the uses of DO NOTHING out there. Greetings, Andres Freund
