hstore The hstore module is usefull for storing (key,value) pairs. This module can be useful in different scenarios: case with many attributes rarely searched, semistructural data or a lazy DBA. hstore -> text - get value , perl analogy $h{key} select 'a=>q, b=>g'->'a'; ? ------ q Note the use of parenthesis in the select below, because priority of 'is' is higher than that of '->': SELECT id FROM entrants WHERE (info->'education_period') IS NOT NULL; hstore || hstore - concatenation, perl analogy %a=( %b, %c ); regression=# select 'a=>b'::hstore || 'c=>d'::hstore; ?column? -------------------- "a"=>"b", "c"=>"d" (1 row) but, notice regression=# select 'a=>b'::hstore || 'a=>d'::hstore; ?column? ---------- "a"=>"d" (1 row) text => text - creates hstore type from two text strings select 'a'=>'b'; ?column? ---------- "a"=>"b" hstore @> hstore - contains operation, check if left operand contains right. regression=# select 'a=>b, b=>1, c=>NULL'::hstore @> 'a=>c'; ?column? ---------- f (1 row) regression=# select 'a=>b, b=>1, c=>NULL'::hstore @> 'b=>1'; ?column? ---------- t (1 row) hstore <@ hstore - contained operation, check if left operand is contained in right (Before PostgreSQL 8.2, the containment operators @> and <@ were respectively called @ and ~. These names are still available, but are deprecated and will eventually be retired. Notice that the old names are reversed from the convention formerly followed by the core geometric datatypes!) akeys(hstore) - returns all keys from hstore as array regression=# select akeys('a=>1,b=>2'); akeys ------- {a,b} skeys(hstore) - returns all keys from hstore as strings regression=# select skeys('a=>1,b=>2'); skeys ------- a b avals(hstore) - returns all values from hstore as array regression=# select avals('a=>1,b=>2'); avals ------- {1,2} svals(hstore) - returns all values from hstore as strings regression=# select svals('a=>1,b=>2'); svals ------- 1 2 delete (hstore,text) - delete (key,value) from hstore if key matches argument. regression=# select delete('a=>1,b=>2','b'); delete ---------- "a"=>"1" each(hstore) - return (key, value) pairs regression=# select * from each('a=>1,b=>2'); key | value -----+------- a | 1 b | 2 exist (hstore,text) hstore ? text - returns 'true if key is exists in hstore and false otherwise. regression=# select exist('a=>1','a'), 'a=>1' ? 'a'; exist | ?column? -------+---------- t | t defined (hstore,text) - returns true if key is exists in hstore and its value is not NULL. regression=# select defined('a=>NULL','a'); defined --------- f Module provides index support for '@>' and '?' operations. CREATE INDEX hidx ON testhstore USING GIST(h); CREATE INDEX hidx ON testhstore USING GIN(h); Add a key: UPDATE tt SET h=h||'c=>3'; Delete a key: UPDATE tt SET h=delete(h,'k1'); hstore type, because of its intrinsic liberality, could contain a lot of different keys. Checking for valid keys is the task of application. Examples below demonstrate several techniques how to check keys statistics. Simple example SELECT * FROM each('aaa=>bq, b=>NULL, ""=>1 '); Using table SELECT (each(h)).key, (each(h)).value INTO stat FROM testhstore ; Online stat SELECT key, count(*) FROM (SELECT (each(h)).key FROM testhstore) AS stat GROUP BY key ORDER BY count DESC, key; key | count -----------+------- line | 883 query | 207 pos | 203 node | 202 space | 197 status | 195 public | 194 title | 190 org | 189 ................... Oleg Bartunov oleg@sai.msu.su, Moscow, Moscow University, Russia Teodor Sigaev teodor@sigaev.ru, Moscow, Delta-Soft Ltd.,Russia