**Last edit**

**Changed:**

< * GIN support for hstore - PostgreSQL 8.3, **Feb**, **04, 2008**

**to**

> * GIN support for hstore - PostgreSQL 8.3, **May 23**, **2007**

History:

- The first (unpublished) version of hstore was released May 16, 2003 for PostgreSQL 7.3 (3 years before JSON).
- Hstore is a part of PostgreSQL distribution since 8.2 version ! Dec, 05, 2006. Thread about inclusion hstore into main tarball - [[1]], May 03, 2006.
- Nicolai Petri complained about removing hstore from contrib [[2]]
- GIN support for hstore - PostgreSQL 8.3, May 23, 2007 [[3]]
- Andrew Gierth improved hstore [[4]] for 9.0 (Sep, 20, 2010)
- Nested hstore with array support [[5]] - WIP, prototype to be presented at PGCon, May, 21, 2013.

Many attributes rarely searched, semistructural data, lazy DBA

- Oleg Bartunov <oleg@sai.msu.su>, Moscow, Moscow University, Russia
- Teodor Sigaev <teodor@sigaev.ru>, Moscow, Delta-Soft Ltd.,Russia

Stable version, included into PostgreSQL distribution, released under BSD license. Development version, available from this site, released under the GNU General Public License, version 2 (June 1991).

- hstore → text - get value , perl analogy $h{key}

select 'a=>q, b=>g'->'a'; ? ------ q

- 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

- 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) - returns
**'true***if key is exists in hstore and***false**otherwise.

regression=# select exist('a=>1','a'); exists ---------- 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);

Use parenthesis in select below, because priority of 'is' is higher than that of '->'

select id from entrants where (info->'education_period') is not null;

- add key

update tt set h=h||'c=>3';

- delete key

update tt set h=delete(h,'k1');

- Statistics

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 ...................