CODEWITHSUNDEEP
postgresqljooq
DWilches
DWilches

Reputation: 23035

Upsert with "on conflict do update" with partial index in jOOQ

I have a partial index on table MY_TABLE that is defined like:

CREATE UNIQUE INDEX my_index 
    ON MY_TABLE(CUSTOMER_ID, OWNER_ID) 
    WHERE CLOSED_ON IS NULL;

Then, I'm attempting an upsert using jOOQ

final var insert = using(configuration)
           .insertInto(MY_TABLE,
                       MY_TABLE.CUSTOMER_ID,
                       MY_TABLE.OWNER_ID,
                       MY_TABLE.UPDATED_ON);
customers.forEach(custId -> insert.values(custId, reprId, today));
insert.onConflict(MY_TABLE.CUSTOMER_ID, MY_TABLE.OWNER_ID)
    .where(MY_TABLE.CLOSED_ON.isNull())
    .doUpdate()
    .set(MY_TABLE.UPDATED_ON, today)
    .execute();

But the above code is throwing this error:

org.jooq.exception.DataAccessException: SQL [...];
   ERROR: invalid reference to FROM-clause entry for table "my_table"
   Hint: There is an entry for table "my_table", but it cannot be referenced from this part of the query.

Looking at the generated SQL I see jOOQ is adding the schema and table name to the where part of the on conflict, which Postgres doesn't like:

... on conflict ("customer_id", "owner_id") where "my_schema"."my_table"."closed_on" is null do update set ...

Is there a way to tell jOOQ not to add the schema and table name?

This is a workaround I'm using, but I wonder if there is a better way:

.where(condition("closed_on IS NULL"))

Upvotes: 2

Views: 1794

Answers (1)

DWilches
DWilches

Reputation: 23035

We found a bug report in jOOQ's issues page that asks exactly this question:

https://github.com/jOOQ/jOOQ/issues/11732

Which mentions this workaround:

.where(field(COURSE_ENROLL.DELETED_TS.getUnqualifiedName()).isNull())

So I can do that instead of what I did with the literal string in my case.

This is also mentioned to be a bug that was fixed 3 weeks ago :)

Thanks.

EDIT: There is already a fix for this in jOOQ 3.14.9

Upvotes: 2

Related Questions