Jooq结合PostgreSQL使用pg_trgm运算符报‘operator does not exist’错误

问题背景 #

使用Java、Spring Boot、jOOQ、带pg_trgm扩展的PostgreSQL及R2DBC技术栈，尝试通过pg_trgm的<<%运算符实现搜索时，jOOQ抛出operator does not exist错误，但直接在数据库执行相同逻辑的SQL可正常运行，改用strict_word_similarity函数也能正常工作。

报错场景代码 #

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

// 写法2
DSL.resultQuery("select 'a' <<% 'a';")

报错信息 #

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

问题原因 #

PostgreSQL的pg_trgm扩展中，<<%运算符仅针对TEXT类型定义。直接执行SQL时，PostgreSQL会自动将字符串常量隐式转为TEXT类型；但jOOQ生成SQL时，默认会将字符串参数绑定为VARCHAR或unknown类型，导致数据库无法匹配到对应的运算符。

解决方案 #

方案1：显式指定参数类型为TEXT #

通过DSL.val()的重载方法指定参数类型为SQLDataType.TEXT，同时确保字段类型匹配（若字段是VARCHAR可显式转为TEXT）：

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

方案2：自定义jOOQ运算符绑定 #

注册<<%运算符为jOOQ的自定义运算符，让框架正确处理类型匹配：

// 定义自定义运算符
public static final Operator<String> STRICT_WORD_SIMILARITY = 
    new Operator<>("<<%", SQLDataType.BOOLEAN, OperatorType.BINARY);

// 使用自定义运算符
String searchKeyword = "something";
DSL.select(Tables.EXAMPLE.ID)
   .from(Tables.EXAMPLE)
   .where(DSL.condition(STRICT_WORD_SIMILARITY, 
       DSL.val(searchKeyword, SQLDataType.TEXT), 
       Tables.EXAMPLE.TEXT_FIELD))
   .fetch();

方案3：使用raw SQL并强制类型转换 #

如果需要直接写SQL字符串，显式转换两边的类型为TEXT：

DSL.resultQuery("select id from example where cast(? as text) <<% cast(text_field as text)", 
    DSL.val(searchKeyword))
   .fetch();

验证 #

执行上述修改后的代码，数据库会正确识别TEXT类型的<<%运算符，即可正常完成搜索逻辑。

内容的提问来源于stack exchange，提问作者fedpet