For detecting the existence of a key in a hstore, I need to run a query like this:
SELECT * FROM tbl WHERE hst ? 'foo'
However, that gives me a PDOException:
PDOException: SQLSTATE[HY093]: Invalid parameter number: no parameters were bound: SELECT * FROM tbl WHERE hst ? 'foo'
Is there any way to escape the question mark so PDO won't pick it up as a placeholder? I've tried with up to four backslashes, as well as a double question mark (??
), but nothing seems to persuade PDO to leave the question mark alone.
Use the function call form. According to the system catalogs, the hstore ?
operator uses the exist
function:
regress=# select oprname, oprcode from pg_operator where oprname = '?'; oprname | oprcode ---------+--------- ? | exist (1 row)
so you can write:
SELECT * FROM tbl WHERE exist(hst,'foo');
(Personally I'm not a big fan of hstore's operator-centric design and documentation, I think it discards the useful self-documenting properties of a function based interface without any real benefit and I usually use its function calls rather than its operators. Just because you can define operators doesn't mean you should.)
I had the same problem when searching on JSONB data. The full question is here
SELECT * FROM post WHERE locations ? :location;
The workaround on PostgreSQL 9.5
is similar:
SELECT * FROM post WHERE jsonb_exists(locations, :location);
I also opened a ticket at PHP bug tracing system
As Diabl0 mentioned, the proposed solution work but does not use the index. Tested with:
CREATE INDEX tempidxgin ON post USING GIN (locations);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With