Jooq and postgres: pg_trgm operator in pure sql produces error 'operator does not exist'

php小编苹果为您介绍一则Java相关的问题：在使用Jooq和PostgreSQL时，可能会出现“运算符不存在”错误，原因是在纯SQL中使用pg_trgm运算符不被识别。本文将详细解释这一问题的背景和解决方法。

问题内容

我正在使用的技术：java、spring boot、jooq、带有 pg_trgm 扩展的 postgres、r2dbc。

我尝试使用 pg_trgm 运算符在 postgres 上进行简单搜索，但 jooq 抛出错误。

代码示例：

string searchkeyword = "something";
dsl.select(tables.example.id)
          .from(tables.example)
          .where(dsl.condition("{0} <<% {1}", dsl.val(searchkeyword), tables.example.text_field))

或者更简单：

dsl.resultquery("select 'a' <<% 'a';")

产生错误 operator 不存在：字符变化 <<% text。

堆栈跟踪：

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)

请注意，如果直接在数据库上运行，以下纯 sql 查询将有效：

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

另请注意：这不是类型转换问题。如果我将 searchkeyword 转换为文本，或者将其内联，则会产生类似的错误

另一个注意事项：如果我更改代码以使用 strict_word_similarity 函数而不是运算符，它就可以工作。问题仅出在运营商

解决方法

您可以将绑定变量显式转换为文本：

dsl.condition("{0}::text <<% {1}", ...)

或者内联它而不是绑定它：

DSL.condition("{0} <<% {1}", DSL.inline(searchKeyword), Tables.EXAMPLE.TEXT_FIELD)

The above is the detailed content of Jooq and postgres: pg_trgm operator in pure sql produces error 'operator does not exist'. For more information, please follow other related articles on the PHP Chinese website!