Re: a row is not inserted in nested INSERT ON CONFLICT
От | David G. Johnston |
---|---|
Тема | Re: a row is not inserted in nested INSERT ON CONFLICT |
Дата | |
Msg-id | CAKFQuwYd5mHWTv373bLLpB2jGDkDq+OTV8sVPMD07B9wog-GCg@mail.gmail.com обсуждение исходный текст |
Ответ на | a row is not inserted in nested INSERT ON CONFLICT (Jason Kim <git@jasonk.me>) |
Список | pgsql-bugs |
On Tue, Aug 13, 2024 at 10:37 AM Jason Kim <git@jasonk.me> wrote:
create table a (i int, j int, k int, t timestamp default (clock_timestamp()), unique (i));
insert into a values (1, 1), (2, 2), (3, 3);
with w(i) as (
insert into a values (1, 100), (2, 200), (3, 300) on conflict (i) do update set i = excluded.i + 10, t = clock_timestamp(), k = 0 returning i
) insert into a values (1, 10), (2, 20) on conflict (i) do update set i = (select (i - 10) from w order by i desc limit 1), t = clock_timestamp(), k = 1;
table a;
I find it unusual that the (1, 10) insert seems to be ignored.
I find it unusual that at no point did that produce an error. Something like:
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.
HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values.
(though because it is two inserts that exact error doesn't happen)
However, this non-behavior is documented:
In short, your command is broken but the system presently is incapable of telling you that and instead produces undefined behavior.
David J.
В списке pgsql-bugs по дате отправления: