16

PostgreSQL knows a couple of funky ASCII-art operators that use the question mark character in their names, for instance these JSON operators:

  • ? does the string exist as a top-level key within the JSON value?
  • ?| Do any of these array strings exist as top-level keys?
  • ?& Do all of these array strings exist as top-level keys?

The problem is that the official PostgreSQL JDBC driver does not seem to correctly parse SQL strings containing such operators. It assumes that the question mark is an ordinary JDBC bind variable. The following code...

try (PreparedStatement s = c.prepareStatement("select '{}'::jsonb ?| array['a', 'b']");
     ResultSet rs = s.executeQuery()) {
     ...
}

... throws an exception:

org.postgresql.util.PSQLException: Für den Parameter 1 wurde kein Wert angegeben.
    at org.postgresql.core.v3.SimpleParameterList.checkAllParametersSet(SimpleParameterList.java:225)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:190)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:424)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:161)
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:114)

How can I use this operator?

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509

2 Answers2

22

There are two possible workarounds:

Use static statements, instead of prepared statements

This is the simplest workaround, but you lose all the benefits from prepared statements (performance, SQL injection protection, etc.). However, this will work 

try (Statement s = c.createStatement();
     ResultSet rs = s.executeQuery("select '{}'::jsonb ?| array['a', 'b']")) {
     ...
}

Avoid the operator. Use a function instead (note: indexes might not be used)

Operators are just syntax sugar for a backing function that exists in the pg_catalog. Here's how to find the name of these functions:

SELECT 
  oprname, 
  oprcode || '(' || format_type(oprleft,  NULL::integer) || ', ' 
                 || format_type(oprright, NULL::integer) || ')' AS function
FROM pg_operator 
WHERE oprname = '?|';

The above yields:

oprname  function
----------------------------------------------------------------------------------
?|       point_vert(point, point)
?|       lseg_vertical(-, lseg)
?|       line_vertical(-, line)
?|       jsonb_exists_any(jsonb, text[])    <--- this is the one we're looking for
?|       exists_any(hstore, text[])

So, the simplest workaround is to just not use the operator, but the corresponding function instead:

try (PreparedStatement s = c.prepareStatement(
         "select jsonb_exists_any('{}'::jsonb, array['a', 'b']");
     ResultSet rs = s.executeQuery()) {
     ...
}
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • 2
    I believe they recently added a workaround for this to the PostgreSQL JDBC driver. See also http://stackoverflow.com/questions/14779896/does-the-jdbc-spec-prevent-from-being-used-as-an-operator-outside-of-quotes/14786469 (and especially http://stackoverflow.com/a/30517450/466862 ) – Mark Rotteveel Jul 14 '16 at 10:56
  • 4
    @MarkRotteveel: Intersting, thank you very much for the pointer. I'm personally not too big of a fan of the `??` escaping strategy. I'm sure that at some point, some PostgreSQL extension will add support for a `??` operator. What'll happen then? – Lukas Eder Jul 14 '16 at 11:01
  • 3
    Nobody in PostgreSQL will add ?? operator... We are all well aware of the ? issues now. – Dave Cramer Jul 14 '16 at 13:05
  • 3
    Worth noting that when using jsonb_exists, any GIN indexes will be ignored, whereas the question mark operators will consider indexes. – depsypher Nov 01 '16 at 19:35
  • 1
    @depsypher: Thanks, very much worth noting. I've updated the answer – Lukas Eder Nov 01 '16 at 21:45
  • Can you proves that *indexes might not be used* when we use the function instead of the operator ? – Ser Oct 16 '18 at 08:20
  • @Ser: Check out the execution plan to be sure: `EXPLAIN ANALYZE SELECT ..` – Lukas Eder Oct 16 '18 at 09:23
  • I'm not even sure if my index is used when the request is processed. Can you look at it ? https://gist.github.com/serut/15f16999d6bcf9ee43db5126b02ea6f1 The execution plan doesn't mention the index at all. – Ser Oct 16 '18 at 16:21
  • @Ser: please ask a new question – Lukas Eder Oct 16 '18 at 16:33
  • Sure https://stackoverflow.com/questions/52850061/indexing-a-jsonb-array-in-postgres – Ser Oct 17 '18 at 08:05
  • 1
    Ok @depsypher answer is hopefully wrong, indexes are used whether you use the operator or the function ! You just need to use the same expression in the query and in the index – Ser Oct 24 '18 at 09:31
2

The JDBC documentation describes how to use operators that contain a question mark:

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 an 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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263