Problem using contains on a Postgres text[]

[Daniel Einspanjer]

The jooq javadocs indicate that contains can be used with the Postgres dialect to do an @> condition on array fields.

I tried this out with a text[] field I had:

private void resolveBandwidth(ObjSystemGateCallLog other, DSLContext ctx) {
    final String label = other.getSourceBandwidth().getDescription();
    Integer bandwidthsFk = Bandwidths.getBandwidthsFk(label, () -> ctx.select(DBandwidths.D_BANDWIDTHS.PK)
            .from(DBandwidths.D_BANDWIDTHS)
            .where(DBandwidths.D_BANDWIDTHS.DESCRIPTION.contains(Stream.of(label).toArray(String[]::new)))
            .fetchOne(Record1::value1));
    setBandwidth(bandwidthsFk);
}

The DDL is:

CREATE TABLE d_bandwidths

(

    pk INTEGER DEFAULT nextval('d_bandwidths_pk_seq'::regclass) PRIMARY KEY NOT NULL,

    kbps INTEGER,

    description TEXT[] NOT NULL

);

However, when this code runs, I get a pg error: 

Caused by: org.jooq.exception.DataAccessException: SQL [select "dwh"."d_bandwidths"."pk" from "dwh"."d_bandwidths" where "dwh"."d_bandwidths"."description" @> ?::varchar[] -- SQL rendered with a free trial version of jOOQ 3.9.0]; ERROR: operator does not exist: text[] @> character varying[]

  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.

  Position: 101

at org.jooq_3.9.0.POSTGRES_9_5.debug(Unknown Source)

I tried a simple version of that query in psql and it behaves the same:

SELECT '{"Test"}'::text[] @> '{"est"}'::varchar[]

Changing the varchar to a text does resolve the problem, so I'll look up the syntax in jooq to be able to do that, but I suspect this is a problem that shouldn't happen in the sql statement generated by jooq?

-Daniel

[Lukas Eder]

Hi Daniel,

Thank you very much for reporting. Yes, this is a known issue:

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

Unfortunately, PostgreSQL:

- Doesn't overload all these operators to work with any combination of text[]/text[], text[]/varchar[], varchar[]/text[], varchar/varchar, and there's no implicit conversion when arrays are involved, as far as I know.

- Needs quite a bit of explicit casting with array bind variables (or other "advanced" data types)

Historically, in jOOQ, all Strings are mapped to varchar types, even if in PostgreSQL, text is probably a bit more popular. 

Unfortunately, I currently don't really know a thorough fix for #4754 with all the above constraints. But there's an easy workaround, of course: Use plain SQL:

DSL.field("{0} @> {1}::text[]", 

  D_BANDWIDTHS.DESCRIPTION, 

  DSL.val(theArray));

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.