From: | Aleksander Alekseev <aleksander(at)timescale(dot)com> |
---|---|
To: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | [PATCH] Make ON CONFLICT DO NOTHING and ON CONFLICT DO UPDATE consistent |
Date: | 2023-01-25 15:45:12 |
Message-ID: | CAJ7c6TPQJNFETz9H_qPpA3x7ybz2D1QMDtBku_iK33gT3UR34Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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 | Content-Type | Size |
---|---|---|
v1-0001-Make-ON-CONFLICT-DO-NOTHING-and-ON-CONFLICT-DO-UP.patch | application/octet-stream | 17.2 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Andrei Zubkov | 2023-01-25 15:46:40 | Re: [PATCH] Tracking statements entry timestamp in pg_stat_statements |
Previous Message | Bruce Momjian | 2023-01-25 15:40:50 | Re: CREATE ROLE bug? |