PostgreSQL jsonb, `?` And JDBC

Question

I am using PostgreSQL 9.4 and the cool JSONB field type. I am trying to request a field in a document. In psql CLI works

SELECT id FROM program WHERE document -> 'dept' ? 'CS' 

When I try to run the same request through my Scala application, I get the error message below. I use Play framework and Anorm, so the request looks like this:

 SQL(s"SELECT id FROM program WHERE document -> 'dept' ? {dept}") .on('dept -> "CS") .... 

SQLException :: No value specified for parameter 5. (SimpleParameterList.java:223)

(there are more options in my real queries)

I can get around this by setting my parameter for jsonb input and using the @> operator to check for containment.

 SQL(s"SELECT id FROM program WHERE document -> 'dept' @> {dept}::jsonb") .on('dept -> "CS") .... 

I'm not too keen on work. I do not know if there are penalties for execution, but this is an extra input and unobvious.

Is there anything else I can do?

Answer 1

How to workaround to avoid? operator, you can create a new operator by doing the same.

This is the source statement code:

 CREATE OPERATOR ?( PROCEDURE = jsonb_exists, LEFTARG = jsonb, RIGHTARG = text, RESTRICT = contsel, JOIN = contjoinsel); SELECT '{"a":1, "b":2}'::jsonb ? 'b'; -- true 

Use a different name without conflict, for example # - # and create a new one:

 CREATE OPERATOR #-#( PROCEDURE = jsonb_exists, LEFTARG = jsonb, RIGHTARG = text, RESTRICT = contsel, JOIN = contjoinsel); SELECT '{"a":1, "b":2}'::jsonb #-# 'b'; -- true 

Use this new statement in your code and it should work.

Check pgAdmin -> pg_catalog -> Operators for all operators that use? in the name.

Answer 2

In JDBC (and standard SQL), the question mark is reserved as a replacement parameter. Other uses are not permitted.

See Does the JDBC Specification Provide '?' from use as an operator (outside quotation marks)? and discussion of jdbc-spec-discuss .

The current PostgreSQL JDBC driver converts all occurrences (outside the text or comments) of the question mark into a special PostgreSQL placeholder. I am not sure if the PostgreSQL JDBC project did anything (for example, by entering escape, as described in the links above) to solve this problem. A quick look at the code and documentation suggests that they did not, but I did not dig too deeply.

Addendum : as shown in bobmarksie's answer , current versions of the PostgreSQL JDBC driver now support escaping the question mark by doubling it (i.e. use ?? instead of ? ).

Answer 3

I had the same problem a few days ago, and after some investigation, I found this.

https://jdbc.postgresql.org/documentation/head/statement.html

In JDBC, the question mark ( ? ) Is a placeholder for the PreparedStatement positional parameters. However, there are a number of PostgreSQL statements that contain a question mark. To interpret such question marks in an SQL expression as positional parameters, use two question marks ( ?? ) as an escape sequence. You can also use this escape sequence in Statement, but this is not required. In particular, only in the instructions can one ( ? ) Be used as an operator.

Using two question marks seemed to work for me - I used the following driver (illustrated using the maven dependency) ...

  <dependency> <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> <version>9.4-1201-jdbc41</version> </dependency> 

... and MyBatis for creating SQL queries, and it seems to work well. This seemed to be easier / cleaner than creating a PostgreSQL statement.

SQL switched from for example

 select * from user_docs where userTags ?| array['sport','property'] 

... to ...

 select * from user_docs where userTags ??| array['sport','property'] 

Hope this works with the script!