record.merge() and Unique Key constraints

[Chris A]

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!

[Lukas Eder]

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

--
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 jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/92eca557-7835-49a7-98d4-58e8a11784b6n%40googlegroups.com.