[PATCH] Make ON CONFLICT DO NOTHING and ON CONFLICT DO UPDATE consistent - Mailing list pgsql-hackers

From Aleksander Alekseev
Subject [PATCH] Make ON CONFLICT DO NOTHING and ON CONFLICT DO UPDATE consistent
Date
Msg-id CAJ7c6TPQJNFETz9H_qPpA3x7ybz2D1QMDtBku_iK33gT3UR34Q@mail.gmail.com
Whole thread Raw
Responses Re: [PATCH] Make ON CONFLICT DO NOTHING and ON CONFLICT DO UPDATE consistent
List pgsql-hackers
Hi hackers,

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.

The proposed patch fixes this.

[1]: https://www.postgresql.org/message-id/22438.1477265185%40sss.pgh.pa.us

-- 
Best regards,
Aleksander Alekseev

Attachment

pgsql-hackers by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: CREATE ROLE bug?
Next
From: Andrei Zubkov
Date:
Subject: Re: [PATCH] Tracking statements entry timestamp in pg_stat_statements