Hi Chris, I would think the problem here is that PostgreSQL doesn't have a way to implement ON DUPLICATE KEY UPDATE in terms of MySQL's semantics. In MySQL, all unique keys are taken into consideration with that clause. PostgreSQL's ON CONFLICT clause only supports conflicts on a single unique key. jOOQ always uses the primary key for that, not a candidate key.
This behaviour was fixed in jOOQ 3.18 for PostgreSQL 15, which finally has MERGE support. See: - https://github.com/jOOQ/jOOQ/issues/7552 - https://github.com/jOOQ/jOOQ/issues/13358 In the meantime, you'll have to run ON CONFLICT statements manually, rather than using UpdatableRecord::merge. I hope this helps Lukas On Thu, Oct 6, 2022 at 7:42 PM Chris A <[email protected]> wrote: > Hi, > > According to the docs merge() "implements the semantics of an INSERT … ON > DUPLICATE KEY UPDATE statement, which will update the row regardless of > which (unique) key value is already present". > > This doesn't seem to be the case. I have a unique key comprising of 2 > fields and I'm getting an exception instead of the merge following through. > This is with PostgreSQL: > > "Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value > violates unique constraint "some_unique_key" > Detail: Key (field1, field2)=(value1, value2) already exists." > > Shouldn't record.merge() insert the missing record or update it if it > already exists? > > Thanks! > > -- > You received this message because you are subscribed to the Google Groups > "jOOQ User Group" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/jooq-user/92eca557-7835-49a7-98d4-58e8a11784b6n%40googlegroups.com > <https://groups.google.com/d/msgid/jooq-user/92eca557-7835-49a7-98d4-58e8a11784b6n%40googlegroups.com?utm_medium=email&utm_source=footer> > . > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO7hZUY_yTQs4EfMFWkiQfMgwM21ywCH53SStGM1PPz3Lg%40mail.gmail.com.
