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