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?

Answer 1

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.