PostgreSQL jsonb, `?` and JDBC

Question

I am using PostgreSQL 9.4 and the awesome JSONB field type. I am trying to query against a field in a document. The following works in the psql CLI

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

When I try to run the same query via my Scala app, I'm getting the error below. I'm using Play framework and Anorm, so the query 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)

(in my actual queries there are more parameters)

I can get around this by casting my parameter to type jsonb and using the @> operator to check containment.

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

I'm not too keen on the work around. I don't know if there are performance penalties for the cast, but it's extra typing, and non-obvious.

Is there anything else I can do?

Brade

Either you don't paste the code properly or you have syntax error: SQL(s"SELECT id FROM program WHERE document -> 'dept' ? {dept} .on('dept -> "CS") → on must be apply on SQL(...), not be part of the statement string as it seems to be there = SQL("...").on(...). Note that string interpolation there is useless. – Deferment

I didn't paste the whole call to SQL because it's irrelevant to my quesion. There is a call to apply after the on – Brade

I would have been better to paste a code which has valid syntax if you want appropriate answer. – Deferment

There seems to be a fix in the latest JDBC driver that allows escaping the questionmark by doubling it: ??. See postgresql.org/message-id/… – Excellence

Answer 1

As a workaround to avoid the ? operator, you could create a new operator doing exactly the same.

This is the code of the original operator:

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 any conflicts, like #-# 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 operator in your code and it should work.

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

Fuji

Answer 2

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

See Does the JDBC spec prevent '?' from being used as an operator (outside of quotes)? and the discussion on jdbc-spec-discuss.

The current PostgreSQL JDBC driver will transform all occurrences (outside text or comments) of a question mark to a PostgreSQL specific parameter placeholder. I am not sure if the PostgreSQL JDBC project has done anything (like introducing an escape as discussed in the links above) to address this yet. A quick look at the code and documentation suggests they didn't, but I didn't dig too deep.

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

Delisadelisle

See my answer (https://mcmap.net/q/821757/-postgresql-jsonb-and-jdbc) - escaping is available and described (as of feb 2016) in the PosgreSQL documentation. – Rois

Answer 3

I had the same issue a couple of days ago and after some investigation I found this.

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

In JDBC, the question mark (?) is the placeholder for the positional parameters of a PreparedStatement. There are, however, a number of PostgreSQL operators that contain a question mark. To keep such question marks in a SQL statement from being interpreted as positional parameters, use two question marks (??) as escape sequence. You can also use this escape sequence in a Statement, but that is not required. Specifically only in a Statement a single (?) can be used as an operator.

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

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

... and MyBatis for creating the SQL queries and it seemed to work well. Seemed easier / cleaner than creating an PostgreSQL operator.

SQL went from e.g.

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

... to ...

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

Hopefully this works with your scenario!

Rois

Answer 4

As bob said just use ?? instead of ?

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

Nest