2

I am running into an infrequent, but frustrating issue where Postgres seems to be missing my primary key conflict, and throwing an error for the unique index, which I have not put in my ON CONFLICT.

An example would be:

Table: users

Columns: id (pkey), ext_id (unique index), attr1, attr2

INSERT INTO users("id", "ext_id", "attr1", "attr2")
VALUES (1, 123, 'a thing', 'something')
ON CONFLICT (id) DO UPDATE
SET "ext_id" = excluded.ext_id, "attr1" = excluded.attr1, "attr2" = excluded.attr2

Sometimes this runs just fine, but sometimes it tells me ERROR: duplicate key value violates unique constraint "index_users_on_ext_id"

I'm sure there's something I'm missing, but I don't know what. Do I need to remove the unique columns from the SET? Why isn't this always throwing an error?

rPat
  • 375
  • 1
  • 13
  • Show the data which is in table and which you try to insert, when this "sometimes" happen. There's only one place that can trigger unique conflict, and this is unique index on `ext_id`. Maybe some row already have that same `ext_id`. – astentx Nov 11 '20 at 23:28
  • Change `ON CONFLICT (id)` to `ON CONFLICT (id, ext_id)`. – Adrian Klaver Nov 12 '20 at 00:18
  • @AdrianKlaver That works even if I don't have a composite index of id and ext_id? – rPat Nov 12 '20 at 02:13

1 Answers1

0

You get this error because the ext_id does already exist in the database.

There is no simple remedy, since you can only specify a single constraint in ON CONFLICT. Look at this question for inspiration.

If you want to skip the UPDATE instead of getting an error, you could add a WHERE condition to the UPDATE clause.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • That's what I also understand. But that isn't possible. Because the matching record already exists in this db, with the same ID and ext_id. But the error persists. There is no record that has the ext_id but not the ID. – rPat Nov 12 '20 at 16:00
  • That will also trigger the error, but from the `UPDATE` branch. See my changed answer. – Laurenz Albe Nov 12 '20 at 16:05