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'?

  • Use the -> operator - but nowadays I would prefer a jsonb column over hstore
    – user1822
    Commented Sep 17, 2022 at 9:39

1 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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.