{"action":"created","issue":{"url":"https://api.github.com/repos/pgjdbc/pgjdbc/issues/265","labels_url":"https://api.github.com/repos/pgjdbc/pgjdbc/issues/265/labels{/name}","comments_url":"https://api.github.com/repos/pgjdbc/pgjdbc/issues/265/comments","events_url":"https://api.github.com/repos/pgjdbc/pgjdbc/issues/265/events","html_url":"https://github.com/pgjdbc/pgjdbc/issues/265","id":60619756,"number":265,"title":"PgJDBC setString(...) can't be used to pass Pg custom types, json, etc","user":{"login":"ringerc","id":274337,"avatar_url":"https://avatars.githubusercontent.com/u/274337?v=3","gravatar_id":"","url":"https://api.github.com/users/ringerc","html_url":"https://github.com/ringerc","followers_url":"https://api.github.com/users/ringerc/followers","following_url":"https://api.github.com/users/ringerc/following{/other_user}","gists_url":"https://api.github.com/users/ringerc/gists{/gist_id}","starred_url":"https://api.github.com/users/ringerc/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/ringerc/subscriptions","organizations_url":"https://api.github.com/users/ringerc/orgs","repos_url":"https://api.github.com/users/ringerc/repos","events_url":"https://api.github.com/users/ringerc/events{/privacy}","received_events_url":"https://api.github.com/users/ringerc/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/pgjdbc/pgjdbc/labels/compat-other-vendor-drv","name":"compat-other-vendor-drv","color":"ededed"},{"url":"https://api.github.com/repos/pgjdbc/pgjdbc/labels/enhancement","name":"enhancement","color":"DDDDDD"},{"url":"https://api.github.com/repos/pgjdbc/pgjdbc/labels/needs-serverside-change","name":"needs-serverside-change","color":"e102d8"}],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":1,"created_at":"2015-03-11T07:17:44Z","updated_at":"2015-03-11T07:19:29Z","closed_at":null,"body":"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.\r\n\r\nThis typically fails with an error like:\r\n\r\n```\r\nCaused by: org.postgresql.util.PSQLException: ERROR: column \"myfield\" is of type uuid but expression is of type text\r\nHint: You will need to rewrite or cast the expression.\r\n```\r\n\r\nWhere possible, solve this by putting explicit casts in your SQL, e.g.\r\n\r\n    UPDATE ... SET myxmlfield = CAST(? AS xml)\r\n\r\nIf 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. \r\n\r\nAnother option is to use `PGobject` to create a custom value wrapper. [Per the example written by Marcus](http://www.pateldenish.com/2013/05/inserting-json-data-into-postgres-using-jdbc-driver.html#comment-1846) for `json`:\r\n\r\n```\r\nString yourJsonString = “{\\”username\\”:\\”denish\\”,\\”posts\\”:10122,\\”emailaddress\\”:\\”denish@omniti.com\\”}”;\r\n\r\nPGobject jsonObject = new PGobject();\r\njsonObject.setType(“json”);\r\njsonObject.setValue(yourJsonString);\r\n\r\nPreparedStatement preparedStatement = …\r\npreparedStatement.setObject(1, jsonObject);\r\n```\r\n\r\nFinally, you can create an implicit `WITH FUNCTION` cast in the database. Do *not* use a `WITHOUT FUNCTION` cast, you will bypass validation!  See [this Stack Overflow post](http://stackoverflow.com/q/15974474/398670) for an example.\r\n\r\nSome discussion on this topic can be found at:\r\n\r\n* http://www.postgresql.org/message-id/54096082.1090009@2ndquadrant.com\r\n* http://www.postgresql.org/message-id/CACTajFZ8+hg_kom6QiVBa94Kx9L3XUqZ99RdUsHBFkSb1MoCPQ@mail.gmail.com\r\n* http://www.postgresql.org/message-id/52E87EB0.7010504@2ndquadrant.com\r\n* http://www.postgresql.org/message-id/28690.1360299322@sss.pgh.pa.us"},"comment":{"url":"https://api.github.com/repos/pgjdbc/pgjdbc/issues/comments/78215772","html_url":"https://github.com/pgjdbc/pgjdbc/issues/265#issuecomment-78215772","issue_url":"https://api.github.com/repos/pgjdbc/pgjdbc/issues/265","id":78215772,"user":{"login":"ringerc","id":274337,"avatar_url":"https://avatars.githubusercontent.com/u/274337?v=3","gravatar_id":"","url":"https://api.github.com/users/ringerc","html_url":"https://github.com/ringerc","followers_url":"https://api.github.com/users/ringerc/followers","following_url":"https://api.github.com/users/ringerc/following{/other_user}","gists_url":"https://api.github.com/users/ringerc/gists{/gist_id}","starred_url":"https://api.github.com/users/ringerc/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/ringerc/subscriptions","organizations_url":"https://api.github.com/users/ringerc/orgs","repos_url":"https://api.github.com/users/ringerc/repos","events_url":"https://api.github.com/users/ringerc/events{/privacy}","received_events_url":"https://api.github.com/users/ringerc/received_events","type":"User","site_admin":false},"created_at":"2015-03-11T07:19:29Z","updated_at":"2015-03-11T07:19:29Z","body":"Search hints:\r\n\r\n```\r\nERROR: column \"myfield\" is of type jsonb but expression is of type text\r\nERROR: column \"myfield\" is of type json but expression is of type text\r\nERROR: column \"myfield\" is of type xml but expression is of type text\r\nERROR: column \"myfield\" is of type hstore but expression is of type text\r\n```"}}