doc/src/sgml/hstore.sgml

   1 <!-- $PostgreSQL$ -->
   2
   3 <sect1 id="hstore">
   4  <title>hstore</title>
   5
   6  <indexterm zone="hstore">
   7   <primary>hstore</primary>
   8  </indexterm>
   9
  10  <para>
  11   This module implements a data type <type>hstore</> for storing sets of
  12   (key,value) pairs within a single <productname>PostgreSQL</> data field.
  13   This can be useful in various scenarios, such as rows with many attributes
  14   that are rarely examined, or semi-structured data.
  15  </para>
  16
  17  <sect2>
  18   <title><type>hstore</> External Representation</title>
  19
  20   <para>
  21    The text representation of an <type>hstore</> value includes zero
  22    or more <replaceable>key</> <literal>=&gt;</> <replaceable>value</>
  23    items, separated by commas.  For example:
  24
  25    <programlisting>
  26     k => v
  27     foo => bar, baz => whatever
  28     "1-a" => "anything at all"
  29    </programlisting>
  30
  31    The order of the items is not considered significant (and may not be
  32    reproduced on output).  Whitespace between items or around the
  33    <literal>=&gt;</> sign is ignored.  Use double quotes if a key or
  34    value includes whitespace, comma, <literal>=</> or <literal>&gt;</>.
  35    To include a double quote or a backslash in a key or value, precede
  36    it with another backslash.  (Keep in mind that depending on the
  37    setting of <varname>standard_conforming_strings</>, you may need to
  38    double backslashes in SQL literal strings.)
  39   </para>
  40
  41   <para>
  42    A value (but not a key) can be a SQL NULL.  This is represented as
  43
  44    <programlisting>
  45     key => NULL
  46    </programlisting>
  47
  48    The <literal>NULL</> keyword is not case-sensitive.  Again, use
  49    double quotes if you want the string <literal>null</> to be treated
  50    as an ordinary data value.
  51   </para>
  52
  53   <para>
  54    Currently, double quotes are always used to surround key and value
  55    strings on output, even when this is not strictly necessary.
  56   </para>
  57
  58  </sect2>
  59
  60  <sect2>
  61   <title><type>hstore</> Operators and Functions</title>
  62
  63   <table id="hstore-op-table">
  64    <title><type>hstore</> Operators</title>
  65
  66    <tgroup cols="4">
  67     <thead>
  68      <row>
  69       <entry>Operator</entry>
  70       <entry>Description</entry>
  71       <entry>Example</entry>
  72       <entry>Result</entry>
  73      </row>
  74     </thead>
  75
  76     <tbody>
  77      <row>
  78       <entry><type>hstore</> <literal>-&gt;</> <type>text</></entry>
  79       <entry>get value for key (null if not present)</entry>
  80       <entry><literal>'a=&gt;x, b=&gt;y'::hstore -&gt; 'a'</literal></entry>
  81       <entry><literal>x</literal></entry>
  82      </row>
  83
  84      <row>
  85       <entry><type>text</> <literal>=&gt;</> <type>text</></entry>
  86       <entry>make single-item <type>hstore</></entry>
  87       <entry><literal>'a' =&gt; 'b'</literal></entry>
  88       <entry><literal>"a"=&gt;"b"</literal></entry>
  89      </row>
  90
  91      <row>
  92       <entry><type>hstore</> <literal>||</> <type>hstore</></entry>
  93       <entry>concatenation</entry>
  94       <entry><literal>'a=&gt;b, c=&gt;d'::hstore || 'c=&gt;x, d=&gt;q'::hstore</literal></entry>
  95       <entry><literal>"a"=&gt;"b", "c"=&gt;"x", "d"=&gt;"q"</literal></entry>
  96      </row>
  97
  98      <row>
  99       <entry><type>hstore</> <literal>?</> <type>text</></entry>
 100       <entry>does <type>hstore</> contain key?</entry>
 101       <entry><literal>'a=&gt;1'::hstore ? 'a'</literal></entry>
 102       <entry><literal>t</literal></entry>
 103      </row>
 104
 105      <row>
 106       <entry><type>hstore</> <literal>@&gt;</> <type>hstore</></entry>
 107       <entry>does left operand contain right?</entry>
 108       <entry><literal>'a=&gt;b, b=&gt;1, c=&gt;NULL'::hstore @&gt; 'b=&gt;1'</literal></entry>
 109       <entry><literal>t</literal></entry>
 110      </row>
 111
 112      <row>
 113       <entry><type>hstore</> <literal>&lt;@</> <type>hstore</></entry>
 114       <entry>is left operand contained in right?</entry>
 115       <entry><literal>'a=&gt;c'::hstore &lt;@ 'a=&gt;b, b=&gt;1, c=&gt;NULL'</literal></entry>
 116       <entry><literal>f</literal></entry>
 117      </row>
 118
 119     </tbody>
 120    </tgroup>
 121   </table>
 122
 123   <para>
 124    (Before PostgreSQL 8.2, the containment operators @&gt; and &lt;@ were
 125    respectively called @ and ~.  These names are still available, but are
 126    deprecated and will eventually be retired.  Notice that the old names
 127    are reversed from the convention formerly followed by the core geometric
 128    datatypes!)
 129   </para>
 130
 131   <table id="hstore-func-table">
 132    <title><type>hstore</> Functions</title>
 133
 134    <tgroup cols="5">
 135     <thead>
 136      <row>
 137       <entry>Function</entry>
 138       <entry>Return Type</entry>
 139       <entry>Description</entry>
 140       <entry>Example</entry>
 141       <entry>Result</entry>
 142      </row>
 143     </thead>
 144
 145     <tbody>
 146      <row>
 147       <entry><function>akeys(hstore)</function></entry>
 148       <entry><type>text[]</type></entry>
 149       <entry>get <type>hstore</>'s keys as array</entry>
 150       <entry><literal>akeys('a=&gt;1,b=&gt;2')</literal></entry>
 151       <entry><literal>{a,b}</literal></entry>
 152      </row>
 153
 154      <row>
 155       <entry><function>skeys(hstore)</function></entry>
 156       <entry><type>setof text</type></entry>
 157       <entry>get <type>hstore</>'s keys as set</entry>
 158       <entry><literal>skeys('a=&gt;1,b=&gt;2')</literal></entry>
 159       <entry>
 160 <programlisting>
 161 a
 162 b
 163 </programlisting></entry>
 164      </row>
 165
 166      <row>
 167       <entry><function>avals(hstore)</function></entry>
 168       <entry><type>text[]</type></entry>
 169       <entry>get <type>hstore</>'s values as array</entry>
 170       <entry><literal>avals('a=&gt;1,b=&gt;2')</literal></entry>
 171       <entry><literal>{1,2}</literal></entry>
 172      </row>
 173
 174      <row>
 175       <entry><function>svals(hstore)</function></entry>
 176       <entry><type>setof text</type></entry>
 177       <entry>get <type>hstore</>'s values as set</entry>
 178       <entry><literal>svals('a=&gt;1,b=&gt;2')</literal></entry>
 179       <entry>
 180 <programlisting>
 181 1
 182 2
 183 </programlisting></entry>
 184      </row>
 185
 186      <row>
 187       <entry><function>each(hstore)</function></entry>
 188       <entry><type>setof (key text, value text)</type></entry>
 189       <entry>get <type>hstore</>'s keys and values as set</entry>
 190       <entry><literal>select * from each('a=&gt;1,b=&gt;2')</literal></entry>
 191       <entry>
 192 <programlisting>
 193  key | value
 194 -----+-------
 195  a   | 1
 196  b   | 2
 197 </programlisting></entry>
 198      </row>
 199
 200      <row>
 201       <entry><function>exist(hstore,text)</function></entry>
 202       <entry><type>boolean</type></entry>
 203       <entry>does <type>hstore</> contain key?</entry>
 204       <entry><literal>exist('a=&gt;1','a')</literal></entry>
 205       <entry><literal>t</literal></entry>
 206      </row>
 207
 208      <row>
 209       <entry><function>defined(hstore,text)</function></entry>
 210       <entry><type>boolean</type></entry>
 211       <entry>does <type>hstore</> contain non-null value for key?</entry>
 212       <entry><literal>defined('a=&gt;NULL','a')</literal></entry>
 213       <entry><literal>f</literal></entry>
 214      </row>
 215
 216      <row>
 217       <entry><function>delete(hstore,text)</function></entry>
 218       <entry><type>hstore</type></entry>
 219       <entry>delete any item matching key</entry>
 220       <entry><literal>delete('a=&gt;1,b=&gt;2','b')</literal></entry>
 221       <entry><literal>"a"=>"1"</literal></entry>
 222      </row>
 223
 224     </tbody>
 225    </tgroup>
 226   </table>
 227  </sect2>
 228
 229  <sect2>
 230   <title>Indexes</title>
 231
 232   <para>
 233    <type>hstore</> has index support for <literal>@&gt;</> and <literal>?</>
 234    operators.  You can use either GiST or GIN index types.  For example:
 235   </para>
 236   <programlisting>
 237 CREATE INDEX hidx ON testhstore USING GIST(h);
 238
 239 CREATE INDEX hidx ON testhstore USING GIN(h);
 240   </programlisting>
 241  </sect2>
 242
 243  <sect2>
 244   <title>Examples</title>
 245
 246   <para>
 247    Add a key, or update an existing key with a new value:
 248   </para>
 249   <programlisting>
 250 UPDATE tab SET h = h || ('c' => '3');
 251   </programlisting>
 252
 253   <para>
 254    Delete a key:
 255   </para>
 256   <programlisting>
 257 UPDATE tab SET h = delete(h, 'k1');
 258   </programlisting>
 259  </sect2>
 260
 261  <sect2>
 262   <title>Statistics</title>
 263
 264   <para>
 265    The <type>hstore</> type, because of its intrinsic liberality, could
 266    contain a lot of different keys. Checking for valid keys is the task of the
 267    application.  Examples below demonstrate several techniques for checking
 268    keys and obtaining statistics.
 269   </para>
 270
 271   <para>
 272    Simple example:
 273   </para>
 274   <programlisting>
 275 SELECT * FROM each('aaa=>bq, b=>NULL, ""=>1');
 276   </programlisting>
 277
 278   <para>
 279    Using a table:
 280   </para>
 281   <programlisting>
 282 SELECT (each(h)).key, (each(h)).value INTO stat FROM testhstore;
 283   </programlisting>
 284
 285   <para>
 286    Online statistics:
 287   </para>
 288   <programlisting>
 289 SELECT key, count(*) FROM
 290   (SELECT (each(h)).key FROM testhstore) AS stat
 291   GROUP BY key
 292   ORDER BY count DESC, key;
 293     key    | count
 294 -----------+-------
 295  line      |   883
 296  query     |   207
 297  pos       |   203
 298  node      |   202
 299  space     |   197
 300  status    |   195
 301  public    |   194
 302  title     |   190
 303  org       |   189
 304 ...................
 305   </programlisting>
 306  </sect2>
 307
 308  <sect2>
 309   <title>Authors</title>
 310
 311   <para>
 312    Oleg Bartunov <email>oleg@sai.msu.su</email>, Moscow, Moscow University, Russia
 313   </para>
 314
 315   <para>
 316    Teodor Sigaev <email>teodor@sigaev.ru</email>, Moscow, Delta-Soft Ltd., Russia
 317   </para>
 318  </sect2>
 319
 320 </sect1>