1

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.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
SP QRT
  • 95
  • 5

1 Answers1

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.

Yordan Boev
  • 301
  • 1
  • 9
  • Thx for answer. Second solution is what i need but i don't understand how to implement it. Should i call the "onConflict()" method twice ? But jooq not allow to do this – SP QRT Mar 07 '22 at 16:10
  • 1
    Have you tried not specifying anything? Simply .onConflict().doNothing()? – Yordan Boev Mar 07 '22 at 16:18
  • 1
    It worked! But. If I still need to specify multiple unique columns. Pretend i have third column with unique constraint, but i do not want to include it in "onConflict" method – SP QRT Mar 07 '22 at 16:28
  • 1
    I think resolving conflicts differently for different columns is complicated. I do not know how to do it in a nice way. If you find something, please add or edit it here. – Yordan Boev Mar 07 '22 at 16:39