Digest for jooq-user@googlegroups.com - 3 updates in 1 topic

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Digest for jooq-user@googlegroups.com - 3 updates in 1 topic

http://groups.google.com/group/jooq-user/topics mailing list
[hidden email]: Oct 23 11:03PM -0700

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?
Lukas Eder <[hidden email]>: Oct 24 10:14AM +0200

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
 
[hidden email]: Oct 24 01:48AM -0700

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:
You received this digest because you're subscribed to updates for this group. You can change your settings on the group membership page.
To unsubscribe from this group and stop receiving emails from it send an email to [hidden email].