Re: a row is not inserted in nested INSERT ON CONFLICT

On Tue, Aug 13, 2024 at 10:37 AM Jason Kim <git(at)jasonk(dot)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)

However, this non-behavior is documented:

https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-MODIFYING

In short, your command is broken but the system presently is incapable of
telling you that and instead produces undefined behavior.

David J.