On Mon, Aug 21, 2023 at 11:02 AM [email protected] <[email protected]> wrote:
> OK, so I'm now even more confused. I am assuming that there is a direct > way of asking for records whose hstore field has a single value. Is this a > correct assumption? A better question to ask yourself is: "What PostgreSQL function/operator does what I want?" You can review https://www.postgresql.org/docs/current/hstore.html to see the functions and operators available. If no PostgreSQL function/operator does what you want, that pg_hstore_ops is not going to be able to help you. Looking at that page, it appears like you should be able to use something like this: DB.extension :pg_array Sequel.extension :pg_hstore_ops, :pg_array_ops DB[:table].where(Sequel.hstore_op(:hstore_column).values.contains(["your_value"])) # SELECT * FROM "table" WHERE (avals("hstore_column") @> ARRAY['your value']) To break that down: * values comes from pg_hstore_ops extension * contains comes from pg_array_ops extension * The pg_array extension is needed to convert the ["your_value"] to a PostgreSQL array > I'm also looking at the methods here: > http://sequel.jeremyevans.net/rdoc-plugins/files/lib/sequel/extensions/pg_hstore_rb.html > > It shows a .has_value? method, which is exactly what I'm looking for. These describe Ruby methods you can call on a Sequel::Postgres::HStore instances, not functions/operators you can use in queries. It seems I have to grab that whole dataset and perform a proc looking for > the value. > That's always an option, though I think the above would work. It's also possible to index the avals call so the query can use an index scan instead of a sequential scan. Thanks, Jeremy -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/sequel-talk/CADGZSSfAok-zop-%2BqTMemBoy7ROwcJd5FHCQ59Uze%2BHAW7ctwg%40mail.gmail.com.
