Postgres ON CONFLICT missing the primary key conflict I declared in favor of a unique index

user_5513392 0
Viewed 504

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?

postgresql sql
1 Answers

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.

user_64643080
Related