1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
|
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
LEGAL NOTICES: This module is released under BSD license (as PostgreSQL
itself)
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
(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!)
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)
* 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
Indices
Module provides index support for '@>' and '?' operations.
create index hidx on testhstore using gist(h);
create index hidx on testhstore using gin(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
...................
|
