Hstore

Hstore - contrib module for storing (key,value) pairs

Online version

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.

Motivation

Many attributes rarely searched, semistructural data, lazy DBA

Authors

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

License

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

Operations

  • 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

Functions

  • 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

Indices

Module provides index support for '@' and '~' operations.

create index hidx on testhstore using gist(h);

Note

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;

Examples

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