Java PreparedStatement error when calling jsonb

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

CodePudding user response：

The 2nd argument for jsonb_set() is text[] - an array - not varchar or text.

So pass your keyValue wrapped in an array literal like you did in your test from pgAdmin:

'{"some_key"}'

'{some_key}'

Double quotes are only needed to wrap special characters in the string.

Not:

'some_key'

See:

Pass array literal to PostgreSQL function

CodePudding user response：

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.