[SOLVED] Jooq and postgres: pg_trgm operators in plain sql produce error "operator does not exist"

Technologies I am using: java, spring boot, jooq, postgres with pg_trgm extension, r2dbc.

I am trying to use pg_trgm operators for a simple search on Postgres, but jooq throws an error.

Code sample:

String searchKeyword = "something";
DSL.select(Tables.EXAMPLE.ID)
          .from(Tables.EXAMPLE)
          .where(DSL.condition("{0} <<% {1}", DSL.val(searchKeyword), Tables.EXAMPLE.TEXT_FIELD))

or even simpler:

DSL.resultQuery("select 'a' <<% 'a';")

Produces the error operator does not exist: character varying <<% text.

stacktrace:

org.jooq.exception.DataAccessException: SQL [select 'a' <<% 'a';]; operator does not exist: unknown <<% unknown

Original Stack Trace:
        at org.jooq.impl.Tools.translate(Tools.java:3470)
        at org.jooq.impl.Tools.translate(Tools.java:3448)
        at org.jooq.impl.Tools.translate(Tools.java:3432)
        at org.jooq.impl.R2DBC$Forwarding.lambda$onError$0(R2DBC.java:252)      at org.jooq.impl.Internal$1.onComplete(Internal.java:497)
        at reactor.core.publisher.StrictSubscriber.onComplete(StrictSubscriber.java:123)
        at org.springframework.security.test.context.support.ReactorContextTestExecutionListener$DelegateTestExecutionListener$SecuritySubContext.onComplete(ReactorContextTestExecutionListener.java:130)
        at reactor.core.publisher.FluxPeekFuseable$PeekFuseableSubscriber.onComplete(FluxPeekFuseable.java:277)
        at reactor.core.publisher.Operators$MultiSubscriptionSubscriber.onComplete(Operators.java:2060)
        at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.onComplete(MonoIgnoreThen.java:209)
        at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.onComplete(MonoIgnoreThen.java:209)
        at reactor.pool.SimpleDequePool.maybeRecycleAndDrain(SimpleDequePool.java:533)
        at reactor.pool.SimpleDequePool$QueuePoolRecyclerInner.onComplete(SimpleDequePool.java:765)
        at org.springframework.security.test.context.support.ReactorContextTestExecutionListener$DelegateTestExecutionListener$SecuritySubContext.onComplete(ReactorContextTestExecutionListener.java:130)
        at reactor.core.publisher.Operators.complete(Operators.java:137)        at reactor.core.publisher.MonoEmpty.subscribe(MonoEmpty.java:46)        at reactor.core.publisher.Mono.subscribe(Mono.java:4490)
        at reactor.pool.SimpleDequePool$QueuePoolRecyclerMono.subscribe(SimpleDequePool.java:877)       at reactor.core.publisher.MonoDefer.subscribe(MonoDefer.java:53)        at reactor.core.publisher.InternalMonoOperator.subscribe(InternalMonoOperator.java:64)      at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.subscribeNext(MonoIgnoreThen.java:240)      at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.onComplete(MonoIgnoreThen.java:203)         at org.springframework.security.test.context.support.ReactorContextTestExecutionListener$DelegateTestExecutionListener$SecuritySubContext.onComplete(ReactorContextTestExecutionListener.java:130)      at reactor.core.publisher.FluxPeek$PeekSubscriber.onComplete(FluxPeek.java:260)         at reactor.core.publisher.Operators.complete(Operators.java:137)        at reactor.core.publisher.MonoEmpty.subscribe(MonoEmpty.java:46)        at reactor.core.publisher.Mono.subscribe(Mono.java:4490)        at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.subscribeNext(MonoIgnoreThen.java:263)      at reactor.core.publisher.MonoIgnoreThen.subscribe(MonoIgnoreThen.java:51)      at reactor.core.publisher.InternalMonoOperator.subscribe(InternalMonoOperator.java:64)      at reactor.core.publisher.MonoDefer.subscribe(MonoDefer.java:53)        at reactor.core.publisher.InternalMonoOperator.subscribe(InternalMonoOperator.java:64)      at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.subscribeNext(MonoIgnoreThen.java:240)      at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.onComplete(MonoIgnoreThen.java:203)         at org.springframework.security.test.context.support.ReactorContextTestExecutionListener$DelegateTestExecutionListener$SecuritySubContext.onComplete(ReactorContextTestExecutionListener.java:130)      at reactor.core.publisher.MonoIgnoreElements$IgnoreElementsSubscriber.onComplete(MonoIgnoreElements.java:89)        at reactor.core.publisher.FluxHandleFuseable$HandleFuseableSubscriber.onComplete(FluxHandleFuseable.java:236)       at reactor.core.publisher.Operators$MonoSubscriber.complete(Operators.java:1840)        at reactor.core.publisher.MonoSupplier.subscribe(MonoSupplier.java:62)      at reactor.core.publisher.Mono.subscribe(Mono.java:4490)        at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.subscribeNext(MonoIgnoreThen.java:263)      at reactor.core.publisher.MonoIgnoreThen.subscribe(MonoIgnoreThen.java:51)      at reactor.core.publisher.InternalMonoOperator.subscribe(InternalMonoOperator.java:64)      at reactor.core.publisher.MonoDefer.subscribe(MonoDefer.java:53)        at reactor.core.publisher.Mono.subscribe(Mono.java:4490)        at org.jooq.impl.R2DBC$AbstractNonBlockingSubscription.lambda$cancel0$4(R2DBC.java:663)         at java.base/java.util.concurrent.atomic.AtomicReference.updateAndGet(AtomicReference.java:210)         at org.jooq.impl.R2DBC$AbstractNonBlockingSubscription.cancel0(R2DBC.java:647)      at org.jooq.impl.R2DBC$AbstractSubscription.complete(R2DBC.java:213)        at org.jooq.impl.R2DBC$AbstractResultSubscriber.complete(R2DBC.java:303)        at org.jooq.impl.R2DBC$Forwarding.complete(R2DBC.java:265)      at org.jooq.impl.R2DBC$Forwarding.onError(R2DBC.java:252)       at reactor.core.publisher.StrictSubscriber.onError(StrictSubscriber.java:106)       at org.springframework.security.test.context.support.ReactorContextTestExecutionListener$DelegateTestExecutionListener$SecuritySubContext.onError Caused by: io.r2dbc.postgresql.ExceptionFactory$PostgresqlBadGrammarException: [42883] operator does not exist: unknown <<% unknown   at io.r2dbc.postgresql.ExceptionFactory.createException(ExceptionFactory.java:96)   Suppressed: The stacktrace has been enhanced by Reactor, refer to additional information below:  Assembly trace from producer [reactor.core.publisher.FluxHandleFuseable] :     reactor.core.publisher.Flux.handle(Flux.java:5913)  io.r2dbc.postgresql.PostgresqlResult.map(PostgresqlResult.java:107) Error has been observed at the following site(s):   *__Flux.handle ⇢ at io.r2dbc.postgresql.PostgresqlResult.map(PostgresqlResult.java:107) Original Stack Trace:       at io.r2dbc.postgresql.ExceptionFactory.createException(ExceptionFactory.java:96)       at io.r2dbc.postgresql.ExceptionFactory.createException(ExceptionFactory.java:65)       at io.r2dbc.postgresql.ExceptionFactory.handleErrorResponse(ExceptionFactory.java:132)      at io.r2dbc.postgresql.PostgresqlResult.lambda$map$2(PostgresqlResult.java:111)         at reactor.core.publisher.FluxHandleFuseable$HandleFuseableSubscriber.onNext(FluxHandleFuseable.java:176)       at reactor.core.publisher.FluxWindowPredicate$WindowFlux.drainRegular(FluxWindowPredicate.java:668)         at reactor.core.publisher.FluxWindowPredicate$WindowFlux.drain(FluxWindowPredicate.java:746)        at reactor.core.publisher.FluxWindowPredicate$WindowFlux.onNext(FluxWindowPredicate.java:788)       at reactor.core.publisher.FluxWindowPredicate$WindowPredicateMain.onNext(FluxWindowPredicate.java:239)      at io.r2dbc.postgresql.util.FluxDiscardOnCancel$FluxDiscardOnCancelSubscriber.onNext(FluxDiscardOnCancel.java:91)

Note that the following plain sql query works if ran directly on the DB:

select id from example where 'something' <<% text_field;

Also note: it is not a type cast issue. If I cast the searchKeyword to text, or if I inline it, a similar error is produced

Another note: if I change the code to use the strict_word_similarity function instead of the operator, it works. The issue is only with the operator