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
--
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 [email protected].
For more options, visit https://groups.google.com/d/optout.
