Re: "command cannot affect row a second time" in INSERT ... ON CONFLICT - Mailing list pgsql-hackers

From Aleksander Alekseev
Subject Re: "command cannot affect row a second time" in INSERT ... ON CONFLICT
Date
Msg-id CAJ7c6TMV+Z+V4o0kQkRdZ1MS4Sh0gW8OdcbDuzx2eioG5djPrA@mail.gmail.com
Whole thread Raw
In response to "command cannot affect row a second time" in INSERT ... ON CONFLICT  (Karthik Ramanathan <karthikram.3006@gmail.com>)
List pgsql-hackers
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



pgsql-hackers by date:

Previous
From: Jelte Fennema-Nio
Date:
Subject: Re: Use "protocol options" name instead of "protocol extensions" everywhere
Next
From: Jelte Fennema-Nio
Date:
Subject: Re: Use "protocol options" name instead of "protocol extensions" everywhere