granicus.if.org Git - postgresql/blob - doc/src/sgml/hstore.sgml

   1 <!-- $PostgreSQL: pgsql/doc/src/sgml/hstore.sgml,v 1.5 2009/11/30 17:56:09 momjian Exp $ -->
   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 the <type>hstore</> data type for storing sets of
  12   key/value pairs within a single <productname>PostgreSQL</> 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  <sect2>
  19   <title><type>hstore</> External Representation</title>
  20
  21   <para>
  22
  23    The text representation of an <type>hstore</>, used for input and output,
  24    includes zero or more <replaceable>key</> <literal>=&gt;</>
  25    <replaceable>value</> pairs separated by commas. Some examples:
  26
  27    <programlisting>
  28     k =&gt; v
  29     foo =&gt; bar, baz =&gt; whatever
  30     "1-a" =&gt; "anything at all"
  31    </programlisting>
  32
  33    The order of the pairs is not significant (and may not be reproduced on
  34    output). Whitespace between pairs or around the <literal>=&gt;</> sign is
  35    ignored. Double-quote keys and values that include whitespace, commas,
  36    <literal>=</>s or <literal>&gt;</>s. To include a double quote or a
  37    backslash in a key or value, escape it with a backslash.
  38   </para>
  39
  40   <para>
  41    Each key in an <type>hstore</> is unique. If you declare an <type>hstore</>
  42    with duplicate keys, only one will be stored in the <type>hstore</> and
  43    there is no guarantee as to which will be kept:
  44
  45    <programlisting>
  46 % select 'a=&gt;1,a=&gt;2'::hstore;
  47   hstore
  48 ----------
  49  "a"=&gt;"1"
  50    </programlisting>
  51   </para>
  52
  53   <para>
  54    A value (but not a key) can be an SQL <literal>NULL</>. For example:
  55
  56    <programlisting>
  57     key =&gt; NULL
  58    </programlisting>
  59
  60    The <literal>NULL</> keyword is case-insensitive. Double-quote the
  61    <literal>NULL</> to treat it as the ordinary string "NULL".
  62   </para>
  63
  64   <note>
  65   <para>
  66    Keep in mind that the <type>hstore</> text format, when used for input,
  67    applies <emphasis>before</> any required quoting or escaping. If you are
  68    passing an <type>hstore</> literal via a parameter, then no additional
  69    processing is needed. But if you're passing it as a quoted literal
  70    constant, then any single-quote characters and (depending on the setting of
  71    the <varname>standard_conforming_strings</> configuration parameter)
  72    backslash characters need to be escaped correctly. See
  73    <xref linkend="sql-syntax-strings"> for more on the handling of string
  74    constants.
  75   </para>
  76   </note>
  77
  78   <para>
  79    On output, double quotes always surround keys and values, even when it's
  80    not strictly necessary.
  81   </para>
  82
  83  </sect2>
  84
  85  <sect2>
  86   <title><type>hstore</> Operators and Functions</title>
  87
  88   <table id="hstore-op-table">
  89    <title><type>hstore</> Operators</title>
  90
  91    <tgroup cols="4">
  92     <thead>
  93      <row>
  94       <entry>Operator</entry>
  95       <entry>Description</entry>
  96       <entry>Example</entry>
  97       <entry>Result</entry>
  98      </row>
  99     </thead>
 100
 101     <tbody>
 102      <row>
 103       <entry><type>hstore</> <literal>-&gt;</> <type>text</></entry>
 104       <entry>get value for key (<literal>NULL</> if not present)</entry>
 105       <entry><literal>'a=&gt;x, b=&gt;y'::hstore -&gt; 'a'</literal></entry>
 106       <entry><literal>x</literal></entry>
 107      </row>
 108
 109      <row>
 110       <entry><type>hstore</> <literal>-&gt;</> <type>text[]</></entry>
 111       <entry>get values for keys (<literal>NULL</> if not present)</entry>
 112       <entry><literal>'a=&gt;x, b=&gt;y, c=&gt;z'::hstore -&gt; ARRAY['c','a']</literal></entry>
 113       <entry><literal>{"z","x"}</literal></entry>
 114      </row>
 115
 116      <row>
 117       <entry><type>text</> <literal>=&gt;</> <type>text</></entry>
 118       <entry>make single-pair <type>hstore</></entry>
 119       <entry><literal>'a' =&gt; 'b'</literal></entry>
 120       <entry><literal>"a"=&gt;"b"</literal></entry>
 121      </row>
 122
 123      <row>
 124       <entry><type>text[]</> <literal>=&gt;</> <type>text[]</></entry>
 125       <entry>construct an <type>hstore</> from separate key and value arrays</entry>
 126       <entry><literal>ARRAY['a','b'] =&gt; ARRAY['1','2']</literal></entry>
 127       <entry><literal>"a"=&gt;"1","b"=&gt;"2"</literal></entry>
 128      </row>
 129
 130      <row>
 131       <entry><type>hstore</> <literal>=&gt;</> <type>text[]</></entry>
 132       <entry>extract a subset of an <type>hstore</></entry>
 133       <entry><literal>'a=&gt;1,b=&gt;2,c=&gt;3'::hstore =&gt; ARRAY['b','c','x']</literal></entry>
 134       <entry><literal>"b"=&gt;"2", "c"=&gt;"3"</literal></entry>
 135      </row>
 136
 137      <row>
 138       <entry><type>hstore</> <literal>||</> <type>hstore</></entry>
 139       <entry>concatenate <type>hstore</>s</entry>
 140       <entry><literal>'a=&gt;b, c=&gt;d'::hstore || 'c=&gt;x, d=&gt;q'::hstore</literal></entry>
 141       <entry><literal>"a"=&gt;"b", "c"=&gt;"x", "d"=&gt;"q"</literal></entry>
 142      </row>
 143
 144      <row>
 145       <entry><type>hstore</> <literal>?</> <type>text</></entry>
 146       <entry>does <type>hstore</> contain key?</entry>
 147       <entry><literal>'a=&gt;1'::hstore ? 'a'</literal></entry>
 148       <entry><literal>t</literal></entry>
 149      </row>
 150
 151      <row>
 152       <entry><type>hstore</> <literal>?&amp;</> <type>text[]</></entry>
 153       <entry>does <type>hstore</> contain all specified keys?</entry>
 154       <entry><literal>'a=&gt;1,b=&gt;2'::hstore ?&amp; ARRAY['a','b']</literal></entry>
 155       <entry><literal>t</literal></entry>
 156      </row>
 157
 158      <row>
 159       <entry><type>hstore</> <literal>?|</> <type>text[]</></entry>
 160       <entry>does <type>hstore</> contain any of the specified keys?</entry>
 161       <entry><literal>'a=&gt;1,b=&gt;2'::hstore ?| ARRAY['b','c']</literal></entry>
 162       <entry><literal>t</literal></entry>
 163      </row>
 164
 165      <row>
 166       <entry><type>hstore</> <literal>@&gt;</> <type>hstore</></entry>
 167       <entry>does left operand contain right?</entry>
 168       <entry><literal>'a=&gt;b, b=&gt;1, c=&gt;NULL'::hstore @&gt; 'b=&gt;1'</literal></entry>
 169       <entry><literal>t</literal></entry>
 170      </row>
 171
 172      <row>
 173       <entry><type>hstore</> <literal>&lt;@</> <type>hstore</></entry>
 174       <entry>is left operand contained in right?</entry>
 175       <entry><literal>'a=&gt;c'::hstore &lt;@ 'a=&gt;b, b=&gt;1, c=&gt;NULL'</literal></entry>
 176       <entry><literal>f</literal></entry>
 177      </row>
 178
 179      <row>
 180       <entry><type>hstore</> <literal>-</> <type>text</></entry>
 181       <entry>delete key from left operand</entry>
 182       <entry><literal>'a=&gt;1, b=&gt;2, c=&gt;3'::hstore - 'b'::text</literal></entry>
 183       <entry><literal>"a"=&gt;"1", "c"=&gt;"3"</literal></entry>
 184      </row>
 185
 186      <row>
 187       <entry><type>hstore</> <literal>-</> <type>text[]</></entry>
 188       <entry>delete keys from left operand</entry>
 189       <entry><literal>'a=&gt;1, b=&gt;2, c=&gt;3'::hstore - ARRAY['a','b']</literal></entry>
 190       <entry><literal>"c"=&gt;"3"</literal></entry>
 191      </row>
 192
 193      <row>
 194       <entry><type>hstore</> <literal>-</> <type>hstore</></entry>
 195       <entry>delete matching pairs from left operand</entry>
 196       <entry><literal>'a=&gt;1, b=&gt;2, c=&gt;3'::hstore - 'a=&gt;4, b=&gt;2'::hstore</literal></entry>
 197       <entry><literal>"a"=&gt;"1", "c"=&gt;"3"</literal></entry>
 198      </row>
 199
 200      <row>
 201       <entry><type>record</> <literal>#=</> <type>hstore</></entry>
 202       <entry>replace fields in <type>record</> with matching values from <type>hstore</></entry>
 203       <entry>see Examples section</entry>
 204       <entry></entry>
 205      </row>
 206
 207      <row>
 208       <entry><literal>%%</> <type>hstore</></entry>
 209       <entry>convert <type>hstore</> to array of alternating keys and values</entry>
 210       <entry><literal>%% 'a=&gt;foo, b=&gt;bar'::hstore</literal></entry>
 211       <entry><literal>{a,foo,b,bar}</literal></entry>
 212      </row>
 213
 214      <row>
 215       <entry><literal>%#</> <type>hstore</></entry>
 216       <entry>convert <type>hstore</> to two-dimensional key/value array</entry>
 217       <entry><literal>%# 'a=&gt;foo, b=&gt;bar'::hstore</literal></entry>
 218       <entry><literal>{{a,foo},{b,bar}}</literal></entry>
 219      </row>
 220
 221     </tbody>
 222    </tgroup>
 223   </table>
 224
 225   <note>
 226   <para>
 227    Prior to PostgreSQL 8.2, the containment operators <literal>@&gt;</>
 228    and <literal>&lt;@</> were called <literal>@</> and <literal>~</>,
 229    respectively. These names are still available, but are deprecated and will
 230    eventually be removed. Notice that the old names are reversed from the
 231    convention formerly followed by the core geometric datatypes!
 232    </para>
 233   </note>
 234
 235   <table id="hstore-func-table">
 236    <title><type>hstore</> Functions</title>
 237
 238    <tgroup cols="5">
 239     <thead>
 240      <row>
 241       <entry>Function</entry>
 242       <entry>Return Type</entry>
 243       <entry>Description</entry>
 244       <entry>Example</entry>
 245       <entry>Result</entry>
 246      </row>
 247     </thead>
 248
 249     <tbody>
 250      <row>
 251       <entry><function>hstore(record)</function></entry>
 252       <entry><type>hstore</type></entry>
 253       <entry>construct an <type>hstore</> from a record or row</entry>
 254       <entry><literal>hstore(ROW(1,2))</literal></entry>
 255       <entry><literal>f1=&gt;1,f2=&gt;2</literal></entry>
 256      </row>
 257
 258      <row>
 259       <entry><function>hstore(text[])</function></entry>
 260       <entry><type>hstore</type></entry>
 261       <entry>construct an <type>hstore</> from an array, which may be either
 262        a key/value array, or a two-dimensional array</entry>
 263       <entry><literal>hstore(ARRAY['a','1','b','2']) || hstore(ARRAY[['c','3'],['d','4']])</literal></entry>
 264       <entry><literal>a=&gt;1, b=&gt;2, c=&gt;3, d=&gt;4</literal></entry>
 265      </row>
 266
 267      <row>
 268       <entry><function>akeys(hstore)</function></entry>
 269       <entry><type>text[]</type></entry>
 270       <entry>get <type>hstore</>'s keys as an array</entry>
 271       <entry><literal>akeys('a=&gt;1,b=&gt;2')</literal></entry>
 272       <entry><literal>{a,b}</literal></entry>
 273      </row>
 274
 275      <row>
 276       <entry><function>skeys(hstore)</function></entry>
 277       <entry><type>setof text</type></entry>
 278       <entry>get <type>hstore</>'s keys as a set</entry>
 279       <entry><literal>skeys('a=&gt;1,b=&gt;2')</literal></entry>
 280       <entry>
 281 22<programlisting>
 282 a
 283 b
 284 </programlisting></entry>
 285      </row>
 286
 287      <row>
 288       <entry><function>avals(hstore)</function></entry>
 289       <entry><type>text[]</type></entry>
 290       <entry>get <type>hstore</>'s values as an array</entry>
 291       <entry><literal>avals('a=&gt;1,b=&gt;2')</literal></entry>
 292       <entry><literal>{1,2}</literal></entry>
 293      </row>
 294
 295      <row>
 296       <entry><function>svals(hstore)</function></entry>
 297       <entry><type>setof text</type></entry>
 298       <entry>get <type>hstore</>'s values as a set</entry>
 299       <entry><literal>svals('a=&gt;1,b=&gt;2')</literal></entry>
 300       <entry>
 301 <programlisting>
 302 1
 303 2
 304 </programlisting></entry>
 305      </row>
 306
 307      <row>
 308       <entry><function>hstore_to_array(hstore)</function></entry>
 309       <entry><type>text[]</type></entry>
 310       <entry>get <type>hstore</>'s keys and values as an array of alternating
 311        keys and values</entry>
 312       <entry><literal>hstore_to_array('a=&gt;1,b=&gt;2')</literal></entry>
 313       <entry><literal>{a,1,b,2}</literal></entry>
 314      </row>
 315
 316      <row>
 317       <entry><function>hstore_to_matrix(hstore)</function></entry>
 318       <entry><type>text[]</type></entry>
 319       <entry>get <type>hstore</>'s keys and values as a two-dimensional array</entry>
 320       <entry><literal>hstore_to_matrix('a=&gt;1,b=&gt;2')</literal></entry>
 321       <entry><literal>{{a,1},{b,2}}</literal></entry>
 322      </row>
 323
 324      <row>
 325       <entry><function>each(hstore)</function></entry>
 326       <entry><type>setof(key text, value text)</type></entry>
 327       <entry>get <type>hstore</>'s keys and values as a set</entry>
 328       <entry><literal>select * from each('a=&gt;1,b=&gt;2')</literal></entry>
 329       <entry>
 330 <programlisting>
 331  key | value
 332 -----+-------
 333  a   | 1
 334  b   | 2
 335 </programlisting></entry>
 336      </row>
 337
 338      <row>
 339       <entry><function>exist(hstore,text)</function></entry>
 340       <entry><type>boolean</type></entry>
 341       <entry>does <type>hstore</> contain key?</entry>
 342       <entry><literal>exist('a=&gt;1','a')</literal></entry>
 343       <entry><literal>t</literal></entry>
 344      </row>
 345
 346      <row>
 347       <entry><function>defined(hstore,text)</function></entry>
 348       <entry><type>boolean</type></entry>
 349       <entry>does <type>hstore</> contain non-<literal>NULL</> value for key?</entry>
 350       <entry><literal>defined('a=&gt;NULL','a')</literal></entry>
 351       <entry><literal>f</literal></entry>
 352      </row>
 353
 354      <row>
 355       <entry><function>delete(hstore,text)</function></entry>
 356       <entry><type>hstore</type></entry>
 357       <entry>delete pair with matching key</entry>
 358       <entry><literal>delete('a=&gt;1,b=&gt;2','b')</literal></entry>
 359       <entry><literal>"a"=>"1"</literal></entry>
 360      </row>
 361
 362      <row>
 363       <entry><function>delete(hstore,text[])</function></entry>
 364       <entry><type>hstore</type></entry>
 365       <entry>delete pairs with matching keys</entry>
 366       <entry><literal>delete('a=&gt;1,b=&gt;2,c=&gt;3',ARRAY['a','b'])</literal></entry>
 367       <entry><literal>"c"=>"3"</literal></entry>
 368      </row>
 369
 370      <row>
 371       <entry><function>delete(hstore,hstore)</function></entry>
 372       <entry><type>hstore</type></entry>
 373       <entry>delete pairs matching those in the second argument</entry>
 374       <entry><literal>delete('a=&gt;1,b=&gt;2','a=&gt;4,b=&gt;2'::hstore)</literal></entry>
 375       <entry><literal>"a"=>"1"</literal></entry>
 376      </row>
 377
 378      <row>
 379       <entry><function>populate_record(record,hstore)</function></entry>
 380       <entry><type>record</type></entry>
 381       <entry>replace fields in <type>record</> with matching values from <type>hstore</></entry>
 382       <entry>see Examples section</entry>
 383       <entry></entry>
 384      </row>
 385
 386     </tbody>
 387    </tgroup>
 388   </table>
 389
 390   <note>
 391    <para>
 392     The function <function>populate_record</function> is actually declared
 393     with <type>anyelement</>, not <type>record</>, as its first argument,
 394     but it will reject non-record types with a runtime error.
 395    </para>
 396   </note>
 397  </sect2>
 398
 399  <sect2>
 400   <title>Indexes</title>
 401
 402   <para>
 403    <type>hstore</> has GiST and GIN index support for the <literal>@&gt;</>,
 404    <literal>?</>, <literal>?&</> and <literal>?|</> operators. For example:
 405   </para>
 406   <programlisting>
 407 CREATE INDEX hidx ON testhstore USING GIST (h);
 408
 409 CREATE INDEX hidx ON testhstore USING GIN (h);
 410   </programlisting>
 411
 412   <para>
 413    <type>hstore</> also supports <type>btree</> or <type>hash</> indexes for
 414    the <literal>=</> operator. This allows <type>hstore</> columns to be
 415    declared <literal>UNIQUE</>, or to be used in <literal>GROUP BY</>,
 416    <literal>ORDER BY</> or <literal>DISTINCT</> expressions. The sort ordering
 417    for <type>hstore</> values is not particularly useful, but these indexes
 418    may be useful for equivalence lookups. Create indexes for <literal>=</>
 419    comparisons as follows:
 420   </para>
 421   <programlisting>
 422 CREATE INDEX hidx ON testhstore USING BTREE (h);
 423
 424 CREATE INDEX hidx ON testhstore USING HASH (h);
 425   </programlisting>
 426  </sect2>
 427
 428  <sect2>
 429   <title>Examples</title>
 430
 431   <para>
 432    Add a key, or update an existing key with a new value:
 433   </para>
 434   <programlisting>
 435 UPDATE tab SET h = h || ('c' =&gt; '3');
 436   </programlisting>
 437
 438   <para>
 439    Delete a key:
 440   </para>
 441   <programlisting>
 442 UPDATE tab SET h = delete(h, 'k1');
 443   </programlisting>
 444
 445   <para>
 446    Convert a <type>record</> to an <type>hstore</>:
 447   </para>
 448   <programlisting>
 449 CREATE TABLE test (col1 integer, col2 text, col3 text);
 450 INSERT INTO test VALUES (123, 'foo', 'bar');
 451
 452 SELECT hstore(t) FROM test AS t;
 453                    hstore
 454 ---------------------------------------------
 455  "col1"=&gt;"123", "col2"=&gt;"foo", "col3"=&gt;"bar"
 456 (1 row)
 457   </programlisting>
 458
 459   <para>
 460    Convert an <type>hstore</> to a predefined <type>record</> type:
 461   </para>
 462   <programlisting>
 463 CREATE TABLE test (col1 integer, col2 text, col3 text);
 464
 465 SELECT * FROM populate_record(null::test,
 466                               '"col1"=&gt;"456", "col2"=&gt;"zzz"');
 467  col1 | col2 | col3
 468 ------+------+------
 469   456 | zzz  |
 470 (1 row)
 471   </programlisting>
 472
 473   <para>
 474    Modify an existing record using the values from an <type>hstore</>:
 475   </para>
 476   <programlisting>
 477 CREATE TABLE test (col1 integer, col2 text, col3 text);
 478 INSERT INTO test VALUES (123, 'foo', 'bar');
 479
 480 SELECT (r).* FROM (SELECT t #= '"col3"=&gt;"baz"' AS r FROM test t) s;
 481  col1 | col2 | col3
 482 ------+------+------
 483   123 | foo  | baz
 484 (1 row)
 485   </programlisting>
 486  </sect2>
 487
 488  <sect2>
 489   <title>Statistics</title>
 490
 491   <para>
 492    The <type>hstore</> type, because of its intrinsic liberality, could
 493    contain a lot of different keys. Checking for valid keys is the task of the
 494    application. The following examples demonstrate several techniques for
 495    checking keys and obtaining statistics.
 496   </para>
 497
 498   <para>
 499    Simple example:
 500   </para>
 501   <programlisting>
 502 SELECT * FROM each('aaa=&gt;bq, b=&gt;NULL, ""=&gt;1');
 503   </programlisting>
 504
 505   <para>
 506    Using a table:
 507   </para>
 508   <programlisting>
 509 SELECT (each(h)).key, (each(h)).value INTO stat FROM testhstore;
 510   </programlisting>
 511
 512   <para>
 513    Online statistics:
 514   </para>
 515   <programlisting>
 516 SELECT key, count(*) FROM
 517   (SELECT (each(h)).key FROM testhstore) AS stat
 518   GROUP BY key
 519   ORDER BY count DESC, key;
 520     key    | count
 521 -----------+-------
 522  line      |   883
 523  query     |   207
 524  pos       |   203
 525  node      |   202
 526  space     |   197
 527  status    |   195
 528  public    |   194
 529  title     |   190
 530  org       |   189
 531 ...................
 532   </programlisting>
 533  </sect2>
 534
 535  <sect2>
 536   <title>Compatibility</title>
 537
 538   <para>
 539    <emphasis>When upgrading from older versions, always load the new
 540    version of this module into the database before restoring a dump.
 541    Otherwise, many new features will be unavailable.</emphasis>
 542   </para>
 543
 544   <para>
 545    As of PostgreSQL 8.5, <type>hstore</> uses a different internal
 546    representation than previous versions. This presents no obstacle for
 547    dump/restore upgrades since the text representation (used in the dump) is
 548    unchanged.
 549   </para>
 550
 551   <para>
 552    In the event of a binary upgrade, upward compatibility is maintained by
 553    having the new code recognize old-format data. This will entail a slight
 554    performance penalty when processing data that has not yet been modified by
 555    the new code. It is possible to force an upgrade of all values in a table
 556    column by doing an <literal>UPDATE</> statement as follows:
 557   </para>
 558   <programlisting>
 559 UPDATE tablename SET hstorecol = hstorecol || '';
 560   </programlisting>
 561
 562   <para>
 563    Another way to do it is:
 564   <programlisting>
 565 ALTER TABLE tablename ALTER hstorecol TYPE hstore USING hstorecol || '';
 566   </programlisting>
 567    The <command>ALTER TABLE</> method requires an exclusive lock on the table,
 568    but does not result in bloating the table with old row versions.
 569   </para>
 570
 571  </sect2>
 572
 573  <sect2>
 574   <title>Authors</title>
 575
 576   <para>
 577    Oleg Bartunov <email>oleg@sai.msu.su</email>, Moscow, Moscow University, Russia
 578   </para>
 579
 580   <para>
 581    Teodor Sigaev <email>teodor@sigaev.ru</email>, Moscow, Delta-Soft Ltd., Russia
 582   </para>
 583
 584   <para>
 585    Additional enhancements by Andrew Gierth <email>andrew@tao11.riddles.org.uk</email>,
 586    United Kingdom
 587   </para>
 588  </sect2>
 589
 590 </sect1>