hstore

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