How to apply cast in postgres values expression with JOOQ

Question

For large 'in' conditions in Postgres the following

IN (VALUES ('a'),('b'),('c'))

can lead to much more efficient query plans than

IN ('a','b','c')

In JOOQ I can express this with

Field f = ...  
Row1[] rows = new Row1[array.length];
for (int i=0;i<array.length;i++)
    rows[i] = DSL.row(array[i]);
Condition c = f.in(DSL.selectFrom(DSL.values(rows)));

But what if I need to cast the values? To be more specific, when comparing values with a citext column at least one of the values needs to be explicitly cast to citext for the query to work. Example:

select distinct "x0"."pk"
from "sometable" as "x0"
 where "x0"."external_shared_id" in ( values 
       ('ext0308174863'::citext),
     ('ext1560238348'))

How can I write this with JOOQ?

score 1 · Accepted Answer · answered Apr 27 '21 at 19:03

There is currently no documented way to create custom data types for usage in casts. You could use internal API at your own risk, which I won't document here. I've created a feature request to create such DataType references: https://github.com/jOOQ/jOOQ/issues/11806

An explicit feature request for citext support is here: https://github.com/jOOQ/jOOQ/issues/5934

As always, when jOOQ is missing support for vendor specific functionality plain SQL templating is your friend. So, just do this:

rows[i] = row(field("{0}::citext", String.class, val(array[i])));

Assuming the following static import:

import static org.jooq.impl.DSL.*;

How to apply cast in postgres values expression with JOOQ

1 Answers1

Linked