(This issue serves as documentation as much as a real bug, as the underlying problem is in PostgreSQL its self rather than PgJDBC)
PgJDBC users frequently use setString(...) to attempt to pass the textual representation of a non-string type like xml, json, jsonb, cidr, inet, hstore, etc etc as a parameter to a statement, e.g.
stmt = conn.prepareStatement("UPDATE ... SET myxmlfield = ?")
stmt.setString(1, "<xml/>");
This typically fails with an error like:
Caused by: org.postgresql.util.PSQLException: ERROR: column "myfield" is of type xml but expression is of type text
Hint: You will need to rewrite or cast the expression.
Where possible, solve this by putting explicit casts in your SQL, e.g.
UPDATE ... SET myxmlfield = CAST(? AS xml)
If you can't do that - for example, you're using an ORM or query generator - then for String inputs it can be worked around in PgJDBC by setting stringtype=unspecified in the JDBC connection options. This tells PostgreSQL that all text or varchar parameters are actually of unknown type, letting it infer their types more freely.
Another option is to use PGobject to create a custom value wrapper. Per the example written by Marcus for json:
String yourJsonString = “{\”username\”:\”denish\”,\”posts\”:10122,\”emailaddress\”:\”denish@omniti.com\”}”;
PGobject jsonObject = new PGobject();
jsonObject.setType(“json”);
jsonObject.setValue(yourJsonString);
PreparedStatement preparedStatement = …
preparedStatement.setObject(1, jsonObject);
Finally, you can create an implicit WITH FUNCTION cast in the database. Do not use a WITHOUT FUNCTION cast, you will bypass validation (or, if the types aren't binary compatible, crash the server)! See this Stack Overflow post for an example.
Some discussion on this topic can be found at:
(This issue serves as documentation as much as a real bug, as the underlying problem is in PostgreSQL its self rather than PgJDBC)
PgJDBC users frequently use
setString(...)to attempt to pass the textual representation of a non-string type likexml,json,jsonb,cidr,inet,hstore, etc etc as a parameter to a statement, e.g.This typically fails with an error like:
Where possible, solve this by putting explicit casts in your SQL, e.g.
If you can't do that - for example, you're using an ORM or query generator - then for String inputs it can be worked around in PgJDBC by setting
stringtype=unspecifiedin the JDBC connection options. This tells PostgreSQL that alltextorvarcharparameters are actually of unknown type, letting it infer their types more freely.Another option is to use
PGobjectto create a custom value wrapper. Per the example written by Marcus forjson:Finally, you can create an implicit
WITH FUNCTIONcast in the database. Do not use aWITHOUT FUNCTIONcast, you will bypass validation (or, if the types aren't binary compatible, crash the server)! See this Stack Overflow post for an example.Some discussion on this topic can be found at: