Debian Codegraph @

doc/src/sgml/hstore.sgml

raw
1 <!-- doc/src/sgml/hstore.sgml -->
2
3 <sect1 id="hstore" xreflabel="hstore">
4 <title>hstore &mdash; hstore key/value datatype</title>
5
6 <indexterm zone="hstore">
7 <primary>hstore</primary>
8 </indexterm>
9
10 <para>
11 This module implements the <type>hstore</type> data type for storing sets of
12 key/value pairs within a single <productname>PostgreSQL</productname> value.
13 This can be useful in various scenarios, such as rows with many attributes
14 that are rarely examined, or semi-structured data. Keys and values are
15 simply text strings.
16 </para>
17
18 <para>
19 This module is considered <quote>trusted</quote>, that is, it can be
20 installed by non-superusers who have <literal>CREATE</literal> privilege
21 on the current database.
22 </para>
23
24 <sect2 id="hstore-external-rep">
25 <title><type>hstore</type> External Representation</title>
26
27 <para>
28
29 The text representation of an <type>hstore</type>, used for input and output,
30 includes zero or more <replaceable>key</replaceable> <literal>=&gt;</literal>
31 <replaceable>value</replaceable> pairs separated by commas. Some examples:
32
33 <synopsis>
34 k =&gt; v
35 foo =&gt; bar, baz =&gt; whatever
36 "1-a" =&gt; "anything at all"
37 </synopsis>
38
39 The order of the pairs is not significant (and may not be reproduced on
40 output). Whitespace between pairs or around the <literal>=&gt;</literal> sign is
41 ignored. Double-quote keys and values that include whitespace, commas,
42 <literal>=</literal>s or <literal>&gt;</literal>s. To include a double quote or a
43 backslash in a key or value, escape it with a backslash.
44 </para>
45
46 <para>
47 Each key in an <type>hstore</type> is unique. If you declare an <type>hstore</type>
48 with duplicate keys, only one will be stored in the <type>hstore</type> and
49 there is no guarantee as to which will be kept:
50
51 <programlisting>
52 SELECT 'a=&gt;1,a=&gt;2'::hstore;
53 hstore
54 ----------
55 "a"=&gt;"1"
56 </programlisting>
57 </para>
58
59 <para>
60 A value (but not a key) can be an SQL <literal>NULL</literal>. For example:
61
62 <programlisting>
63 key =&gt; NULL
64 </programlisting>
65
66 The <literal>NULL</literal> keyword is case-insensitive. Double-quote the
67 <literal>NULL</literal> to treat it as the ordinary string <quote>NULL</quote>.
68 </para>
69
70 <note>
71 <para>
72 Keep in mind that the <type>hstore</type> text format, when used for input,
73 applies <emphasis>before</emphasis> any required quoting or escaping. If you are
74 passing an <type>hstore</type> literal via a parameter, then no additional
75 processing is needed. But if you're passing it as a quoted literal
76 constant, then any single-quote characters and (depending on the setting of
77 the <varname>standard_conforming_strings</varname> configuration parameter)
78 backslash characters need to be escaped correctly. See
79 <xref linkend="sql-syntax-strings"/> for more on the handling of string
80 constants.
81 </para>
82 </note>
83
84 <para>
85 On output, double quotes always surround keys and values, even when it's
86 not strictly necessary.
87 </para>
88
89 </sect2>
90
91 <sect2 id="hstore-ops-funcs">
92 <title><type>hstore</type> Operators and Functions</title>
93
94 <para>
95 The operators provided by the <literal>hstore</literal> module are
96 shown in <xref linkend="hstore-op-table"/>, the functions
97 in <xref linkend="hstore-func-table"/>.
98 </para>
99
100 <table id="hstore-op-table">
101 <title><type>hstore</type> Operators</title>
102 <tgroup cols="1">
103 <thead>
104 <row>
105 <entry role="func_table_entry"><para role="func_signature">
106 Operator
107 </para>
108 <para>
109 Description
110 </para>
111 <para>
112 Example(s)
113 </para></entry>
114 </row>
115 </thead>
116
117 <tbody>
118 <row>
119 <entry role="func_table_entry"><para role="func_signature">
120 <type>hstore</type> <literal>-&gt;</literal> <type>text</type>
121 <returnvalue>text</returnvalue>
122 </para>
123 <para>
124 Returns value associated with given key, or <literal>NULL</literal> if
125 not present.
126 </para>
127 <para>
128 <literal>'a=&gt;x, b=&gt;y'::hstore -&gt; 'a'</literal>
129 <returnvalue>x</returnvalue>
130 </para></entry>
131 </row>
132
133 <row>
134 <entry role="func_table_entry"><para role="func_signature">
135 <type>hstore</type> <literal>-&gt;</literal> <type>text[]</type>
136 <returnvalue>text[]</returnvalue>
137 </para>
138 <para>
139 Returns values associated with given keys, or <literal>NULL</literal>
140 if not present.
141 </para>
142 <para>
143 <literal>'a=&gt;x, b=&gt;y, c=&gt;z'::hstore -&gt; ARRAY['c','a']</literal>
144 <returnvalue>{"z","x"}</returnvalue>
145 </para></entry>
146 </row>
147
148 <row>
149 <entry role="func_table_entry"><para role="func_signature">
150 <type>hstore</type> <literal>||</literal> <type>hstore</type>
151 <returnvalue>hstore</returnvalue>
152 </para>
153 <para>
154 Concatenates two <type>hstore</type>s.
155 </para>
156 <para>
157 <literal>'a=&gt;b, c=&gt;d'::hstore || 'c=&gt;x, d=&gt;q'::hstore</literal>
158 <returnvalue>"a"=&gt;"b", "c"=&gt;"x", "d"=&gt;"q"</returnvalue>
159 </para></entry>
160 </row>
161
162 <row>
163 <entry role="func_table_entry"><para role="func_signature">
164 <type>hstore</type> <literal>?</literal> <type>text</type>
165 <returnvalue>boolean</returnvalue>
166 </para>
167 <para>
168 Does <type>hstore</type> contain key?
169 </para>
170 <para>
171 <literal>'a=&gt;1'::hstore ? 'a'</literal>
172 <returnvalue>t</returnvalue>
173 </para></entry>
174 </row>
175
176 <row>
177 <entry role="func_table_entry"><para role="func_signature">
178 <type>hstore</type> <literal>?&amp;</literal> <type>text[]</type>
179 <returnvalue>boolean</returnvalue>
180 </para>
181 <para>
182 Does <type>hstore</type> contain all the specified keys?
183 </para>
184 <para>
185 <literal>'a=&gt;1,b=&gt;2'::hstore ?&amp; ARRAY['a','b']</literal>
186 <returnvalue>t</returnvalue>
187 </para></entry>
188 </row>
189
190 <row>
191 <entry role="func_table_entry"><para role="func_signature">
192 <type>hstore</type> <literal>?|</literal> <type>text[]</type>
193 <returnvalue>boolean</returnvalue>
194 </para>
195 <para>
196 Does <type>hstore</type> contain any of the specified keys?
197 </para>
198 <para>
199 <literal>'a=&gt;1,b=&gt;2'::hstore ?| ARRAY['b','c']</literal>
200 <returnvalue>t</returnvalue>
201 </para></entry>
202 </row>
203
204 <row>
205 <entry role="func_table_entry"><para role="func_signature">
206 <type>hstore</type> <literal>@&gt;</literal> <type>hstore</type>
207 <returnvalue>boolean</returnvalue>
208 </para>
209 <para>
210 Does left operand contain right?
211 </para>
212 <para>
213 <literal>'a=&gt;b, b=&gt;1, c=&gt;NULL'::hstore @&gt; 'b=&gt;1'</literal>
214 <returnvalue>t</returnvalue>
215 </para></entry>
216 </row>
217
218 <row>
219 <entry role="func_table_entry"><para role="func_signature">
220 <type>hstore</type> <literal>&lt;@</literal> <type>hstore</type>
221 <returnvalue>boolean</returnvalue>
222 </para>
223 <para>
224 Is left operand contained in right?
225 </para>
226 <para>
227 <literal>'a=&gt;c'::hstore &lt;@ 'a=&gt;b, b=&gt;1, c=&gt;NULL'</literal>
228 <returnvalue>f</returnvalue>
229 </para></entry>
230 </row>
231
232 <row>
233 <entry role="func_table_entry"><para role="func_signature">
234 <type>hstore</type> <literal>-</literal> <type>text</type>
235 <returnvalue>hstore</returnvalue>
236 </para>
237 <para>
238 Deletes key from left operand.
239 </para>
240 <para>
241 <literal>'a=&gt;1, b=&gt;2, c=&gt;3'::hstore - 'b'::text</literal>
242 <returnvalue>"a"=&gt;"1", "c"=&gt;"3"</returnvalue>
243 </para></entry>
244 </row>
245
246 <row>
247 <entry role="func_table_entry"><para role="func_signature">
248 <type>hstore</type> <literal>-</literal> <type>text[]</type>
249 <returnvalue>hstore</returnvalue>
250 </para>
251 <para>
252 Deletes keys from left operand.
253 </para>
254 <para>
255 <literal>'a=&gt;1, b=&gt;2, c=&gt;3'::hstore - ARRAY['a','b']</literal>
256 <returnvalue>"c"=&gt;"3"</returnvalue>
257 </para></entry>
258 </row>
259
260 <row>
261 <entry role="func_table_entry"><para role="func_signature">
262 <type>hstore</type> <literal>-</literal> <type>hstore</type>
263 <returnvalue>hstore</returnvalue>
264 </para>
265 <para>
266 Deletes pairs from left operand that match pairs in the right operand.
267 </para>
268 <para>
269 <literal>'a=&gt;1, b=&gt;2, c=&gt;3'::hstore - 'a=&gt;4, b=&gt;2'::hstore</literal>
270 <returnvalue>"a"=&gt;"1", "c"=&gt;"3"</returnvalue>
271 </para></entry>
272 </row>
273
274 <row>
275 <entry role="func_table_entry"><para role="func_signature">
276 <type>anyelement</type> <literal>#=</literal> <type>hstore</type>
277 <returnvalue>anyelement</returnvalue>
278 </para>
279 <para>
280 Replaces fields in the left operand (which must be a composite type)
281 with matching values from <type>hstore</type>.
282 </para>
283 <para>
284 <literal>ROW(1,3) #= 'f1=>11'::hstore</literal>
285 <returnvalue>(11,3)</returnvalue>
286 </para></entry>
287 </row>
288
289 <row>
290 <entry role="func_table_entry"><para role="func_signature">
291 <literal>%%</literal> <type>hstore</type>
292 <returnvalue>text[]</returnvalue>
293 </para>
294 <para>
295 Converts <type>hstore</type> to an array of alternating keys and
296 values.
297 </para>
298 <para>
299 <literal>%% 'a=&gt;foo, b=&gt;bar'::hstore</literal>
300 <returnvalue>{a,foo,b,bar}</returnvalue>
301 </para></entry>
302 </row>
303
304 <row>
305 <entry role="func_table_entry"><para role="func_signature">
306 <literal>%#</literal> <type>hstore</type>
307 <returnvalue>text[]</returnvalue>
308 </para>
309 <para>
310 Converts <type>hstore</type> to a two-dimensional key/value array.
311 </para>
312 <para>
313 <literal>%# 'a=&gt;foo, b=&gt;bar'::hstore</literal>
314 <returnvalue>{{a,foo},{b,bar}}</returnvalue>
315 </para></entry>
316 </row>
317 </tbody>
318 </tgroup>
319 </table>
320
321 <table id="hstore-func-table">
322 <title><type>hstore</type> Functions</title>
323 <tgroup cols="1">
324 <thead>
325 <row>
326 <entry role="func_table_entry"><para role="func_signature">
327 Function
328 </para>
329 <para>
330 Description
331 </para>
332 <para>
333 Example(s)
334 </para></entry>
335 </row>
336 </thead>
337
338 <tbody>
339 <row>
340 <entry role="func_table_entry"><para role="func_signature">
341 <indexterm><primary>hstore</primary></indexterm>
342 <function>hstore</function> ( <type>record</type> )
343 <returnvalue>hstore</returnvalue>
344 </para>
345 <para>
346 Constructs an <type>hstore</type> from a record or row.
347 </para>
348 <para>
349 <literal>hstore(ROW(1,2))</literal>
350 <returnvalue>"f1"=&gt;"1", "f2"=&gt;"2"</returnvalue>
351 </para></entry>
352 </row>
353
354 <row>
355 <entry role="func_table_entry"><para role="func_signature">
356 <function>hstore</function> ( <type>text[]</type> )
357 <returnvalue>hstore</returnvalue>
358 </para>
359 <para>
360 Constructs an <type>hstore</type> from an array, which may be either
361 a key/value array, or a two-dimensional array.
362 </para>
363 <para>
364 <literal>hstore(ARRAY['a','1','b','2'])</literal>
365 <returnvalue>"a"=&gt;"1", "b"=&gt;"2"</returnvalue>
366 </para>
367 <para>
368 <literal>hstore(ARRAY[['c','3'],['d','4']])</literal>
369 <returnvalue>"c"=&gt;"3", "d"=&gt;"4"</returnvalue>
370 </para></entry>
371 </row>
372
373 <row>
374 <entry role="func_table_entry"><para role="func_signature">
375 <function>hstore</function> ( <type>text[]</type>, <type>text[]</type> )
376 <returnvalue>hstore</returnvalue>
377 </para>
378 <para>
379 Constructs an <type>hstore</type> from separate key and value arrays.
380 </para>
381 <para>
382 <literal>hstore(ARRAY['a','b'], ARRAY['1','2'])</literal>
383 <returnvalue>"a"=&gt;"1", "b"=&gt;"2"</returnvalue>
384 </para></entry>
385 </row>
386
387 <row>
388 <entry role="func_table_entry"><para role="func_signature">
389 <function>hstore</function> ( <type>text</type>, <type>text</type> )
390 <returnvalue>hstore</returnvalue>
391 </para>
392 <para>
393 Makes a single-item <type>hstore</type>.
394 </para>
395 <para>
396 <literal>hstore('a', 'b')</literal>
397 <returnvalue>"a"=&gt;"b"</returnvalue>
398 </para></entry>
399 </row>
400
401 <row>
402 <entry role="func_table_entry"><para role="func_signature">
403 <indexterm><primary>akeys</primary></indexterm>
404 <function>akeys</function> ( <type>hstore</type> )
405 <returnvalue>text[]</returnvalue>
406 </para>
407 <para>
408 Extracts an <type>hstore</type>'s keys as an array.
409 </para>
410 <para>
411 <literal>akeys('a=&gt;1,b=&gt;2')</literal>
412 <returnvalue>{a,b}</returnvalue>
413 </para></entry>
414 </row>
415
416 <row>
417 <entry role="func_table_entry"><para role="func_signature">
418 <indexterm><primary>skeys</primary></indexterm>
419 <function>skeys</function> ( <type>hstore</type> )
420 <returnvalue>setof text</returnvalue>
421 </para>
422 <para>
423 Extracts an <type>hstore</type>'s keys as a set.
424 </para>
425 <para>
426 <literal>skeys('a=&gt;1,b=&gt;2')</literal>
427 <returnvalue></returnvalue>
428 <programlisting>
429 a
430 b
431 </programlisting>
432 </para></entry>
433 </row>
434
435 <row>
436 <entry role="func_table_entry"><para role="func_signature">
437 <indexterm><primary>avals</primary></indexterm>
438 <function>avals</function> ( <type>hstore</type> )
439 <returnvalue>text[]</returnvalue>
440 </para>
441 <para>
442 Extracts an <type>hstore</type>'s values as an array.
443 </para>
444 <para>
445 <literal>avals('a=&gt;1,b=&gt;2')</literal>
446 <returnvalue>{1,2}</returnvalue>
447 </para></entry>
448 </row>
449
450 <row>
451 <entry role="func_table_entry"><para role="func_signature">
452 <indexterm><primary>svals</primary></indexterm>
453 <function>svals</function> ( <type>hstore</type> )
454 <returnvalue>setof text</returnvalue>
455 </para>
456 <para>
457 Extracts an <type>hstore</type>'s values as a set.
458 </para>
459 <para>
460 <literal>svals('a=&gt;1,b=&gt;2')</literal>
461 <returnvalue></returnvalue>
462 <programlisting>
463 1
464 2
465 </programlisting>
466 </para></entry>
467 </row>
468
469 <row>
470 <entry role="func_table_entry"><para role="func_signature">
471 <indexterm><primary>hstore_to_array</primary></indexterm>
472 <function>hstore_to_array</function> ( <type>hstore</type> )
473 <returnvalue>text[]</returnvalue>
474 </para>
475 <para>
476 Extracts an <type>hstore</type>'s keys and values as an array of
477 alternating keys and values.
478 </para>
479 <para>
480 <literal>hstore_to_array('a=&gt;1,b=&gt;2')</literal>
481 <returnvalue>{a,1,b,2}</returnvalue>
482 </para></entry>
483 </row>
484
485 <row>
486 <entry role="func_table_entry"><para role="func_signature">
487 <indexterm><primary>hstore_to_matrix</primary></indexterm>
488 <function>hstore_to_matrix</function> ( <type>hstore</type> )
489 <returnvalue>text[]</returnvalue>
490 </para>
491 <para>
492 Extracts an <type>hstore</type>'s keys and values as a two-dimensional
493 array.
494 </para>
495 <para>
496 <literal>hstore_to_matrix('a=&gt;1,b=&gt;2')</literal>
497 <returnvalue>{{a,1},{b,2}}</returnvalue>
498 </para></entry>
499 </row>
500
501 <row>
502 <entry role="func_table_entry"><para role="func_signature">
503 <indexterm><primary>hstore_to_json</primary></indexterm>
504 <function>hstore_to_json</function> ( <type>hstore</type> )
505 <returnvalue>json</returnvalue>
506 </para>
507 <para>
508 Converts an <type>hstore</type> to a <type>json</type> value,
509 converting all non-null values to JSON strings.
510 </para>
511 <para>
512 This function is used implicitly when an <type>hstore</type> value is
513 cast to <type>json</type>.
514 </para>
515 <para>
516 <literal>hstore_to_json('"a key"=&gt;1, b=&gt;t, c=&gt;null, d=&gt;12345, e=&gt;012345, f=&gt;1.234, g=&gt;2.345e+4')</literal>
517 <returnvalue>{"a key": "1", "b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4"}</returnvalue>
518 </para></entry>
519 </row>
520
521 <row>
522 <entry role="func_table_entry"><para role="func_signature">
523 <indexterm><primary>hstore_to_jsonb</primary></indexterm>
524 <function>hstore_to_jsonb</function> ( <type>hstore</type> )
525 <returnvalue>jsonb</returnvalue>
526 </para>
527 <para>
528 Converts an <type>hstore</type> to a <type>jsonb</type> value,
529 converting all non-null values to JSON strings.
530 </para>
531 <para>
532 This function is used implicitly when an <type>hstore</type> value is
533 cast to <type>jsonb</type>.
534 </para>
535 <para>
536 <literal>hstore_to_jsonb('"a key"=&gt;1, b=&gt;t, c=&gt;null, d=&gt;12345, e=&gt;012345, f=&gt;1.234, g=&gt;2.345e+4')</literal>
537 <returnvalue>{"a key": "1", "b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4"}</returnvalue>
538 </para></entry>
539 </row>
540
541 <row>
542 <entry role="func_table_entry"><para role="func_signature">
543 <indexterm><primary>hstore_to_json_loose</primary></indexterm>
544 <function>hstore_to_json_loose</function> ( <type>hstore</type> )
545 <returnvalue>json</returnvalue>
546 </para>
547 <para>
548 Converts an <type>hstore</type> to a <type>json</type> value, but
549 attempts to distinguish numerical and Boolean values so they are
550 unquoted in the JSON.
551 </para>
552 <para>
553 <literal>hstore_to_json_loose('"a key"=&gt;1, b=&gt;t, c=&gt;null, d=&gt;12345, e=&gt;012345, f=&gt;1.234, g=&gt;2.345e+4')</literal>
554 <returnvalue>{"a key": 1, "b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4}</returnvalue>
555 </para></entry>
556 </row>
557
558 <row>
559 <entry role="func_table_entry"><para role="func_signature">
560 <indexterm><primary>hstore_to_jsonb_loose</primary></indexterm>
561 <function>hstore_to_jsonb_loose</function> ( <type>hstore</type> )
562 <returnvalue>jsonb</returnvalue>
563 </para>
564 <para>
565 Converts an <type>hstore</type> to a <type>jsonb</type> value, but
566 attempts to distinguish numerical and Boolean values so they are
567 unquoted in the JSON.
568 </para>
569 <para>
570 <literal>hstore_to_jsonb_loose('"a key"=&gt;1, b=&gt;t, c=&gt;null, d=&gt;12345, e=&gt;012345, f=&gt;1.234, g=&gt;2.345e+4')</literal>
571 <returnvalue>{"a key": 1, "b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4}</returnvalue>
572 </para></entry>
573 </row>
574
575 <row>
576 <entry role="func_table_entry"><para role="func_signature">
577 <indexterm><primary>slice</primary></indexterm>
578 <function>slice</function> ( <type>hstore</type>, <type>text[]</type> )
579 <returnvalue>hstore</returnvalue>
580 </para>
581 <para>
582 Extracts a subset of an <type>hstore</type> containing only the
583 specified keys.
584 </para>
585 <para>
586 <literal>slice('a=&gt;1,b=&gt;2,c=&gt;3'::hstore, ARRAY['b','c','x'])</literal>
587 <returnvalue>"b"=&gt;"2", "c"=&gt;"3"</returnvalue>
588 </para></entry>
589 </row>
590
591 <row>
592 <entry role="func_table_entry"><para role="func_signature">
593 <indexterm><primary>each</primary></indexterm>
594 <function>each</function> ( <type>hstore</type> )
595 <returnvalue>setof record</returnvalue>
596 ( <parameter>key</parameter> <type>text</type>,
597 <parameter>value</parameter> <type>text</type> )
598 </para>
599 <para>
600 Extracts an <type>hstore</type>'s keys and values as a set of records.
601 </para>
602 <para>
603 <literal>select * from each('a=&gt;1,b=&gt;2')</literal>
604 <returnvalue></returnvalue>
605 <programlisting>
606 key | value
607 -----+-------
608 a | 1
609 b | 2
610 </programlisting>
611 </para></entry>
612 </row>
613
614 <row>
615 <entry role="func_table_entry"><para role="func_signature">
616 <indexterm><primary>exist</primary></indexterm>
617 <function>exist</function> ( <type>hstore</type>, <type>text</type> )
618 <returnvalue>boolean</returnvalue>
619 </para>
620 <para>
621 Does <type>hstore</type> contain key?
622 </para>
623 <para>
624 <literal>exist('a=&gt;1', 'a')</literal>
625 <returnvalue>t</returnvalue>
626 </para></entry>
627 </row>
628
629 <row>
630 <entry role="func_table_entry"><para role="func_signature">
631 <indexterm><primary>defined</primary></indexterm>
632 <function>defined</function> ( <type>hstore</type>, <type>text</type> )
633 <returnvalue>boolean</returnvalue>
634 </para>
635 <para>
636 Does <type>hstore</type> contain a non-<literal>NULL</literal> value
637 for key?
638 </para>
639 <para>
640 <literal>defined('a=&gt;NULL', 'a')</literal>
641 <returnvalue>f</returnvalue>
642 </para></entry>
643 </row>
644
645 <row>
646 <entry role="func_table_entry"><para role="func_signature">
647 <indexterm><primary>delete</primary></indexterm>
648 <function>delete</function> ( <type>hstore</type>, <type>text</type> )
649 <returnvalue>hstore</returnvalue>
650 </para>
651 <para>
652 Deletes pair with matching key.
653 </para>
654 <para>
655 <literal>delete('a=&gt;1,b=&gt;2', 'b')</literal>
656 <returnvalue>"a"=&gt;"1"</returnvalue>
657 </para></entry>
658 </row>
659
660 <row>
661 <entry role="func_table_entry"><para role="func_signature">
662 <function>delete</function> ( <type>hstore</type>, <type>text[]</type> )
663 <returnvalue>hstore</returnvalue>
664 </para>
665 <para>
666 Deletes pairs with matching keys.
667 </para>
668 <para>
669 <literal>delete('a=&gt;1,b=&gt;2,c=&gt;3', ARRAY['a','b'])</literal>
670 <returnvalue>"c"=&gt;"3"</returnvalue>
671 </para></entry>
672 </row>
673
674 <row>
675 <entry role="func_table_entry"><para role="func_signature">
676 <function>delete</function> ( <type>hstore</type>, <type>hstore</type> )
677 <returnvalue>hstore</returnvalue>
678 </para>
679 <para>
680 Deletes pairs matching those in the second argument.
681 </para>
682 <para>
683 <literal>delete('a=&gt;1,b=&gt;2', 'a=&gt;4,b=&gt;2'::hstore)</literal>
684 <returnvalue>"a"=&gt;"1"</returnvalue>
685 </para></entry>
686 </row>
687
688 <row>
689 <entry role="func_table_entry"><para role="func_signature">
690 <indexterm><primary>populate_record</primary></indexterm>
691 <function>populate_record</function> ( <type>anyelement</type>, <type>hstore</type> )
692 <returnvalue>anyelement</returnvalue>
693 </para>
694 <para>
695 Replaces fields in the left operand (which must be a composite type)
696 with matching values from <type>hstore</type>.
697 </para>
698 <para>
699 <literal>populate_record(ROW(1,2), 'f1=>42'::hstore)</literal>
700 <returnvalue>(42,2)</returnvalue>
701 </para></entry>
702 </row>
703 </tbody>
704 </tgroup>
705 </table>
706
707 <para>
708 In addition to these operators and functions, values of
709 the <type>hstore</type> type can be subscripted, allowing them to act
710 like associative arrays. Only a single subscript of type <type>text</type>
711 can be specified; it is interpreted as a key and the corresponding
712 value is fetched or stored. For example,
713
714 <programlisting>
715 CREATE TABLE mytable (h hstore);
716 INSERT INTO mytable VALUES ('a=>b, c=>d');
717 SELECT h['a'] FROM mytable;
718 h
719 ---
720 b
721 (1 row)
722
723 UPDATE mytable SET h['c'] = 'new';
724 SELECT h FROM mytable;
725 h
726 ----------------------
727 "a"=>"b", "c"=>"new"
728 (1 row)
729 </programlisting>
730
731 A subscripted fetch returns <literal>NULL</literal> if the subscript
732 is <literal>NULL</literal> or that key does not exist in
733 the <type>hstore</type>. (Thus, a subscripted fetch is not greatly
734 different from the <literal>-&gt;</literal> operator.)
735 A subscripted update fails if the subscript is <literal>NULL</literal>;
736 otherwise, it replaces the value for that key, adding an entry to
737 the <type>hstore</type> if the key does not already exist.
738 </para>
739 </sect2>
740
741 <sect2 id="hstore-indexes">
742 <title>Indexes</title>
743
744 <para>
745 <type>hstore</type> has GiST and GIN index support for the <literal>@&gt;</literal>,
746 <literal>?</literal>, <literal>?&amp;</literal> and <literal>?|</literal> operators. For example:
747 </para>
748 <programlisting>
749 CREATE INDEX hidx ON testhstore USING GIST (h);
750
751 CREATE INDEX hidx ON testhstore USING GIN (h);
752 </programlisting>
753
754 <para>
755 <literal>gist_hstore_ops</literal> GiST opclass approximates a set of
756 key/value pairs as a bitmap signature. Its optional integer parameter
757 <literal>siglen</literal> determines the
758 signature length in bytes. The default length is 16 bytes.
759 Valid values of signature length are between 1 and 2024 bytes. Longer
760 signatures lead to a more precise search (scanning a smaller fraction of the index and
761 fewer heap pages), at the cost of a larger index.
762 </para>
763
764 <para>
765 Example of creating such an index with a signature length of 32 bytes:
766 <programlisting>
767 CREATE INDEX hidx ON testhstore USING GIST (h gist_hstore_ops(siglen=32));
768 </programlisting>
769 </para>
770
771 <para>
772 <type>hstore</type> also supports <type>btree</type> or <type>hash</type> indexes for
773 the <literal>=</literal> operator. This allows <type>hstore</type> columns to be
774 declared <literal>UNIQUE</literal>, or to be used in <literal>GROUP BY</literal>,
775 <literal>ORDER BY</literal> or <literal>DISTINCT</literal> expressions. The sort ordering
776 for <type>hstore</type> values is not particularly useful, but these indexes
777 may be useful for equivalence lookups. Create indexes for <literal>=</literal>
778 comparisons as follows:
779 </para>
780 <programlisting>
781 CREATE INDEX hidx ON testhstore USING BTREE (h);
782
783 CREATE INDEX hidx ON testhstore USING HASH (h);
784 </programlisting>
785 </sect2>
786
787 <sect2 id="hstore-examples">
788 <title>Examples</title>
789
790 <para>
791 Add a key, or update an existing key with a new value:
792 <programlisting>
793 UPDATE tab SET h['c'] = '3';
794 </programlisting>
795 Another way to do the same thing is:
796 <programlisting>
797 UPDATE tab SET h = h || hstore('c', '3');
798 </programlisting>
799 If multiple keys are to be added or changed in one operation,
800 the concatenation approach is more efficient than subscripting:
801 <programlisting>
802 UPDATE tab SET h = h || hstore(array['q', 'w'], array['11', '12']);
803 </programlisting>
804 </para>
805
806 <para>
807 Delete a key:
808 <programlisting>
809 UPDATE tab SET h = delete(h, 'k1');
810 </programlisting>
811 </para>
812
813 <para>
814 Convert a <type>record</type> to an <type>hstore</type>:
815 <programlisting>
816 CREATE TABLE test (col1 integer, col2 text, col3 text);
817 INSERT INTO test VALUES (123, 'foo', 'bar');
818
819 SELECT hstore(t) FROM test AS t;
820 hstore
821 ---------------------------------------------
822 "col1"=&gt;"123", "col2"=&gt;"foo", "col3"=&gt;"bar"
823 (1 row)
824 </programlisting>
825 </para>
826
827 <para>
828 Convert an <type>hstore</type> to a predefined <type>record</type> type:
829 <programlisting>
830 CREATE TABLE test (col1 integer, col2 text, col3 text);
831
832 SELECT * FROM populate_record(null::test,
833 '"col1"=&gt;"456", "col2"=&gt;"zzz"');
834 col1 | col2 | col3
835 ------+------+------
836 456 | zzz |
837 (1 row)
838 </programlisting>
839 </para>
840
841 <para>
842 Modify an existing record using the values from an <type>hstore</type>:
843 <programlisting>
844 CREATE TABLE test (col1 integer, col2 text, col3 text);
845 INSERT INTO test VALUES (123, 'foo', 'bar');
846
847 SELECT (r).* FROM (SELECT t #= '"col3"=&gt;"baz"' AS r FROM test t) s;
848 col1 | col2 | col3
849 ------+------+------
850 123 | foo | baz
851 (1 row)
852 </programlisting>
853 </para>
854 </sect2>
855
856 <sect2 id="hstore-statistics">
857 <title>Statistics</title>
858
859 <para>
860 The <type>hstore</type> type, because of its intrinsic liberality, could
861 contain a lot of different keys. Checking for valid keys is the task of the
862 application. The following examples demonstrate several techniques for
863 checking keys and obtaining statistics.
864 </para>
865
866 <para>
867 Simple example:
868 <programlisting>
869 SELECT * FROM each('aaa=&gt;bq, b=&gt;NULL, ""=&gt;1');
870 </programlisting>
871 </para>
872
873 <para>
874 Using a table:
875 <programlisting>
876 CREATE TABLE stat AS SELECT (each(h)).key, (each(h)).value FROM testhstore;
877 </programlisting>
878 </para>
879
880 <para>
881 Online statistics:
882 <programlisting>
883 SELECT key, count(*) FROM
884 (SELECT (each(h)).key FROM testhstore) AS stat
885 GROUP BY key
886 ORDER BY count DESC, key;
887 key | count
888 -----------+-------
889 line | 883
890 query | 207
891 pos | 203
892 node | 202
893 space | 197
894 status | 195
895 public | 194
896 title | 190
897 org | 189
898 ...................
899 </programlisting>
900 </para>
901 </sect2>
902
903 <sect2 id="hstore-compatibility">
904 <title>Compatibility</title>
905
906 <para>
907 As of PostgreSQL 9.0, <type>hstore</type> uses a different internal
908 representation than previous versions. This presents no obstacle for
909 dump/restore upgrades since the text representation (used in the dump) is
910 unchanged.
911 </para>
912
913 <para>
914 In the event of a binary upgrade, upward compatibility is maintained by
915 having the new code recognize old-format data. This will entail a slight
916 performance penalty when processing data that has not yet been modified by
917 the new code. It is possible to force an upgrade of all values in a table
918 column by doing an <literal>UPDATE</literal> statement as follows:
919 <programlisting>
920 UPDATE tablename SET hstorecol = hstorecol || '';
921 </programlisting>
922 </para>
923
924 <para>
925 Another way to do it is:
926 <programlisting>
927 ALTER TABLE tablename ALTER hstorecol TYPE hstore USING hstorecol || '';
928 </programlisting>
929 The <command>ALTER TABLE</command> method requires an
930 <literal>ACCESS EXCLUSIVE</literal> lock on the table,
931 but does not result in bloating the table with old row versions.
932 </para>
933
934 </sect2>
935
936 <sect2 id="hstore-transforms">
937 <title>Transforms</title>
938
939 <para>
940 Additional extensions are available that implement transforms for
941 the <type>hstore</type> type for the languages PL/Perl and PL/Python. The
942 extensions for PL/Perl are called <literal>hstore_plperl</literal>
943 and <literal>hstore_plperlu</literal>, for trusted and untrusted PL/Perl.
944 If you install these transforms and specify them when creating a
945 function, <type>hstore</type> values are mapped to Perl hashes. The
946 extension for PL/Python is called <literal>hstore_plpython3u</literal>.
947 If you use it, <type>hstore</type> values are mapped to Python dictionaries.
948 </para>
949 </sect2>
950
951 <sect2 id="hstore-authors">
952 <title>Authors</title>
953
954 <para>
955 Oleg Bartunov <email>[email protected]</email>, Moscow, Moscow University, Russia
956 </para>
957
958 <para>
959 Teodor Sigaev <email>[email protected]</email>, Moscow, Delta-Soft Ltd., Russia
960 </para>
961
962 <para>
963 Additional enhancements by Andrew Gierth <email>[email protected]</email>,
964 United Kingdom
965 </para>
966 </sect2>
967
968 </sect1>