diff options
Diffstat (limited to 'contrib/hstore/README.hstore')
| -rw-r--r-- | contrib/hstore/README.hstore | 175 |
1 files changed, 175 insertions, 0 deletions
diff --git a/contrib/hstore/README.hstore b/contrib/hstore/README.hstore new file mode 100644 index 00000000000..89c67bf3703 --- /dev/null +++ b/contrib/hstore/README.hstore @@ -0,0 +1,175 @@ +Hstore - contrib module for storing (key,value) pairs + +[Online version] (http://www.sai.msu.su/~megera/oddmuse/index.cgi?Hstore) + +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 + + * isexists (hstore,text) - returns 'true if key is exists in hstore and false otherwise. + +regression=# select isexists('a=>1','a'); + isexists +---------- + t + + * isdefined (hstore,text) - returns true if key is exists in hstore and its value is not NULL. + +regression=# select isdefined('a=>NULL','a'); + isdefined +----------- + 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. + + o simple example + +select * from each('aaa=>bq, b=>NULL, ""=>1 '); + + o using table + +select (each(h)).key, (each(h)).value into stat from testhstore ; + + o 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 +...................
\ No newline at end of file |