How do I use PostgreSQL JSON(B) operators containing a question mark "?" via JDBC

Question

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 · Accepted Answer · 2016-11-01 21:45:40Z

23

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()) {
     ...
}

edited Nov 1, 2016 at 21:45

answered Jul 14, 2016 at 9:50

Lukas Eder

223k138 gold badges731 silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Mark Rotteveel Over a year ago

I believe they recently added a workaround for this to the PostgreSQL JDBC driver. See also stackoverflow.com/questions/14779896/… (and especially stackoverflow.com/a/30517450/466862 )

Lukas Eder Over a year ago

@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?

Dave Cramer Over a year ago

Nobody in PostgreSQL will add ?? operator... We are all well aware of the ? issues now.

depsypher Over a year ago

Worth noting that when using jsonb_exists, any GIN indexes will be ignored, whereas the question mark operators will consider indexes.

Lukas Eder Over a year ago

@depsypher: Thanks, very much worth noting. I've updated the answer

|

Laurenz Albe · Accepted Answer · 2023-03-21 07:23:40Z

4

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.

answered Mar 21, 2023 at 7:23

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Collectives™ on Stack Overflow

How do I use PostgreSQL JSON(B) operators containing a question mark "?" via JDBC

2 Answers 2

Use static statements, instead of prepared statements

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

11 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Use static statements, instead of prepared statements

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

11 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related