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 (release notes; commit 642194ba).

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;

This extension will install a number (58 as of PostgreSQL 12) of functions into the schema where it is installed.

Change history

Work-in-progress

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'));
  • PostgreSQL documentation: hstore

Categories

Contrib module, Data type

See also

hstore_plperl, hstore_plpython, Hstore hacks