How to solve "ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification" [duplicate]

Question

I got table with two unique column:

CREATE TABLE "users" (  
    "id" bigserial PRIMARY KEY,  
    "phone" varchar(20) UNIQUE,  
    "email" varchar(50) UNIQUE  
);  

I want to write save or update method
My jooq code:

dsl.insertInto(Users.USERS)
    .set(Users.USERS.EMAIL, user.getEmail())
    .set(Users.USERS.PHONE, user.getPhone())
    .onConflict(Users.USERS.PHONE, Users.USERS.EMAIL)
    .doNothing()
    .execute();

But i got exception:
"ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification"
Help please.

Answer 1

The error says it correctly. You have specified an onConflict clause, but no existing unique index was found for it. You have 2 separated unique indices, but the onConflict specifies two columns. Your query would work if you had defined a single unique index on both phone and email together.

Solution 1: Adding a unique index over the 2 columns by unique(phone, email) should fix it (assuming you want to have the combination of the phone and email unique).

Solution 2: Make onConflict correspond to all the unique indexes. Consider using onConflict().doNothing() to do nothing for any constraint violation that can occur.