I am using Postgres and I have a table with a unique key (memberId, propertyName). I am using onDuplicateKey and the generated code shows it is using on conflict but it uses the id. Is it possible to specify your own keys to check or does Jooq try to read the table and check that there are unique constraints? My current workaround is doing a select then doing an update or an insert.
The ON DUPLICATE KEY syntax is derived from MySQL, where the semantics of the clause is to consider all the unique constraints (including primary key) on the table, not just the ones you care about.
But why use this syntax in the first place, when you're targeting PostgreSQL? jOOQ supports PostgreSQL's ON CONFLICT, which allows for specifying which unique key to use for the clause.