How to query values with wildcards in PostgreSQL hstore
You can extract values by key from an hstore column with the -> operator.
SELECT data->'Supplier' AS supFROM productsWHERE 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.
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 supFROM products;will include a NULL value assuming at least one row doesn't have an assignment for Supplier.
SELECT DISTINCTsvals(slice(data, ARRAY['Supplier']))FROM "products"will not return the NULL value.