Re: "command cannot affect row a second time" in INSERT ... ON CONFLICT

Hi Karthik,

> I am looking to better understand the applicability of the error message "command cannot affect row a second time".
>
> Consider the following table and data:
> CREATE TABLE ioc (i int, UNIQUE(i));
> INSERT INTO ioc VALUES (1);
>
> The following two queries produce different errors:
> Query 1
> postgres=# INSERT INTO ioc VALUES (1), (20) ON CONFLICT (i) DO UPDATE SET i = 20;
> ERROR: 21000: 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.
>
> Query 2
> postgres=# INSERT INTO ioc VALUES (20), (1) ON CONFLICT (i) DO UPDATE SET i = 20;
> ERROR: 23505: duplicate key value violates unique constraint "ioc_i_key"
> DETAIL: Key (i)=(20) already exists.

Not sure if it will answer your question *entirely* but you will find
a bit more detail about "cannot affect row a second time" in the
discussion [1]. This error has nothing to do with unique constraints,
so I think you trigger one of two errors depending on the order of
inserted rows and the content of your table. This being said, I didn't
investigate your scenario in much detail.

[1]: https://www.postgresql.org/message-id/flat/CAJ7c6TPQJNFETz9H_qPpA3x7ybz2D1QMDtBku_iK33gT3UR34Q%40mail.gmail.com

--
Best regards,
Aleksander Alekseev