How to query values with wildcards in PostgreSQL hstore

Question

I'm trying to query hstore for all the values of a certain key that match a search criteria.

I can get all the values for a certain key like this:

SELECT DISTINCT
svals(slice(data, ARRAY['Supplier']))
FROM "products"

I can also get a specific value:

SELECT DISTINCT
svals(slice(data, ARRAY['Supplier'])) AS sup
FROM "products"
WHERE data @> 'Supplier => Toshiba'

What I would really like is something like (this doesn't work):

SELECT DISTINCT
svals(slice(data, ARRAY['Supplier'])) AS sup
FROM "products"
WHERE data @> 'Supplier => %tosh%'

or:

SELECT DISTINCT
svals(slice(data, ARRAY['Supplier'])) AS sup
FROM "products"
WHERE lower(sup)
LIKE '%tosh%'

for case-insensitive search. How is this done?

willglynn · Accepted Answer · 2012-09-21T01:50:01.500

45

You can extract values by key from an hstore column with the -> operator.

SELECT data->'Supplier' AS sup
FROM products
WHERE lower(data->'Supplier') LIKE '%tosh%';

Additionally, like most expressions in PostgreSQL (excepting things like random()), you can index this value:

CREATE INDEX products_supplier_key ON products ((data->'Supplier'));
CREATE INDEX products_supplier_lowercase_key ON products ((lower(data->'Supplier')));

This would allow PostgreSQL to answer many such queries using the index instead of fetching each row and scanning the hstore column. See the notes on Index Types regarding index usage with LIKE.

edited Sep 21 '12 at 01:50

answered Sep 20 '12 at 20:35

willglynn

11,210
48
40

Worked like a charm. Thanks, willglynn! – Rob Gonzalez Sep 20 '12 at 22:42
1

Quick followup: is there a way to ensure that all keys are indexed like that automatically? – Rob Gonzalez Sep 20 '12 at 22:42
3

First answer: no. Each key needs to be in a separate index (assuming you want B-trees, which you do for that sort of matching), and adding lots of indexes is generally a bad idea. – willglynn Sep 21 '12 at 01:44
2

Second answer: [sure, why not](https://gist.github.com/3759322). PostgreSQL data structures are themselves represented by data structures, and PostgreSQL lets you run procedures, so you can do anything you want. Wrapping it in an `EXECUTE IMMEDIATE` -- or worse, folding it into a trigger -- is left as an exercise to the reader. – willglynn Sep 21 '12 at 01:44
willglynn: thanks again. You saved me a bunch of time on this one. I really appreciate it. – Rob Gonzalez Sep 25 '12 at 12:56

score 8 · Answer 2 · answered Oct 04 '12 at 17:35

One caveat to willglynn's answer for anyone seeing this in the future -- the original query and the new query have slightly different behavior. Namely,

SELECT data->'Supplier' AS sup
FROM products;

will include a NULL value assuming at least one row doesn't have an assignment for Supplier.

SELECT DISTINCT
svals(slice(data, ARRAY['Supplier']))
FROM "products"

will not return the NULL value.

How to query values with wildcards in PostgreSQL hstore

2 Answers2

Linked