hstore
A contrib module providing a data type for storing key/value pairs
hstore is a contrib module providing a data type (hstore) for storing key/value pairs. This enables storage of sets of key/value pairs within a single PostgreSQL value.
hstore was added in PostgreSQL 8.2.
Installation
Assuming the hstore module is available on your system, install it (as a superuser) into the database it is to be used with
CREATE EXTENSION hstore;
Note that this extension will install a number (58 as of PostgreSQL 12) of functions into the schema where it is created, so it may be prefereable to install it into a dedicated schema with:
CREATE EXTENSION hstore SCHEMA someschema;
Change history
Work-in-progress
- PostgreSQL 14
- support for subscripting of
hstorevalues added (commit 0ec5f7e7)
- support for subscripting of
- PostgreSQL 13
- marked as a
trusted extension(commit eb67623c)
- marked as a
- PostgreSQL 9.4 (1.3)
- functions
hstore_to_jsonb()andhstore_to_jsonb_loose()added (commit d9134d0a)
- functions
- PostgreSQL 9.1 (1.0)
- converted to an extension (commit 629b3af2)
- PostgreSQL 8.3
- PostgreSQL 8.2
- added (commit 642194ba)
Examples
A simple example of hstore usage:
testdb=# CREATE TABLE hstore_test(id SERIAL, val HSTORE); NOTICE: CREATE TABLE will create implicit sequence "hstore_test_id_seq" for serial column "hstore_test.id" CREATE TABLE testdb=# INSERT INTO hstore_test VALUES(DEFAULT, 'foo=>"bar"'); INSERT 0 1 testdb=# SELECT * from hstore_test; id | val ----+-------------- 1 | "foo"=>"bar" (1 row)
- Append a value to an HSTORE
-
testdb=# UPDATE hstore_test SET val = val || 'a=>"b"' WHERE id=1; UPDATE 1 testdb=# SELECT * FROM hstore_test ; id | val ----+------------------------ 1 | "a"=>"b", "foo"=>"bar" (1 row)
or alternatively:
testdb=# UPDATE hstore_test SET val = val || hstore('fish','finger') WHERE id=1; UPDATE 1 testdb=# SELECT * FROM hstore_test; id | val ----+------------------------ 1 | "a"=>"b", "foo"=>"bar", "fish"=>"finger" (1 row) - Find a value in an HSTORE
-
testdb=# SELECT id, val FROM hstore_test WHERE val->'foo' ='bar'; id | val ----+------------------------ 1 | "a"=>"b", "foo"=>"bar", "fish"=>"finger" (1 row)
- Retrieve an HSTORE's keys/values as a set
-
testdb=# SELECT * FROM EACH((SELECT val FROM hstore_test)); key | value ------+-------- x | y foo | bar fish | finger (3 rows)
- Delete a value from an HSTORE
-
testdb=*# UPDATE hstore_test SET val = val - 'x'::TEXT; UPDATE 1 testdb=*# SELECT * from hstore_test; id | val ----+-------------------------------- 2 | "foo"=>"bar", "fish"=>"finger" (1 row)
- Delete matching key/value pairs from an HSTORE
-
testdb=*# UPDATE hstore_test SET val = val - 'fish => finger'::hstore; UPDATE 1 testdb=*# SELECT * from hstore_test; id | val ----+--------------- 2 | "foo"=>"bar", (1 row)
- Create index on key in an HSTORE
-
CREATE INDEX index_foo ON hstore_test((val->'foo'));
References
- PostgreSQL documentation: hstore