Escaping hstore contains operators in a JDBC Prepared statement

Question

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:

• Is there anything in the JDBC spec which allows a ? to be escaped and be anything other than a parameter placeholder?
• Is there any better work around for this issue than just using plain Statements with variables manually inserted into the query string?

Hardison

Don't know any JDBC but does d = $1 work? – Binate

No, that's not valid syntax. Throws a syntax error at the $1. – Hardison

Many PostgreSQL interfaces prefer numbered placeholders (i.e. $1, $2, ...), I guess JDBC isn't one of them. – Binate

Answer 1

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.

Postgres hstore documentation

Grath

Thanks, I didn't see the verbose operator down there. – Hardison

It's important to note that exist(c, 'foo') does not use an index. Check a query plan with and without to see the difference. – Visakhapatnam

Answer 2

There is a discussion about this issue on pgsql-hackers mailing list: http://grokbase.com/t/postgresql/pgsql-hackers/1325c6ys9n/alias-hstores-to-so-that-it-works-with-jdbc

For now I like most this workaround which also supports indexes:

CREATE FUNCTION exist_inline (hstore, text) RETURNS bool AS $$ SELECT $1 ? $2; $$ LANGUAGE sql;

Mullet

Answer 3

You can use this query to find the function backing an operator in PostgreSQL like this. In your example:

SELECT 
  oprname, 
  oprcode || '(' || format_type(oprleft,  NULL::integer) || ', ' 
                 || format_type(oprright, NULL::integer) || ')' AS function
FROM pg_operator 
WHERE oprname LIKE '?%'
AND (SELECT oid FROM pg_type WHERE typname = 'hstore') IN (oprleft, oprright);

This produces:

|oprname|function                  |
|-------|--------------------------|
|?      |exist(hstore, text)       |
|?|     |exists_any(hstore, text[])|
|?&     |exists_all(hstore, text[])|

See also a related question about using JSON operators containing ?. Note that the function usage may not profit from the same indexing capability when using a GIN index on your HSTORE column.

Deenadeenya

Answer 4

If you'd like to add multiple key-value pairs using PreparedStatement then you can do:

PreparedStatement ps = c.prepareStatement(
                     "insert into xyz(id, data) values(?, hstore(?, ?))");

ps.setLong(1, 23456L);
ps.setArray(2, c.createArrayOf("text", new String[]{"name", "city"}));
ps.setArray(3, c.createArrayOf("text", new String[]{"Duke", "Valley"}));

This will insert: 23456, 'name=>Duke, city=>Valley'

Positively

this has nothing with the question asked – Orthohydrogen