Postgres find by hstore key / value

Question

I have a table

create table device_entity
(
 id uuid not null primary key,
 name varchar(255),
 metadata hstore
);

This table has entries with 'country' = 'uk' in the metadata field.

This query returns all entries where the key 'country' is present in the metadata field.

SELECT * from device_entity where metadata ? 'country'

How can I select all entries where the key 'country' = 'uk'?

• user1822: Use the -> operator - but nowadays I would prefer a jsonb column over hstore

Answer 1

SELECT * from device_entity where (metadata -> 'country') = 'uk';

This query will probably have quite slow performance which can probably be improved significantly by deploying the right inverted index.

• user1822: No need for an "inverted index". A B-Tree index on the expression should work just fine
• Erwin Brandstetter: Of course, a dedicated column "country" in the table would be best for ease of use, index, and performance.
• Jasen: b-tree would work if this is the only interesting key