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
2017-01-23 5:54 GMT+01:00 Daniel Einspanjer <[email protected]>
:
> 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.
>
--
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.
