From: | Peter Mortier <peter(dot)mortier(at)gmail(dot)com> |
---|---|
To: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | problem with pgjdbc prepared statements and new jsonb exists operator (?) |
Date: | 2015-01-19 20:36:24 |
Message-ID: | CAFxe7BBHtgryUVZtrjKH3Bw9Ms7jkEL5KAz_Ui67Q=sFyBXSiw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
Hello,
I'm testing out the new jsonb functionality in Postgresql 9.4 and ran
across an issue with some of the newly introduced operators for the jsonb
datatype described here (
http://www.postgresql.org/docs/9.4/static/functions-json.html#FUNCTIONS-JSONB-OP-TABLE)
when used with JDBC prepared statements.
I have created the following table:
create table "JsonTest0" ("id" BIGSERIAL NOT NULL PRIMARY KEY,"json" jsonb
DEFAULT ' {"a":"v1","b":2} ' NOT NULL)
When I'm creating and executing a simple statement using the exist operator
then I get expected results back:
connection.createStatement.executeQuery("select x2.\"json\" from
\"JsonTest0\" x2 where (x2.\"json\" ? 'c') = true")
When I try to execute the same query using a prepared statement, I get the
following:
connection.prepareStatement("select x2.\"json\" from \"JsonTest0\" x2 where
(x2.\"json\" ? 'c') = true").executeQuery()
org.postgresql.util.PSQLException: No value specified for parameter 1.
at
org.postgresql.core.v3.SimpleParameterList.checkAllParametersSet(SimpleParameterList.java:216)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:244)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:560)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302)
So it looks like the ? character, which is used as the jsonb exists
operator is confusing the prepared statement parser, which treats it as a
parameter substitution instead.
Is this expected behaviour and am I stuck with non-prepared statements when
using the new ?, ?& and ?| jsonb operators ?
Any other workarounds, like escaping or function aliases that you may know
of ?
I tested with both: 9.3-1100-jdbc41 and 9.4-1200-jdbc41-SNAPSHOT versions
BR,
Peter Mortier
From | Date | Subject | |
---|---|---|---|
Next Message | Dave Cramer | 2015-01-19 21:28:11 | Re: problem with pgjdbc prepared statements and new jsonb exists operator (?) |
Previous Message | dmp | 2015-01-19 17:22:57 | Re: Problem with DATE |