Java PreparedStatement error when calling jsonb_set()

Question

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

Try setObject instead of setString to give PostgreSQL full freedom to choose the data type. — Laurenz Albe
– Laurenz Albe, Commented Jun 14, 2022 at 6:37

Erwin Brandstetter · Accepted Answer · 2022-06-13 22:36:39Z

1

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"}'

or

'{some_key}'

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

Not:

'some_key'

See:

Pass array literal to PostgreSQL function

answered Jun 13, 2022 at 22:36

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jjanes · Accepted Answer · 2022-06-14 02:25:27Z

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.

Collectives™ on Stack Overflow

Java PreparedStatement error when calling jsonb_set()

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related