This behaviour is quite annoying since JSON strings are accepted without problems when used as literal strings in SQL commands.
There is a already an issue for this in the postgres driver Github repository (even if the problem seems the be the serverside processing).
Besides using a cast (see answer of
@a_horse_with_no_name) in the sql string, the issue author offers two additional solutions:
- Use a parameter
stringtype=unspecified
in the JDBC connection URL/options.
This tells PostgreSQL that all text or varchar parameters are actually
of unknown type, letting it infer their types more freely.
- Wrap the parameter in a
org.postgresql.util.PGobject
:
PGobject jsonObject = new PGobject();
jsonObject.setType("json");
jsonObject.setValue(yourJsonString);
pstmt.setObject(11, jsonObject);