I am using PostgreSQL 9.1.4 with hstore and the PostgreSQL JDBC driver (9.1-901.jdbc4).
I am trying to use the contains operators (?
, ?&
, ?|
) in a PreparedStatement, however the ?
character is parsed as a variable placeholder. Can this character be escaped to send the correct operator in the query?
An example:
PreparedStatement stmt = conn.prepareStatement("SELECT a, b FROM table1 WHERE c ? 'foo' AND d = ?");
stmt.setInt(1, dValue);
stmt.executeQuery();
In this form the following example would raise an exception:
org.postgresql.util.PSQLException: No value specified for parameter 2.
Update:
After investigating the query parser in the pgjdbc driver this snippet seems to indicate that it is not possible to escape the ?
character. The questions that remain are:
?
to be escaped and be anything other than a parameter placeholder?Effectively, it looks like the java SQL parser is not hstore compliant.
But since the syntax c ? 'foo'
is equivalent to exist(c, 'foo')
, you can easily workaround this problem. Have a look at the following page to see what the verbose operators for hstore are.