[Dbix-class] Using Postgres JSONB operators in queries

Darren Duncan darren at darrenduncan.net
Wed Dec 17 04:19:06 GMT 2014

On 2014-12-04 3:38 PM, Augustus Saunders wrote:
> Hi all, I have been unable to find a way to use some of the new JSONB operators
> in Postgres 9.4 with DBIx::Class. A quick search for JSONB on the email archive
> site didn’t turn up any results, so I thought I would ask.
> In particular, ?, ?&, and ?| are now operators, and we run into problems with
> the DBI placeholder being ?. I read that putting single quotes around the
> question mark would allow a literal question mark in DBI, but I can’t seem to
> make this work from DBIx::Class. Can anybody tell me whether this is currently
> possible, if so how, and if not what might be involved or where in the code to
> look? Thanks-

At the DBI level anyway, you can use this:


If you set that DBD::Pg option, then literal ? are not treated as placeholders 
and you instead have to use other forms like $1,$2 etc or named placeholders; on 
the plus side, those also let you use the same placeholder more than once in a 
statement rather than having to pass in the same bind value multiple times.

This being said, I don't know if DBIx::Class is compatible with that way of 
using PostgreSQL, not that this can't change.

-- Darren Duncan

More information about the DBIx-Class mailing list