This is a quick reference on how to install the HSTORE contrib module into your PostgreSQL database as well as a few example queries explaining how to interface with and query the HSTORE data type.
To install the hstore contrib module, simply run this query in the database you wish to use hstore in:
CREATE EXTENSION hstore;
In these examples, we assume you have a table named “hstore_test” with a single column named “data” which is of the data type “hstore”.
Set the contents of an hstore
INSERT INTO hstore_test (data) VALUES ('"key1"=>"value1", "key2"=>"value2", "key3"=>"value3"')
Delete a key from an hstore
UPDATE hstore_test SET data = delete(data, 'key2')
Add a key/value to an hstore / replace the value of an existing key within an hstore
UPDATE hstore_test SET data = data || '"key4"=>"some value"'::hstore
Return records where hstore contains a specific key
SELECT * FROM hstore_test WHERE data ? 'key4'
Return records where hstore does not contain a specific key
SELECT * FROM hstore_test WHERE NOT data ? 'key5'
Returns records where a specific key/value are present within an hstore.
SELECT * FROM hstore_test WHERE data @> '"key4"=>"some value"'::hstore
Tags: HSTORE, PostgreSQL