Source

pgdevel / doc / src / sgml / hstore.sgml

Magnus Hagander 9f2e211 
Tom Lane 53e99f5 
Bruce Momjian e567c9f 
Bruce Momjian c3c69ab 
Tom Lane 53e99f5 
Bruce Momjian c3c69ab 




Bruce Momjian 29fd97d 

Tom Lane 53e99f5 
Tom Lane 172eacb 
Bruce Momjian 29fd97d 
Tom Lane f3a72bd 

Bruce Momjian c3c69ab 
Tom Lane 53e99f5 


Bruce Momjian 29fd97d 



Tom Lane 53e99f5 
Peter Eisentraut 66424a2 




Tom Lane 53e99f5 
Bruce Momjian 29fd97d 











Peter Eisentraut 66424a2 

Bruce Momjian 29fd97d 


Peter Eisentraut 66424a2 
Tom Lane 53e99f5 


Bruce Momjian 29fd97d 
Tom Lane 53e99f5 
Peter Eisentraut 66424a2 


Tom Lane 53e99f5 
Bruce Momjian 29fd97d 
Peter Eisentraut 66424a2 
Tom Lane 53e99f5 

Tom Lane 172eacb 

Bruce Momjian 29fd97d 








Tom Lane 172eacb 


Tom Lane 53e99f5 
Bruce Momjian 29fd97d 

Tom Lane 53e99f5 

Bruce Momjian c3c69ab 


Tom Lane 53e99f5 

Peter Eisentraut a3b681f 





Tom Lane 53e99f5 















Bruce Momjian 29fd97d 
Tom Lane 53e99f5 




Tom Lane 172eacb 
Bruce Momjian 29fd97d 
Tom Lane 172eacb 




Tom Lane 53e99f5 
Bruce Momjian 29fd97d 
Tom Lane 53e99f5 











Tom Lane 172eacb 













Tom Lane 53e99f5 












Tom Lane 172eacb 















Bruce Momjian 29fd97d 
Tom Lane 172eacb 





Bruce Momjian 29fd97d 
Tom Lane 172eacb 





Bruce Momjian 29fd97d 
Tom Lane 172eacb 





Bruce Momjian 29fd97d 
Tom Lane 172eacb 



Tom Lane 53e99f5 



Bruce Momjian 29fd97d 
Tom Lane 53e99f5 
Bruce Momjian 29fd97d 



Peter Eisentraut 5194b9d 
Bruce Momjian 29fd97d 

Tom Lane 53e99f5 
















Tom Lane 172eacb 
















Robert Haas b17129b 







Robert Haas f974212 







Tom Lane 53e99f5 

Bruce Momjian 29fd97d 
Tom Lane 53e99f5 






Bruce Momjian 29fd97d 
Tom Lane 53e99f5 

Magnus Hagander 3dfe7e8 
Tom Lane 53e99f5 







Bruce Momjian 29fd97d 
Tom Lane 53e99f5 






Bruce Momjian 29fd97d 
Tom Lane 53e99f5 








Tom Lane 172eacb 
















Andrew Dunstan 9494f2c 















Robert Haas ce51747 







Tom Lane 53e99f5 
Bruce Momjian 29fd97d 

Tom Lane 53e99f5 



Bruce Momjian c3c69ab 


Tom Lane 53e99f5 













Bruce Momjian 29fd97d 
Tom Lane 53e99f5 






Bruce Momjian 29fd97d 
Tom Lane 53e99f5 



Tom Lane 172eacb 


Bruce Momjian 29fd97d 
Tom Lane 172eacb 






Bruce Momjian 29fd97d 
Tom Lane 172eacb 






Bruce Momjian 29fd97d 
Tom Lane 172eacb 



Tom Lane 53e99f5 


Tom Lane 172eacb 


Andrew Dunstan 9494f2c 






Tom Lane 172eacb 
Bruce Momjian 29fd97d 
Peter Eisentraut 5194b9d 
Tom Lane 172eacb 

Bruce Momjian c3c69ab 


Tom Lane 53e99f5 

Bruce Momjian c3c69ab 
Bruce Momjian 29fd97d 
Tom Lane 4ec700a 
Bruce Momjian c3c69ab 
Peter Eisentraut 66424a2 
Tom Lane 172eacb 
Tom Lane 53e99f5 
Tom Lane 172eacb 
Peter Eisentraut 66424a2 
Tom Lane 172eacb 

Bruce Momjian 29fd97d 






Tom Lane 172eacb 
Peter Eisentraut 66424a2 
Tom Lane 172eacb 


Peter Eisentraut 66424a2 
Bruce Momjian c3c69ab 





Tom Lane 53e99f5 
Peter Eisentraut 66424a2 
Robert Haas b60653b 
Peter Eisentraut 66424a2 

Tom Lane 53e99f5 
Bruce Momjian c3c69ab 

Peter Eisentraut 66424a2 
Tom Lane 53e99f5 
Peter Eisentraut 66424a2 

Tom Lane 172eacb 

Bruce Momjian 29fd97d 
Peter Eisentraut 66424a2 
Tom Lane 172eacb 





Bruce Momjian 29fd97d 
Tom Lane 172eacb 
Peter Eisentraut 66424a2 

Tom Lane 172eacb 

Bruce Momjian 29fd97d 
Peter Eisentraut 66424a2 
Tom Lane 172eacb 


Bruce Momjian 29fd97d 
Tom Lane 172eacb 



Peter Eisentraut 66424a2 

Tom Lane 172eacb 

Bruce Momjian 29fd97d 
Peter Eisentraut 66424a2 
Tom Lane 172eacb 


Bruce Momjian 29fd97d 
Tom Lane 172eacb 



Peter Eisentraut 66424a2 

Bruce Momjian c3c69ab 



Tom Lane 53e99f5 
Bruce Momjian c3c69ab 
Tom Lane 53e99f5 

Bruce Momjian 29fd97d 

Bruce Momjian c3c69ab 


Tom Lane 53e99f5 
Peter Eisentraut 66424a2 
Bruce Momjian 29fd97d 
Peter Eisentraut 66424a2 

Bruce Momjian c3c69ab 

Tom Lane 53e99f5 
Peter Eisentraut 66424a2 
Tom Lane 53e99f5 
Peter Eisentraut 66424a2 

Bruce Momjian c3c69ab 
Tom Lane 53e99f5 

Peter Eisentraut 66424a2 
Tom Lane 53e99f5 




Bruce Momjian c3c69ab 










Peter Eisentraut 66424a2 

Bruce Momjian c3c69ab 


Tom Lane 172eacb 


Tom Lane 50a90fa 
Tom Lane 172eacb 





Bruce Momjian 29fd97d 




Peter Eisentraut 66424a2 
Tom Lane 172eacb 
Peter Eisentraut 66424a2 

Tom Lane 172eacb 


Peter Eisentraut 66424a2 
Tom Lane 172eacb 
Peter Eisentraut 66424a2 
Tom Lane 172eacb 






Bruce Momjian c3c69ab 
Tom Lane 53e99f5 
Bruce Momjian c3c69ab 


Tom Lane 53e99f5 
Bruce Momjian c3c69ab 
Tom Lane 53e99f5 
Bruce Momjian c3c69ab 
Tom Lane 172eacb 

Bruce Momjian 29fd97d 

Tom Lane 172eacb 
Bruce Momjian c3c69ab 

Tom Lane 53e99f5 
  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
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
<!-- doc/src/sgml/hstore.sgml -->

<sect1 id="hstore" xreflabel="hstore">
 <title>hstore</title>

 <indexterm zone="hstore">
  <primary>hstore</primary>
 </indexterm>

 <para>
  This module implements the <type>hstore</> data type for storing sets of
  key/value pairs within a single <productname>PostgreSQL</> value.
  This can be useful in various scenarios, such as rows with many attributes
  that are rarely examined, or semi-structured data.  Keys and values are
  simply text strings.
 </para>

 <sect2>
  <title><type>hstore</> External Representation</title>

  <para>

   The text representation of an <type>hstore</>, used for input and output,
   includes zero or more <replaceable>key</> <literal>=&gt;</>
   <replaceable>value</> pairs separated by commas. Some examples:

<synopsis>
k =&gt; v
foo =&gt; bar, baz =&gt; whatever
"1-a" =&gt; "anything at all"
</synopsis>

   The order of the pairs is not significant (and may not be reproduced on
   output). Whitespace between pairs or around the <literal>=&gt;</> sign is
   ignored. Double-quote keys and values that include whitespace, commas,
   <literal>=</>s or <literal>&gt;</>s. To include a double quote or a
   backslash in a key or value, escape it with a backslash.
  </para>

  <para>
   Each key in an <type>hstore</> is unique. If you declare an <type>hstore</>
   with duplicate keys, only one will be stored in the <type>hstore</> and
   there is no guarantee as to which will be kept:

<programlisting>
SELECT 'a=&gt;1,a=&gt;2'::hstore;
  hstore
----------
 "a"=&gt;"1"
</programlisting>
  </para>

  <para>
   A value (but not a key) can be an SQL <literal>NULL</>. For example:

<programlisting>
key =&gt; NULL
</programlisting>

   The <literal>NULL</> keyword is case-insensitive. Double-quote the
   <literal>NULL</> to treat it as the ordinary string <quote>NULL</quote>.
  </para>

  <note>
  <para>
   Keep in mind that the <type>hstore</> text format, when used for input,
   applies <emphasis>before</> any required quoting or escaping. If you are
   passing an <type>hstore</> literal via a parameter, then no additional
   processing is needed. But if you're passing it as a quoted literal
   constant, then any single-quote characters and (depending on the setting of
   the <varname>standard_conforming_strings</> configuration parameter)
   backslash characters need to be escaped correctly. See
   <xref linkend="sql-syntax-strings"> for more on the handling of string
   constants.
  </para>
  </note>

  <para>
   On output, double quotes always surround keys and values, even when it's
   not strictly necessary.
  </para>

 </sect2>

 <sect2>
  <title><type>hstore</> Operators and Functions</title>

  <para>
   The operators provided by the <literal>hstore</literal> module are
   shown in <xref linkend="hstore-op-table">, the functions
   in <xref linkend="hstore-func-table">.
  </para>

  <table id="hstore-op-table">
   <title><type>hstore</> Operators</title>

   <tgroup cols="4">
    <thead>
     <row>
      <entry>Operator</entry>
      <entry>Description</entry>
      <entry>Example</entry>
      <entry>Result</entry>
     </row>
    </thead>

    <tbody>
     <row>
      <entry><type>hstore</> <literal>-&gt;</> <type>text</></entry>
      <entry>get value for key (<literal>NULL</> if not present)</entry>
      <entry><literal>'a=&gt;x, b=&gt;y'::hstore -&gt; 'a'</literal></entry>
      <entry><literal>x</literal></entry>
     </row>

     <row>
      <entry><type>hstore</> <literal>-&gt;</> <type>text[]</></entry>
      <entry>get values for keys (<literal>NULL</> if not present)</entry>
      <entry><literal>'a=&gt;x, b=&gt;y, c=&gt;z'::hstore -&gt; ARRAY['c','a']</literal></entry>
      <entry><literal>{"z","x"}</literal></entry>
     </row>

     <row>
      <entry><type>hstore</> <literal>||</> <type>hstore</></entry>
      <entry>concatenate <type>hstore</>s</entry>
      <entry><literal>'a=&gt;b, c=&gt;d'::hstore || 'c=&gt;x, d=&gt;q'::hstore</literal></entry>
      <entry><literal>"a"=&gt;"b", "c"=&gt;"x", "d"=&gt;"q"</literal></entry>
     </row>

     <row>
      <entry><type>hstore</> <literal>?</> <type>text</></entry>
      <entry>does <type>hstore</> contain key?</entry>
      <entry><literal>'a=&gt;1'::hstore ? 'a'</literal></entry>
      <entry><literal>t</literal></entry>
     </row>

     <row>
      <entry><type>hstore</> <literal>?&amp;</> <type>text[]</></entry>
      <entry>does <type>hstore</> contain all specified keys?</entry>
      <entry><literal>'a=&gt;1,b=&gt;2'::hstore ?&amp; ARRAY['a','b']</literal></entry>
      <entry><literal>t</literal></entry>
     </row>

     <row>
      <entry><type>hstore</> <literal>?|</> <type>text[]</></entry>
      <entry>does <type>hstore</> contain any of the specified keys?</entry>
      <entry><literal>'a=&gt;1,b=&gt;2'::hstore ?| ARRAY['b','c']</literal></entry>
      <entry><literal>t</literal></entry>
     </row>

     <row>
      <entry><type>hstore</> <literal>@&gt;</> <type>hstore</></entry>
      <entry>does left operand contain right?</entry>
      <entry><literal>'a=&gt;b, b=&gt;1, c=&gt;NULL'::hstore @&gt; 'b=&gt;1'</literal></entry>
      <entry><literal>t</literal></entry>
     </row>

     <row>
      <entry><type>hstore</> <literal>&lt;@</> <type>hstore</></entry>
      <entry>is left operand contained in right?</entry>
      <entry><literal>'a=&gt;c'::hstore &lt;@ 'a=&gt;b, b=&gt;1, c=&gt;NULL'</literal></entry>
      <entry><literal>f</literal></entry>
     </row>

     <row>
      <entry><type>hstore</> <literal>-</> <type>text</></entry>
      <entry>delete key from left operand</entry>
      <entry><literal>'a=&gt;1, b=&gt;2, c=&gt;3'::hstore - 'b'::text</literal></entry>
      <entry><literal>"a"=&gt;"1", "c"=&gt;"3"</literal></entry>
     </row>

     <row>
      <entry><type>hstore</> <literal>-</> <type>text[]</></entry>
      <entry>delete keys from left operand</entry>
      <entry><literal>'a=&gt;1, b=&gt;2, c=&gt;3'::hstore - ARRAY['a','b']</literal></entry>
      <entry><literal>"c"=&gt;"3"</literal></entry>
     </row>

     <row>
      <entry><type>hstore</> <literal>-</> <type>hstore</></entry>
      <entry>delete matching pairs from left operand</entry>
      <entry><literal>'a=&gt;1, b=&gt;2, c=&gt;3'::hstore - 'a=&gt;4, b=&gt;2'::hstore</literal></entry>
      <entry><literal>"a"=&gt;"1", "c"=&gt;"3"</literal></entry>
     </row>

     <row>
      <entry><type>record</> <literal>#=</> <type>hstore</></entry>
      <entry>replace fields in <type>record</> with matching values from <type>hstore</></entry>
      <entry>see Examples section</entry>
      <entry></entry>
     </row>

     <row>
      <entry><literal>%%</> <type>hstore</></entry>
      <entry>convert <type>hstore</> to array of alternating keys and values</entry>
      <entry><literal>%% 'a=&gt;foo, b=&gt;bar'::hstore</literal></entry>
      <entry><literal>{a,foo,b,bar}</literal></entry>
     </row>

     <row>
      <entry><literal>%#</> <type>hstore</></entry>
      <entry>convert <type>hstore</> to two-dimensional key/value array</entry>
      <entry><literal>%# 'a=&gt;foo, b=&gt;bar'::hstore</literal></entry>
      <entry><literal>{{a,foo},{b,bar}}</literal></entry>
     </row>

    </tbody>
   </tgroup>
  </table>

  <note>
  <para>
   Prior to PostgreSQL 8.2, the containment operators <literal>@&gt;</>
   and <literal>&lt;@</> were called <literal>@</> and <literal>~</>,
   respectively. These names are still available, but are deprecated and will
   eventually be removed. Notice that the old names are reversed from the
   convention formerly followed by the core geometric data types!
   </para>
  </note>

  <table id="hstore-func-table">
   <title><type>hstore</> Functions</title>

   <tgroup cols="5">
    <thead>
     <row>
      <entry>Function</entry>
      <entry>Return Type</entry>
      <entry>Description</entry>
      <entry>Example</entry>
      <entry>Result</entry>
     </row>
    </thead>

    <tbody>
     <row>
      <entry><function>hstore(record)</function></entry>
      <entry><type>hstore</type></entry>
      <entry>construct an <type>hstore</> from a record or row</entry>
      <entry><literal>hstore(ROW(1,2))</literal></entry>
      <entry><literal>f1=&gt;1,f2=&gt;2</literal></entry>
     </row>

     <row>
      <entry><function>hstore(text[])</function></entry>
      <entry><type>hstore</type></entry>
      <entry>construct an <type>hstore</> from an array, which may be either
       a key/value array, or a two-dimensional array</entry>
      <entry><literal>hstore(ARRAY['a','1','b','2']) || hstore(ARRAY[['c','3'],['d','4']])</literal></entry>
      <entry><literal>a=&gt;1, b=&gt;2, c=&gt;3, d=&gt;4</literal></entry>
     </row>

     <row>
      <entry><function>hstore(text[], text[])</function></entry>
      <entry><type>hstore</type></entry>
      <entry>construct an <type>hstore</> from separate key and value arrays</entry>
      <entry><literal>hstore(ARRAY['a','b'], ARRAY['1','2'])</literal></entry>
      <entry><literal>"a"=&gt;"1","b"=&gt;"2"</literal></entry>
     </row>

     <row>
      <entry><function>hstore(text, text)</function></entry>
      <entry><type>hstore</type></entry>
      <entry>make single-item <type>hstore</></entry>
      <entry><literal>hstore('a', 'b')</literal></entry>
      <entry><literal>"a"=&gt;"b"</literal></entry>
     </row>

     <row>
      <entry><function>akeys(hstore)</function></entry>
      <entry><type>text[]</type></entry>
      <entry>get <type>hstore</>'s keys as an array</entry>
      <entry><literal>akeys('a=&gt;1,b=&gt;2')</literal></entry>
      <entry><literal>{a,b}</literal></entry>
     </row>

     <row>
      <entry><function>skeys(hstore)</function></entry>
      <entry><type>setof text</type></entry>
      <entry>get <type>hstore</>'s keys as a set</entry>
      <entry><literal>skeys('a=&gt;1,b=&gt;2')</literal></entry>
      <entry>
<programlisting>
a
b
</programlisting></entry>
     </row>

     <row>
      <entry><function>avals(hstore)</function></entry>
      <entry><type>text[]</type></entry>
      <entry>get <type>hstore</>'s values as an array</entry>
      <entry><literal>avals('a=&gt;1,b=&gt;2')</literal></entry>
      <entry><literal>{1,2}</literal></entry>
     </row>

     <row>
      <entry><function>svals(hstore)</function></entry>
      <entry><type>setof text</type></entry>
      <entry>get <type>hstore</>'s values as a set</entry>
      <entry><literal>svals('a=&gt;1,b=&gt;2')</literal></entry>
      <entry>
<programlisting>
1
2
</programlisting></entry>
     </row>

     <row>
      <entry><function>hstore_to_array(hstore)</function></entry>
      <entry><type>text[]</type></entry>
      <entry>get <type>hstore</>'s keys and values as an array of alternating
       keys and values</entry>
      <entry><literal>hstore_to_array('a=&gt;1,b=&gt;2')</literal></entry>
      <entry><literal>{a,1,b,2}</literal></entry>
     </row>

     <row>
      <entry><function>hstore_to_matrix(hstore)</function></entry>
      <entry><type>text[]</type></entry>
      <entry>get <type>hstore</>'s keys and values as a two-dimensional array</entry>
      <entry><literal>hstore_to_matrix('a=&gt;1,b=&gt;2')</literal></entry>
      <entry><literal>{{a,1},{b,2}}</literal></entry>
     </row>

     <row>
      <entry><function>hstore_to_json(hstore)</function></entry>
      <entry><type>json</type></entry>
      <entry>get <type>hstore</type> as a json value</entry>
      <entry><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></entry>
      <entry><literal>{"a key": "1", "b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4"}</literal></entry>
     </row>

     <row>
      <entry><function>hstore_to_json_loose(hstore)</function></entry>
      <entry><type>json</type></entry>
      <entry>get <type>hstore</type> as a json value, but attempting to distinguish numerical and boolean values so they are unquoted in the json</entry>
      <entry><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></entry>
      <entry><literal>{"a key": 1, "b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4}</literal></entry>
     </row>

     <row>
      <entry><function>slice(hstore, text[])</function></entry>
      <entry><type>hstore</type></entry>
      <entry>extract a subset of an <type>hstore</></entry>
      <entry><literal>slice('a=&gt;1,b=&gt;2,c=&gt;3'::hstore, ARRAY['b','c','x'])</literal></entry>
      <entry><literal>"b"=&gt;"2", "c"=&gt;"3"</literal></entry>
     </row>

     <row>
      <entry><function>each(hstore)</function></entry>
      <entry><type>setof(key text, value text)</type></entry>
      <entry>get <type>hstore</>'s keys and values as a set</entry>
      <entry><literal>select * from each('a=&gt;1,b=&gt;2')</literal></entry>
      <entry>
<programlisting>
 key | value
-----+-------
 a   | 1
 b   | 2
</programlisting></entry>
     </row>

     <row>
      <entry><function>exist(hstore,text)</function></entry>
      <entry><type>boolean</type></entry>
      <entry>does <type>hstore</> contain key?</entry>
      <entry><literal>exist('a=&gt;1','a')</literal></entry>
      <entry><literal>t</literal></entry>
     </row>

     <row>
      <entry><function>defined(hstore,text)</function></entry>
      <entry><type>boolean</type></entry>
      <entry>does <type>hstore</> contain non-<literal>NULL</> value for key?</entry>
      <entry><literal>defined('a=&gt;NULL','a')</literal></entry>
      <entry><literal>f</literal></entry>
     </row>

     <row>
      <entry><function>delete(hstore,text)</function></entry>
      <entry><type>hstore</type></entry>
      <entry>delete pair with matching key</entry>
      <entry><literal>delete('a=&gt;1,b=&gt;2','b')</literal></entry>
      <entry><literal>"a"=>"1"</literal></entry>
     </row>

     <row>
      <entry><function>delete(hstore,text[])</function></entry>
      <entry><type>hstore</type></entry>
      <entry>delete pairs with matching keys</entry>
      <entry><literal>delete('a=&gt;1,b=&gt;2,c=&gt;3',ARRAY['a','b'])</literal></entry>
      <entry><literal>"c"=>"3"</literal></entry>
     </row>

     <row>
      <entry><function>delete(hstore,hstore)</function></entry>
      <entry><type>hstore</type></entry>
      <entry>delete pairs matching those in the second argument</entry>
      <entry><literal>delete('a=&gt;1,b=&gt;2','a=&gt;4,b=&gt;2'::hstore)</literal></entry>
      <entry><literal>"a"=>"1"</literal></entry>
     </row>

     <row>
      <entry><function>populate_record(record,hstore)</function></entry>
      <entry><type>record</type></entry>
      <entry>replace fields in <type>record</> with matching values from <type>hstore</></entry>
      <entry>see Examples section</entry>
      <entry></entry>
     </row>

    </tbody>
   </tgroup>
  </table>

  <note>
   <para>
     The function <function>hstore_to_json</function> is used when an <type>hstore</type>
     value is cast to <type>json</type>.
   </para>
  </note>

  <note>
   <para>
    The function <function>populate_record</function> is actually declared
    with <type>anyelement</>, not <type>record</>, as its first argument,
    but it will reject non-record types with a run-time error.
   </para>
  </note>
 </sect2>

 <sect2>
  <title>Indexes</title>

  <para>
   <type>hstore</> has GiST and GIN index support for the <literal>@&gt;</>,
   <literal>?</>, <literal>?&amp;</> and <literal>?|</> operators. For example:
  </para>
<programlisting>
CREATE INDEX hidx ON testhstore USING GIST (h);

CREATE INDEX hidx ON testhstore USING GIN (h);
</programlisting>

  <para>
   <type>hstore</> also supports <type>btree</> or <type>hash</> indexes for
   the <literal>=</> operator. This allows <type>hstore</> columns to be
   declared <literal>UNIQUE</>, or to be used in <literal>GROUP BY</>,
   <literal>ORDER BY</> or <literal>DISTINCT</> expressions. The sort ordering
   for <type>hstore</> values is not particularly useful, but these indexes
   may be useful for equivalence lookups. Create indexes for <literal>=</>
   comparisons as follows:
  </para>
<programlisting>
CREATE INDEX hidx ON testhstore USING BTREE (h);

CREATE INDEX hidx ON testhstore USING HASH (h);
</programlisting>
 </sect2>

 <sect2>
  <title>Examples</title>

  <para>
   Add a key, or update an existing key with a new value:
<programlisting>
UPDATE tab SET h = h || hstore('c', '3');
</programlisting>
  </para>

  <para>
   Delete a key:
<programlisting>
UPDATE tab SET h = delete(h, 'k1');
</programlisting>
  </para>

  <para>
   Convert a <type>record</> to an <type>hstore</>:
<programlisting>
CREATE TABLE test (col1 integer, col2 text, col3 text);
INSERT INTO test VALUES (123, 'foo', 'bar');

SELECT hstore(t) FROM test AS t;
                   hstore                    
---------------------------------------------
 "col1"=&gt;"123", "col2"=&gt;"foo", "col3"=&gt;"bar"
(1 row)
</programlisting>
  </para>

  <para>
   Convert an <type>hstore</> to a predefined <type>record</> type:
<programlisting>
CREATE TABLE test (col1 integer, col2 text, col3 text);

SELECT * FROM populate_record(null::test,
                              '"col1"=&gt;"456", "col2"=&gt;"zzz"');
 col1 | col2 | col3 
------+------+------
  456 | zzz  | 
(1 row)
</programlisting>
  </para>

  <para>
   Modify an existing record using the values from an <type>hstore</>:
<programlisting>
CREATE TABLE test (col1 integer, col2 text, col3 text);
INSERT INTO test VALUES (123, 'foo', 'bar');

SELECT (r).* FROM (SELECT t #= '"col3"=&gt;"baz"' AS r FROM test t) s;
 col1 | col2 | col3 
------+------+------
  123 | foo  | baz
(1 row)
</programlisting>
  </para>
 </sect2>

 <sect2>
  <title>Statistics</title>

  <para>
   The <type>hstore</> type, because of its intrinsic liberality, could
   contain a lot of different keys. Checking for valid keys is the task of the
   application. The following examples demonstrate several techniques for
   checking keys and obtaining statistics.
  </para>

  <para>
   Simple example:
<programlisting>
SELECT * FROM each('aaa=&gt;bq, b=&gt;NULL, ""=&gt;1');
</programlisting>
  </para>

  <para>
   Using a table:
<programlisting>
SELECT (each(h)).key, (each(h)).value INTO stat FROM testhstore;
</programlisting>
  </para>

  <para>
   Online statistics:
<programlisting>
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
...................
</programlisting>
  </para>
 </sect2>

 <sect2>
  <title>Compatibility</title>

  <para>
   As of PostgreSQL 9.0, <type>hstore</> uses a different internal
   representation than previous versions. This presents no obstacle for
   dump/restore upgrades since the text representation (used in the dump) is
   unchanged.
  </para>

  <para>
   In the event of a binary upgrade, upward compatibility is maintained by
   having the new code recognize old-format data. This will entail a slight
   performance penalty when processing data that has not yet been modified by
   the new code. It is possible to force an upgrade of all values in a table
   column by doing an <literal>UPDATE</> statement as follows:
<programlisting>
UPDATE tablename SET hstorecol = hstorecol || '';
</programlisting>
  </para>

  <para>
   Another way to do it is:
<programlisting>
ALTER TABLE tablename ALTER hstorecol TYPE hstore USING hstorecol || '';
</programlisting>
   The <command>ALTER TABLE</> method requires an exclusive lock on the table,
   but does not result in bloating the table with old row versions.
  </para>

 </sect2>

 <sect2>
  <title>Authors</title>

  <para>
   Oleg Bartunov <email>oleg@sai.msu.su</email>, Moscow, Moscow University, Russia
  </para>

  <para>
   Teodor Sigaev <email>teodor@sigaev.ru</email>, Moscow, Delta-Soft Ltd., Russia
  </para>

  <para>
   Additional enhancements by Andrew Gierth <email>andrew@tao11.riddles.org.uk</email>,
   United Kingdom
  </para>
 </sect2>

</sect1>