Thank you Lukas for the quick response! It helps. Vaibhav
On Tuesday, October 24, 2017 at 1:44:28 PM UTC+5:30, Lukas Eder wrote: > > Indeed, jOOQ doesn't support that WHERE predicate in the ON CONFLICT > clause yet. I've created a feature request for this: > https://github.com/jOOQ/jOOQ/issues/6736 > > There are workarounds: > > 1. You can always resort to using plain SQL > 2. You can patch the generated SQL string with an ExecuteListener or a > VisitListener > 3. You can extract the generated SQL and run it using JDBC directly, > patching the SQL string with a simple regex > > Hope this helps, > Lukas > > 2017-10-24 8:03 GMT+02:00 <[email protected] <javascript:>>: > >> I want to emulate the following insert statement (listed here >> <https://stackoverflow.com/a/46728249/1141285>) in jOOQ : >> >> INSERT INTO key_value_pair (key, value, is_active) VALUES >> ('temperature','20', false) ON CONFLICT (key) WHERE is_active >> DO UPDATESET value = '33', is_active = true; >> >> >> To do this, I am writing the following code : >> >> dslContext.insertInto(KEY_VALUE_PAIR).columns(KEY_VALUE_PAIR.KEY, >> KEY_VALUE_PAIR.VALUE, KEY_VALUE_PAIR.IS_ACTIVE) >> .values("temperature", "20", false) >> .onConflict(KEY_VALUE_PAIR.KEY, KEY_VALUE_PAIR.VALUE, >> KEY_VALUE_PAIR.IS_ACTIVE).doNothing().execute(); >> >> but, it fails with the following error : >> >> >> *nested exception is org.postgresql.util.PSQLException: ERROR: there is no >> unique or exclusion constraint matching the ON CONFLICT specification* >> >> >> The partial index that I am using is : >> >> CREATE INDEX key_and_value_if_is_active_true_unique ON key_value_pair (key, >> value) WHERE is_active = true; >> >> >> The question is : I think jOOQ does not support the WHERE index predicate >> in the onConflict clause. Is there a workaround for this? >> >> -- >> 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] <javascript:>. >> For more options, visit https://groups.google.com/d/optout. >> > > -- 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]. For more options, visit https://groups.google.com/d/optout.
