Insert Statement (jOOQ and Postgresql) : Using index predicate in a partial unique index

[vaibha...@chegg.com]

I want to emulate the following insert statement (listed here) in jOOQ :

INSERT INTO key_value_pair (key, value, is_active) VALUES ('temperature','20', false) 
ON CONFLICT (key) WHERE is_active
DO UPDATE
SET 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]

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

--
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+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[vaibha...@chegg.com]

Thank you Lukas for the quick response! It helps.

Vaibhav

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.