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.
(though because it is two inserts that exact error doesn't happen)
