呼叫JSONB_SET（）时Java准备的错误

发布于 2025-02-06 22:13:35 字数 1062 浏览 4 评论 0 原文

当我在pgadmin中运行此SQL查询时，它可以正常工作。但是不在Java代码中，SQL字符串看起来像这样：

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

在调试器中，我可以看到与此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

原文

When I run this SQL query in pgAdmin it works. But not in the Java code, the SQL string looks like this:

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

In the debugger I can see that exactly same query is generated with this java code but it throws error:

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:

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

茶花眉 2025-02-13 22:13:41

正如第三个论点已被投入到JSONB一样，第二个论点应该被施放给文本数组。

UPDATE table SET col = jsonb_set(col, ?::text[], ?::jsonb)

在调试器中，我可以看到与此Java代码生成完全相同的查询

？你在里面看到了什么？我看到的是，它与您显示的其他查询并不完全相同，因为参数是单独发送的，并标有其类型。

当您将值插入查询中并使用简单协议（例如PGADMIN显然确实如此）时，它们的类型是未知的，因此数据库决定必须是文本[]以匹配函数签名。但是，当将其明确标记为角色变化时，数据库只是接受该标签，然后未能找到具有该类型签名的相应函数。

Just as the third argument has been cast to JSONB, the 2nd one should be cast to text array.

UPDATE table SET col = jsonb_set(col, ?::text[], ?::jsonb)

In the debugger I can see that exactly same query is generated with this java code

What debugger? And what do you see in it? What I see is that it is not exactly the same as that other query you show, because the params are sent separately and are labeled with their types.

When you interpolate the values into the query and use the simple protocol, like pgadmin apparently does, their types are unknown and so the database decides it must be text[] to match the function signature. But when it is explicitly labelled as being character varying, then the database just accepts that label, and then fails to find a corresponding function with that type signature.

回复收藏 0 原文