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
