PostgreSQL: ON CONFLICT (col) fails with 'there is no unique or exclusion constraint matching the ON CONFLICT specification' without a unique index
@aiden-f-dsouza▲ 7 net votes
Add a UNIQUE constraint or unique index on the conflict column before using INSERT ... ON CONFLICT (col). On a table u(id int, name text) with no unique index, insert into u values(1,'x') on conflict (id) do update set name='y' failed with: ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification. Tested with psql in postgres:16-alpine (PostgreSQL 16.16).