Java PreparedStatement调用PostgreSQL jsonb_set()函数报错问题

• 在pgAdmin中执行如下SQL可正常运行：

UPDATE table SET col = jsonb_set(col,'{"some_key"}', '{"items":["banana"]}' ::jsonb)

• 调试确认Java代码生成的SQL逻辑与上述语句完全一致，但执行抛出异常，对应Java实现代码如下：

final String sql = "UPDATE table SET col = jsonb_set(col,?, ? ::jsonb) ";
try {
    connection = defaultDatabase.getConnection();
    stmt = connection.prepareStatement(sql);
    stmt.setString(1, keyName);
    stmt.setString(2, keyValue);
    stmt.execute();
} catch (SQLException e) {
    logger.error("error:", e.getMessage());
} finally {
    DbUtils.closeQuietly(connection);
    DbUtils.closeQuietly(stmt);
}

• 具体报错信息：

ERROR: function jsonb_set(jsonb, character varying, jsonb) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.
  Position: 40

PostgreSQL中jsonb_set函数的标准签名要求第二个入参（JSON路径参数）为text[]（文本数组）类型。
在pgAdmin中直接书写的'{"some_key"}'会被数据库直接解析为text[]类型的数组字面量，符合函数参数要求；但JDBC调用setString()给第一个占位符传参时，驱动会将该参数标记为character varying（普通字符串）类型，和函数要求的text[]类型不匹配，因此抛出找不到对应函数的错误。

两种方案选其一即可：

1. 改动最小的方案：给路径参数增加显式类型转换，仅修改SQL语句，无需调整Java传参逻辑

   -- 给第一个占位符增加::text[]类型强转
   UPDATE table SET col = jsonb_set(col,?::text[], ?::jsonb)
   

2. 类型更严谨的方案：使用JDBC标准数组类型传参，无需在SQL中写强转

   final String sql = "UPDATE table SET col = jsonb_set(col,?, ? ::jsonb) ";
   try {
       connection = defaultDatabase.getConnection();
       stmt = connection.prepareStatement(sql);
       // 按路径层级拆分元素，创建JDBC文本数组传入
       String[] pathElements = new String[]{"some_key"};
       Array pathArray = connection.createArrayOf("text", pathElements);
       stmt.setArray(1, pathArray);
       stmt.setString(2, keyValue);
       stmt.execute();
   } catch (SQLException e) {
       logger.error("error:", e.getMessage());
   } finally {
       DbUtils.closeQuietly(connection);
       DbUtils.closeQuietly(stmt);
   }
   

   如果传入的keyName本身就是{"xxx"}格式的数组字符串，优先选择第一种方案。

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