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

   1 <!-- doc/src/sgml/hstore.sgml -->
   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 <synopsis>
  28 k =&gt; v
  29 foo =&gt; bar, baz =&gt; whatever
  30 "1-a" =&gt; "anything at all"
  31 </synopsis>
  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 <quote>NULL</quote>.
  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>hstore</> <literal>||</> <type>hstore</></entry>
 125       <entry>concatenate <type>hstore</>s</entry>
 126       <entry><literal>'a=&gt;b, c=&gt;d'::hstore || 'c=&gt;x, d=&gt;q'::hstore</literal></entry>
 127       <entry><literal>"a"=&gt;"b", "c"=&gt;"x", "d"=&gt;"q"</literal></entry>
 128      </row>
 129
 130      <row>
 131       <entry><type>hstore</> <literal>?</> <type>text</></entry>
 132       <entry>does <type>hstore</> contain key?</entry>
 133       <entry><literal>'a=&gt;1'::hstore ? 'a'</literal></entry>
 134       <entry><literal>t</literal></entry>
 135      </row>
 136
 137      <row>
 138       <entry><type>hstore</> <literal>?&amp;</> <type>text[]</></entry>
 139       <entry>does <type>hstore</> contain all specified keys?</entry>
 140       <entry><literal>'a=&gt;1,b=&gt;2'::hstore ?&amp; ARRAY['a','b']</literal></entry>
 141       <entry><literal>t</literal></entry>
 142      </row>
 143
 144      <row>
 145       <entry><type>hstore</> <literal>?|</> <type>text[]</></entry>
 146       <entry>does <type>hstore</> contain any of the specified keys?</entry>
 147       <entry><literal>'a=&gt;1,b=&gt;2'::hstore ?| ARRAY['b','c']</literal></entry>
 148       <entry><literal>t</literal></entry>
 149      </row>
 150
 151      <row>
 152       <entry><type>hstore</> <literal>@&gt;</> <type>hstore</></entry>
 153       <entry>does left operand contain right?</entry>
 154       <entry><literal>'a=&gt;b, b=&gt;1, c=&gt;NULL'::hstore @&gt; 'b=&gt;1'</literal></entry>
 155       <entry><literal>t</literal></entry>
 156      </row>
 157
 158      <row>
 159       <entry><type>hstore</> <literal>&lt;@</> <type>hstore</></entry>
 160       <entry>is left operand contained in right?</entry>
 161       <entry><literal>'a=&gt;c'::hstore &lt;@ 'a=&gt;b, b=&gt;1, c=&gt;NULL'</literal></entry>
 162       <entry><literal>f</literal></entry>
 163      </row>
 164
 165      <row>
 166       <entry><type>hstore</> <literal>-</> <type>text</></entry>
 167       <entry>delete key from left operand</entry>
 168       <entry><literal>'a=&gt;1, b=&gt;2, c=&gt;3'::hstore - 'b'::text</literal></entry>
 169       <entry><literal>"a"=&gt;"1", "c"=&gt;"3"</literal></entry>
 170      </row>
 171
 172      <row>
 173       <entry><type>hstore</> <literal>-</> <type>text[]</></entry>
 174       <entry>delete keys from left operand</entry>
 175       <entry><literal>'a=&gt;1, b=&gt;2, c=&gt;3'::hstore - ARRAY['a','b']</literal></entry>
 176       <entry><literal>"c"=&gt;"3"</literal></entry>
 177      </row>
 178
 179      <row>
 180       <entry><type>hstore</> <literal>-</> <type>hstore</></entry>
 181       <entry>delete matching pairs from left operand</entry>
 182       <entry><literal>'a=&gt;1, b=&gt;2, c=&gt;3'::hstore - 'a=&gt;4, b=&gt;2'::hstore</literal></entry>
 183       <entry><literal>"a"=&gt;"1", "c"=&gt;"3"</literal></entry>
 184      </row>
 185
 186      <row>
 187       <entry><type>record</> <literal>#=</> <type>hstore</></entry>
 188       <entry>replace fields in <type>record</> with matching values from <type>hstore</></entry>
 189       <entry>see Examples section</entry>
 190       <entry></entry>
 191      </row>
 192
 193      <row>
 194       <entry><literal>%%</> <type>hstore</></entry>
 195       <entry>convert <type>hstore</> to array of alternating keys and values</entry>
 196       <entry><literal>%% 'a=&gt;foo, b=&gt;bar'::hstore</literal></entry>
 197       <entry><literal>{a,foo,b,bar}</literal></entry>
 198      </row>
 199
 200      <row>
 201       <entry><literal>%#</> <type>hstore</></entry>
 202       <entry>convert <type>hstore</> to two-dimensional key/value array</entry>
 203       <entry><literal>%# 'a=&gt;foo, b=&gt;bar'::hstore</literal></entry>
 204       <entry><literal>{{a,foo},{b,bar}}</literal></entry>
 205      </row>
 206
 207     </tbody>
 208    </tgroup>
 209   </table>
 210
 211   <note>
 212   <para>
 213    Prior to PostgreSQL 8.2, the containment operators <literal>@&gt;</>
 214    and <literal>&lt;@</> were called <literal>@</> and <literal>~</>,
 215    respectively. These names are still available, but are deprecated and will
 216    eventually be removed. Notice that the old names are reversed from the
 217    convention formerly followed by the core geometric data types!
 218    </para>
 219   </note>
 220
 221   <note>
 222   <para>
 223    The <literal>=&gt;</> operator is deprecated and may be removed in a
 224    future release.  Use the <literal>hstore(text, text)</literal> function
 225    instead.
 226    </para>
 227   </note>
 228
 229   <table id="hstore-func-table">
 230    <title><type>hstore</> Functions</title>
 231
 232    <tgroup cols="5">
 233     <thead>
 234      <row>
 235       <entry>Function</entry>
 236       <entry>Return Type</entry>
 237       <entry>Description</entry>
 238       <entry>Example</entry>
 239       <entry>Result</entry>
 240      </row>
 241     </thead>
 242
 243     <tbody>
 244      <row>
 245       <entry><function>hstore(record)</function></entry>
 246       <entry><type>hstore</type></entry>
 247       <entry>construct an <type>hstore</> from a record or row</entry>
 248       <entry><literal>hstore(ROW(1,2))</literal></entry>
 249       <entry><literal>f1=&gt;1,f2=&gt;2</literal></entry>
 250      </row>
 251
 252      <row>
 253       <entry><function>hstore(text[])</function></entry>
 254       <entry><type>hstore</type></entry>
 255       <entry>construct an <type>hstore</> from an array, which may be either
 256        a key/value array, or a two-dimensional array</entry>
 257       <entry><literal>hstore(ARRAY['a','1','b','2']) || hstore(ARRAY[['c','3'],['d','4']])</literal></entry>
 258       <entry><literal>a=&gt;1, b=&gt;2, c=&gt;3, d=&gt;4</literal></entry>
 259      </row>
 260
 261      <row>
 262       <entry><function>hstore(text[], text[])</function></entry>
 263       <entry><type>hstore</type></entry>
 264       <entry>construct an <type>hstore</> from separate key and value arrays</entry>
 265       <entry><literal>hstore(ARRAY['a','b'], ARRAY['1','2'])</literal></entry>
 266       <entry><literal>"a"=&gt;"1","b"=&gt;"2"</literal></entry>
 267      </row>
 268
 269      <row>
 270       <entry><function>hstore(text, text)</function></entry>
 271       <entry><type>hstore</type></entry>
 272       <entry>make single-item <type>hstore</></entry>
 273       <entry><literal>hstore('a', 'b')</literal></entry>
 274       <entry><literal>"a"=&gt;"b"</literal></entry>
 275      </row>
 276
 277      <row>
 278       <entry><function>akeys(hstore)</function></entry>
 279       <entry><type>text[]</type></entry>
 280       <entry>get <type>hstore</>'s keys as an array</entry>
 281       <entry><literal>akeys('a=&gt;1,b=&gt;2')</literal></entry>
 282       <entry><literal>{a,b}</literal></entry>
 283      </row>
 284
 285      <row>
 286       <entry><function>skeys(hstore)</function></entry>
 287       <entry><type>setof text</type></entry>
 288       <entry>get <type>hstore</>'s keys as a set</entry>
 289       <entry><literal>skeys('a=&gt;1,b=&gt;2')</literal></entry>
 290       <entry>
 291 <programlisting>
 292 a
 293 b
 294 </programlisting></entry>
 295      </row>
 296
 297      <row>
 298       <entry><function>avals(hstore)</function></entry>
 299       <entry><type>text[]</type></entry>
 300       <entry>get <type>hstore</>'s values as an array</entry>
 301       <entry><literal>avals('a=&gt;1,b=&gt;2')</literal></entry>
 302       <entry><literal>{1,2}</literal></entry>
 303      </row>
 304
 305      <row>
 306       <entry><function>svals(hstore)</function></entry>
 307       <entry><type>setof text</type></entry>
 308       <entry>get <type>hstore</>'s values as a set</entry>
 309       <entry><literal>svals('a=&gt;1,b=&gt;2')</literal></entry>
 310       <entry>
 311 <programlisting>
 312 1
 313 2
 314 </programlisting></entry>
 315      </row>
 316
 317      <row>
 318       <entry><function>hstore_to_array(hstore)</function></entry>
 319       <entry><type>text[]</type></entry>
 320       <entry>get <type>hstore</>'s keys and values as an array of alternating
 321        keys and values</entry>
 322       <entry><literal>hstore_to_array('a=&gt;1,b=&gt;2')</literal></entry>
 323       <entry><literal>{a,1,b,2}</literal></entry>
 324      </row>
 325
 326      <row>
 327       <entry><function>hstore_to_matrix(hstore)</function></entry>
 328       <entry><type>text[]</type></entry>
 329       <entry>get <type>hstore</>'s keys and values as a two-dimensional array</entry>
 330       <entry><literal>hstore_to_matrix('a=&gt;1,b=&gt;2')</literal></entry>
 331       <entry><literal>{{a,1},{b,2}}</literal></entry>
 332      </row>
 333
 334      <row>
 335       <entry><function>slice(hstore, text[])</function></entry>
 336       <entry><type>hstore</type></entry>
 337       <entry>extract a subset of an <type>hstore</></entry>
 338       <entry><literal>slice('a=&gt;1,b=&gt;2,c=&gt;3'::hstore, ARRAY['b','c','x'])</literal></entry>
 339       <entry><literal>"b"=&gt;"2", "c"=&gt;"3"</literal></entry>
 340      </row>
 341
 342      <row>
 343       <entry><function>each(hstore)</function></entry>
 344       <entry><type>setof(key text, value text)</type></entry>
 345       <entry>get <type>hstore</>'s keys and values as a set</entry>
 346       <entry><literal>select * from each('a=&gt;1,b=&gt;2')</literal></entry>
 347       <entry>
 348 <programlisting>
 349  key | value
 350 -----+-------
 351  a   | 1
 352  b   | 2
 353 </programlisting></entry>
 354      </row>
 355
 356      <row>
 357       <entry><function>exist(hstore,text)</function></entry>
 358       <entry><type>boolean</type></entry>
 359       <entry>does <type>hstore</> contain key?</entry>
 360       <entry><literal>exist('a=&gt;1','a')</literal></entry>
 361       <entry><literal>t</literal></entry>
 362      </row>
 363
 364      <row>
 365       <entry><function>defined(hstore,text)</function></entry>
 366       <entry><type>boolean</type></entry>
 367       <entry>does <type>hstore</> contain non-<literal>NULL</> value for key?</entry>
 368       <entry><literal>defined('a=&gt;NULL','a')</literal></entry>
 369       <entry><literal>f</literal></entry>
 370      </row>
 371
 372      <row>
 373       <entry><function>delete(hstore,text)</function></entry>
 374       <entry><type>hstore</type></entry>
 375       <entry>delete pair with matching key</entry>
 376       <entry><literal>delete('a=&gt;1,b=&gt;2','b')</literal></entry>
 377       <entry><literal>"a"=>"1"</literal></entry>
 378      </row>
 379
 380      <row>
 381       <entry><function>delete(hstore,text[])</function></entry>
 382       <entry><type>hstore</type></entry>
 383       <entry>delete pairs with matching keys</entry>
 384       <entry><literal>delete('a=&gt;1,b=&gt;2,c=&gt;3',ARRAY['a','b'])</literal></entry>
 385       <entry><literal>"c"=>"3"</literal></entry>
 386      </row>
 387
 388      <row>
 389       <entry><function>delete(hstore,hstore)</function></entry>
 390       <entry><type>hstore</type></entry>
 391       <entry>delete pairs matching those in the second argument</entry>
 392       <entry><literal>delete('a=&gt;1,b=&gt;2','a=&gt;4,b=&gt;2'::hstore)</literal></entry>
 393       <entry><literal>"a"=>"1"</literal></entry>
 394      </row>
 395
 396      <row>
 397       <entry><function>populate_record(record,hstore)</function></entry>
 398       <entry><type>record</type></entry>
 399       <entry>replace fields in <type>record</> with matching values from <type>hstore</></entry>
 400       <entry>see Examples section</entry>
 401       <entry></entry>
 402      </row>
 403
 404     </tbody>
 405    </tgroup>
 406   </table>
 407
 408   <note>
 409    <para>
 410     The function <function>populate_record</function> is actually declared
 411     with <type>anyelement</>, not <type>record</>, as its first argument,
 412     but it will reject non-record types with a run-time error.
 413    </para>
 414   </note>
 415  </sect2>
 416
 417  <sect2>
 418   <title>Indexes</title>
 419
 420   <para>
 421    <type>hstore</> has GiST and GIN index support for the <literal>@&gt;</>,
 422    <literal>?</>, <literal>?&amp;</> and <literal>?|</> operators. For example:
 423   </para>
 424 <programlisting>
 425 CREATE INDEX hidx ON testhstore USING GIST (h);
 426
 427 CREATE INDEX hidx ON testhstore USING GIN (h);
 428 </programlisting>
 429
 430   <para>
 431    <type>hstore</> also supports <type>btree</> or <type>hash</> indexes for
 432    the <literal>=</> operator. This allows <type>hstore</> columns to be
 433    declared <literal>UNIQUE</>, or to be used in <literal>GROUP BY</>,
 434    <literal>ORDER BY</> or <literal>DISTINCT</> expressions. The sort ordering
 435    for <type>hstore</> values is not particularly useful, but these indexes
 436    may be useful for equivalence lookups. Create indexes for <literal>=</>
 437    comparisons as follows:
 438   </para>
 439 <programlisting>
 440 CREATE INDEX hidx ON testhstore USING BTREE (h);
 441
 442 CREATE INDEX hidx ON testhstore USING HASH (h);
 443 </programlisting>
 444  </sect2>
 445
 446  <sect2>
 447   <title>Examples</title>
 448
 449   <para>
 450    Add a key, or update an existing key with a new value:
 451 <programlisting>
 452 UPDATE tab SET h = h || ('c' =&gt; '3');
 453 </programlisting>
 454   </para>
 455
 456   <para>
 457    Delete a key:
 458 <programlisting>
 459 UPDATE tab SET h = delete(h, 'k1');
 460 </programlisting>
 461   </para>
 462
 463   <para>
 464    Convert a <type>record</> to an <type>hstore</>:
 465 <programlisting>
 466 CREATE TABLE test (col1 integer, col2 text, col3 text);
 467 INSERT INTO test VALUES (123, 'foo', 'bar');
 468
 469 SELECT hstore(t) FROM test AS t;
 470                    hstore
 471 ---------------------------------------------
 472  "col1"=&gt;"123", "col2"=&gt;"foo", "col3"=&gt;"bar"
 473 (1 row)
 474 </programlisting>
 475   </para>
 476
 477   <para>
 478    Convert an <type>hstore</> to a predefined <type>record</> type:
 479 <programlisting>
 480 CREATE TABLE test (col1 integer, col2 text, col3 text);
 481
 482 SELECT * FROM populate_record(null::test,
 483                               '"col1"=&gt;"456", "col2"=&gt;"zzz"');
 484  col1 | col2 | col3
 485 ------+------+------
 486   456 | zzz  |
 487 (1 row)
 488 </programlisting>
 489   </para>
 490
 491   <para>
 492    Modify an existing record using the values from an <type>hstore</>:
 493 <programlisting>
 494 CREATE TABLE test (col1 integer, col2 text, col3 text);
 495 INSERT INTO test VALUES (123, 'foo', 'bar');
 496
 497 SELECT (r).* FROM (SELECT t #= '"col3"=&gt;"baz"' AS r FROM test t) s;
 498  col1 | col2 | col3
 499 ------+------+------
 500   123 | foo  | baz
 501 (1 row)
 502 </programlisting>
 503   </para>
 504  </sect2>
 505
 506  <sect2>
 507   <title>Statistics</title>
 508
 509   <para>
 510    The <type>hstore</> type, because of its intrinsic liberality, could
 511    contain a lot of different keys. Checking for valid keys is the task of the
 512    application. The following examples demonstrate several techniques for
 513    checking keys and obtaining statistics.
 514   </para>
 515
 516   <para>
 517    Simple example:
 518 <programlisting>
 519 SELECT * FROM each('aaa=&gt;bq, b=&gt;NULL, ""=&gt;1');
 520 </programlisting>
 521   </para>
 522
 523   <para>
 524    Using a table:
 525 <programlisting>
 526 SELECT (each(h)).key, (each(h)).value INTO stat FROM testhstore;
 527 </programlisting>
 528   </para>
 529
 530   <para>
 531    Online statistics:
 532 <programlisting>
 533 SELECT key, count(*) FROM
 534   (SELECT (each(h)).key FROM testhstore) AS stat
 535   GROUP BY key
 536   ORDER BY count DESC, key;
 537     key    | count
 538 -----------+-------
 539  line      |   883
 540  query     |   207
 541  pos       |   203
 542  node      |   202
 543  space     |   197
 544  status    |   195
 545  public    |   194
 546  title     |   190
 547  org       |   189
 548 ...................
 549 </programlisting>
 550   </para>
 551  </sect2>
 552
 553  <sect2>
 554   <title>Compatibility</title>
 555
 556   <para>
 557    <emphasis>When upgrading from older versions, always load the new
 558    version of this module into the database before restoring a dump.
 559    Otherwise, many new features will be unavailable.</emphasis>
 560   </para>
 561
 562   <para>
 563    As of PostgreSQL 9.0, <type>hstore</> uses a different internal
 564    representation than previous versions. This presents no obstacle for
 565    dump/restore upgrades since the text representation (used in the dump) is
 566    unchanged.
 567   </para>
 568
 569   <para>
 570    In the event of a binary upgrade, upward compatibility is maintained by
 571    having the new code recognize old-format data. This will entail a slight
 572    performance penalty when processing data that has not yet been modified by
 573    the new code. It is possible to force an upgrade of all values in a table
 574    column by doing an <literal>UPDATE</> statement as follows:
 575 <programlisting>
 576 UPDATE tablename SET hstorecol = hstorecol || '';
 577 </programlisting>
 578   </para>
 579
 580   <para>
 581    Another way to do it is:
 582 <programlisting>
 583 ALTER TABLE tablename ALTER hstorecol TYPE hstore USING hstorecol || '';
 584 </programlisting>
 585    The <command>ALTER TABLE</> method requires an exclusive lock on the table,
 586    but does not result in bloating the table with old row versions.
 587   </para>
 588
 589  </sect2>
 590
 591  <sect2>
 592   <title>Authors</title>
 593
 594   <para>
 595    Oleg Bartunov <email>oleg@sai.msu.su</email>, Moscow, Moscow University, Russia
 596   </para>
 597
 598   <para>
 599    Teodor Sigaev <email>teodor@sigaev.ru</email>, Moscow, Delta-Soft Ltd., Russia
 600   </para>
 601
 602   <para>
 603    Additional enhancements by Andrew Gierth <email>andrew@tao11.riddles.org.uk</email>,
 604    United Kingdom
 605   </para>
 606  </sect2>
 607
 608 </sect1>