postgresql/doc/src/sgml/hstore.sgml

<!-- doc/src/sgml/hstore.sgml -->

<sect1 id="hstore" xreflabel="hstore">
 <title>hstore &mdash; hstore key/value datatype</title>

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

 <para>
  This module implements the <type>hstore</type> data type for storing sets of
  key/value pairs within a single <productname>PostgreSQL</productname> 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>

 <para>
  This module is considered <quote>trusted</quote>, that is, it can be
  installed by non-superusers who have <literal>CREATE</literal> privilege
  on the current database.
 </para>

 <sect2 id="hstore-external-rep">
  <title><type>hstore</type> External Representation</title>

  <para>

   The text representation of an <type>hstore</type>, used for input and output,
   includes zero or more <replaceable>key</replaceable> <literal>=&gt;</literal>
   <replaceable>value</replaceable> 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;</literal> sign is
   ignored. Double-quote keys and values that include whitespace, commas,
   <literal>=</literal>s or <literal>&gt;</literal>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</type> is unique. If you declare an <type>hstore</type>
   with duplicate keys, only one will be stored in the <type>hstore</type> 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</literal>. For example:

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

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

  <note>
  <para>
   Keep in mind that the <type>hstore</type> text format, when used for input,
   applies <emphasis>before</emphasis> any required quoting or escaping. If you are
   passing an <type>hstore</type> 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</varname> 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 id="hstore-ops-funcs">
  <title><type>hstore</type> 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</type> Operators</title>
    <tgroup cols="1">
     <thead>
      <row>
       <entry role="func_table_entry"><para role="func_signature">
        Operator
       </para>
       <para>
        Description
       </para>
       <para>
        Example(s)
       </para></entry>
      </row>
     </thead>

     <tbody>
      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <type>hstore</type> <literal>-&gt;</literal> <type>text</type>
        <returnvalue>text</returnvalue>
       </para>
       <para>
        Returns value associated with given key, or <literal>NULL</literal> if
        not present.
       </para>
       <para>
        <literal>'a=&gt;x, b=&gt;y'::hstore -&gt; 'a'</literal>
        <returnvalue>x</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <type>hstore</type> <literal>-&gt;</literal> <type>text[]</type>
        <returnvalue>text[]</returnvalue>
       </para>
       <para>
        Returns values associated with given keys, or <literal>NULL</literal>
        if not present.
       </para>
       <para>
        <literal>'a=&gt;x, b=&gt;y, c=&gt;z'::hstore -&gt; ARRAY['c','a']</literal>
        <returnvalue>{"z","x"}</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <type>hstore</type> <literal>||</literal> <type>hstore</type>
        <returnvalue>hstore</returnvalue>
       </para>
       <para>
        Concatenates two <type>hstore</type>s.
       </para>
       <para>
        <literal>'a=&gt;b, c=&gt;d'::hstore || 'c=&gt;x, d=&gt;q'::hstore</literal>
        <returnvalue>"a"=&gt;"b", "c"=&gt;"x", "d"=&gt;"q"</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <type>hstore</type> <literal>?</literal> <type>text</type>
        <returnvalue>boolean</returnvalue>
       </para>
       <para>
        Does <type>hstore</type> contain key?
       </para>
       <para>
        <literal>'a=&gt;1'::hstore ? 'a'</literal>
        <returnvalue>t</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <type>hstore</type> <literal>?&amp;</literal> <type>text[]</type>
        <returnvalue>boolean</returnvalue>
       </para>
       <para>
        Does <type>hstore</type> contain all the specified keys?
       </para>
       <para>
        <literal>'a=&gt;1,b=&gt;2'::hstore ?&amp; ARRAY['a','b']</literal>
        <returnvalue>t</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <type>hstore</type> <literal>?|</literal> <type>text[]</type>
        <returnvalue>boolean</returnvalue>
       </para>
       <para>
        Does <type>hstore</type> contain any of the specified keys?
       </para>
       <para>
        <literal>'a=&gt;1,b=&gt;2'::hstore ?| ARRAY['b','c']</literal>
        <returnvalue>t</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <type>hstore</type> <literal>@&gt;</literal> <type>hstore</type>
        <returnvalue>boolean</returnvalue>
       </para>
       <para>
        Does left operand contain right?
       </para>
       <para>
        <literal>'a=&gt;b, b=&gt;1, c=&gt;NULL'::hstore @&gt; 'b=&gt;1'</literal>
        <returnvalue>t</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <type>hstore</type> <literal>&lt;@</literal> <type>hstore</type>
        <returnvalue>boolean</returnvalue>
       </para>
       <para>
        Is left operand contained in right?
       </para>
       <para>
        <literal>'a=&gt;c'::hstore &lt;@ 'a=&gt;b, b=&gt;1, c=&gt;NULL'</literal>
        <returnvalue>f</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <type>hstore</type> <literal>-</literal> <type>text</type>
        <returnvalue>hstore</returnvalue>
       </para>
       <para>
        Deletes key from left operand.
       </para>
       <para>
        <literal>'a=&gt;1, b=&gt;2, c=&gt;3'::hstore - 'b'::text</literal>
        <returnvalue>"a"=&gt;"1", "c"=&gt;"3"</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <type>hstore</type> <literal>-</literal> <type>text[]</type>
        <returnvalue>hstore</returnvalue>
       </para>
       <para>
        Deletes keys from left operand.
       </para>
       <para>
        <literal>'a=&gt;1, b=&gt;2, c=&gt;3'::hstore - ARRAY['a','b']</literal>
        <returnvalue>"c"=&gt;"3"</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <type>hstore</type> <literal>-</literal> <type>hstore</type>
        <returnvalue>hstore</returnvalue>
       </para>
       <para>
        Deletes pairs from left operand that match pairs in the right operand.
       </para>
       <para>
        <literal>'a=&gt;1, b=&gt;2, c=&gt;3'::hstore - 'a=&gt;4, b=&gt;2'::hstore</literal>
        <returnvalue>"a"=&gt;"1", "c"=&gt;"3"</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <type>anyelement</type> <literal>#=</literal> <type>hstore</type>
        <returnvalue>anyelement</returnvalue>
       </para>
       <para>
        Replaces fields in the left operand (which must be a composite type)
        with matching values from <type>hstore</type>.
       </para>
       <para>
        <literal>ROW(1,3) #= 'f1=>11'::hstore</literal>
        <returnvalue>(11,3)</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <literal>%%</literal> <type>hstore</type>
        <returnvalue>text[]</returnvalue>
       </para>
       <para>
        Converts <type>hstore</type> to an array of alternating keys and
        values.
       </para>
       <para>
        <literal>%% 'a=&gt;foo, b=&gt;bar'::hstore</literal>
        <returnvalue>{a,foo,b,bar}</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <literal>%#</literal> <type>hstore</type>
        <returnvalue>text[]</returnvalue>
       </para>
       <para>
        Converts <type>hstore</type> to a two-dimensional key/value array.
       </para>
       <para>
        <literal>%# 'a=&gt;foo, b=&gt;bar'::hstore</literal>
        <returnvalue>{{a,foo},{b,bar}}</returnvalue>
       </para></entry>
      </row>
     </tbody>
    </tgroup>
  </table>

  <table id="hstore-func-table">
   <title><type>hstore</type> Functions</title>
    <tgroup cols="1">
     <thead>
      <row>
       <entry role="func_table_entry"><para role="func_signature">
        Function
       </para>
       <para>
        Description
       </para>
       <para>
        Example(s)
       </para></entry>
      </row>
     </thead>

     <tbody>
      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm><primary>hstore</primary></indexterm>
        <function>hstore</function> ( <type>record</type> )
        <returnvalue>hstore</returnvalue>
       </para>
       <para>
        Constructs an <type>hstore</type> from a record or row.
       </para>
       <para>
        <literal>hstore(ROW(1,2))</literal>
        <returnvalue>"f1"=&gt;"1", "f2"=&gt;"2"</returnvalue>
       </para></entry>
      </row>

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

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

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <function>hstore</function> ( <type>text</type>, <type>text</type> )
        <returnvalue>hstore</returnvalue>
       </para>
       <para>
        Makes a single-item <type>hstore</type>.
       </para>
       <para>
        <literal>hstore('a', 'b')</literal>
        <returnvalue>"a"=&gt;"b"</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm><primary>akeys</primary></indexterm>
        <function>akeys</function> ( <type>hstore</type> )
        <returnvalue>text[]</returnvalue>
       </para>
       <para>
        Extracts an <type>hstore</type>'s keys as an array.
       </para>
       <para>
        <literal>akeys('a=&gt;1,b=&gt;2')</literal>
        <returnvalue>{a,b}</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm><primary>skeys</primary></indexterm>
        <function>skeys</function> ( <type>hstore</type> )
        <returnvalue>setof text</returnvalue>
       </para>
       <para>
        Extracts an <type>hstore</type>'s keys as a set.
       </para>
       <para>
        <literal>skeys('a=&gt;1,b=&gt;2')</literal>
        <returnvalue></returnvalue>
<programlisting>
a
b
</programlisting>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm><primary>avals</primary></indexterm>
        <function>avals</function> ( <type>hstore</type> )
        <returnvalue>text[]</returnvalue>
       </para>
       <para>
        Extracts an <type>hstore</type>'s values as an array.
       </para>
       <para>
        <literal>avals('a=&gt;1,b=&gt;2')</literal>
        <returnvalue>{1,2}</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm><primary>svals</primary></indexterm>
        <function>svals</function> ( <type>hstore</type> )
        <returnvalue>setof text</returnvalue>
       </para>
       <para>
        Extracts an <type>hstore</type>'s values as a set.
       </para>
       <para>
        <literal>svals('a=&gt;1,b=&gt;2')</literal>
        <returnvalue></returnvalue>
<programlisting>
1
2
</programlisting>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm><primary>hstore_to_array</primary></indexterm>
        <function>hstore_to_array</function> ( <type>hstore</type> )
        <returnvalue>text[]</returnvalue>
       </para>
       <para>
        Extracts an <type>hstore</type>'s keys and values as an array of
        alternating keys and values.
       </para>
       <para>
        <literal>hstore_to_array('a=&gt;1,b=&gt;2')</literal>
        <returnvalue>{a,1,b,2}</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm><primary>hstore_to_matrix</primary></indexterm>
        <function>hstore_to_matrix</function> ( <type>hstore</type> )
        <returnvalue>text[]</returnvalue>
       </para>
       <para>
        Extracts an <type>hstore</type>'s keys and values as a two-dimensional
        array.
       </para>
       <para>
        <literal>hstore_to_matrix('a=&gt;1,b=&gt;2')</literal>
        <returnvalue>{{a,1},{b,2}}</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm><primary>hstore_to_json</primary></indexterm>
        <function>hstore_to_json</function> ( <type>hstore</type> )
        <returnvalue>json</returnvalue>
       </para>
       <para>
        Converts an <type>hstore</type> to a <type>json</type> value,
        converting all non-null values to JSON strings.
       </para>
       <para>
        This function is used implicitly when an <type>hstore</type> value is
        cast to <type>json</type>.
       </para>
       <para>
        <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>
        <returnvalue>{"a key": "1", "b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4"}</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm><primary>hstore_to_jsonb</primary></indexterm>
        <function>hstore_to_jsonb</function> ( <type>hstore</type> )
        <returnvalue>jsonb</returnvalue>
       </para>
       <para>
        Converts an <type>hstore</type> to a <type>jsonb</type> value,
        converting all non-null values to JSON strings.
       </para>
       <para>
        This function is used implicitly when an <type>hstore</type> value is
        cast to <type>jsonb</type>.
       </para>
       <para>
        <literal>hstore_to_jsonb('"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>
        <returnvalue>{"a key": "1", "b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4"}</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm><primary>hstore_to_json_loose</primary></indexterm>
        <function>hstore_to_json_loose</function> ( <type>hstore</type> )
        <returnvalue>json</returnvalue>
       </para>
       <para>
        Converts an <type>hstore</type> to a <type>json</type> value, but
        attempts to distinguish numerical and Boolean values so they are
        unquoted in the JSON.
       </para>
       <para>
        <literal>hstore_to_json_loose('"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>
        <returnvalue>{"a key": 1, "b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4}</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm><primary>hstore_to_jsonb_loose</primary></indexterm>
        <function>hstore_to_jsonb_loose</function> ( <type>hstore</type> )
        <returnvalue>jsonb</returnvalue>
       </para>
       <para>
        Converts an <type>hstore</type> to a <type>jsonb</type> value, but
        attempts to distinguish numerical and Boolean values so they are
        unquoted in the JSON.
       </para>
       <para>
        <literal>hstore_to_jsonb_loose('"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>
        <returnvalue>{"a key": 1, "b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4}</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm><primary>slice</primary></indexterm>
        <function>slice</function> ( <type>hstore</type>, <type>text[]</type> )
        <returnvalue>hstore</returnvalue>
       </para>
       <para>
        Extracts a subset of an <type>hstore</type> containing only the
        specified keys.
       </para>
       <para>
        <literal>slice('a=&gt;1,b=&gt;2,c=&gt;3'::hstore, ARRAY['b','c','x'])</literal>
        <returnvalue>"b"=&gt;"2", "c"=&gt;"3"</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm><primary>each</primary></indexterm>
        <function>each</function> ( <type>hstore</type> )
        <returnvalue>setof record</returnvalue>
        ( <parameter>key</parameter> <type>text</type>,
        <parameter>value</parameter> <type>text</type> )
       </para>
       <para>
        Extracts an <type>hstore</type>'s keys and values as a set of records.
       </para>
       <para>
        <literal>select * from each('a=&gt;1,b=&gt;2')</literal>
        <returnvalue></returnvalue>
<programlisting>
 key | value
-----+-------
 a   | 1
 b   | 2
</programlisting>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm><primary>exist</primary></indexterm>
        <function>exist</function> ( <type>hstore</type>, <type>text</type> )
        <returnvalue>boolean</returnvalue>
       </para>
       <para>
        Does <type>hstore</type> contain key?
       </para>
       <para>
        <literal>exist('a=&gt;1', 'a')</literal>
        <returnvalue>t</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm><primary>defined</primary></indexterm>
        <function>defined</function> ( <type>hstore</type>, <type>text</type> )
        <returnvalue>boolean</returnvalue>
       </para>
       <para>
        Does <type>hstore</type> contain a non-<literal>NULL</literal> value
        for key?
       </para>
       <para>
        <literal>defined('a=&gt;NULL', 'a')</literal>
        <returnvalue>f</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm><primary>delete</primary></indexterm>
        <function>delete</function> ( <type>hstore</type>, <type>text</type> )
        <returnvalue>hstore</returnvalue>
       </para>
       <para>
        Deletes pair with matching key.
       </para>
       <para>
        <literal>delete('a=&gt;1,b=&gt;2', 'b')</literal>
        <returnvalue>"a"=&gt;"1"</returnvalue>
       </para></entry>
      </row>

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

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

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm><primary>populate_record</primary></indexterm>
        <function>populate_record</function> ( <type>anyelement</type>, <type>hstore</type> )
        <returnvalue>anyelement</returnvalue>
       </para>
       <para>
        Replaces fields in the left operand (which must be a composite type)
        with matching values from <type>hstore</type>.
       </para>
       <para>
        <literal>populate_record(ROW(1,2), 'f1=>42'::hstore)</literal>
        <returnvalue>(42,2)</returnvalue>
       </para></entry>
      </row>
    </tbody>
   </tgroup>
  </table>

  <para>
   In addition to these operators and functions, values of
   the <type>hstore</type> type can be subscripted, allowing them to act
   like associative arrays.  Only a single subscript of type <type>text</type>
   can be specified; it is interpreted as a key and the corresponding
   value is fetched or stored.  For example,

<programlisting>
CREATE TABLE mytable (h hstore);
INSERT INTO mytable VALUES ('a=>b, c=>d');
SELECT h['a'] FROM mytable;
 h
---
 b
(1 row)

UPDATE mytable SET h['c'] = 'new';
SELECT h FROM mytable;
          h
----------------------
 "a"=>"b", "c"=>"new"
(1 row)
</programlisting>

   A subscripted fetch returns <literal>NULL</literal> if the subscript
   is <literal>NULL</literal> or that key does not exist in
   the <type>hstore</type>.  (Thus, a subscripted fetch is not greatly
   different from the <literal>-&gt;</literal> operator.)
   A subscripted update fails if the subscript is <literal>NULL</literal>;
   otherwise, it replaces the value for that key, adding an entry to
   the <type>hstore</type> if the key does not already exist.
  </para>
 </sect2>

 <sect2 id="hstore-indexes">
  <title>Indexes</title>

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

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

  <para>
   <literal>gist_hstore_ops</literal> GiST opclass approximates a set of
   key/value pairs as a bitmap signature.  Its optional integer parameter
   <literal>siglen</literal> determines the
   signature length in bytes.  The default length is 16 bytes.
   Valid values of signature length are between 1 and 2024 bytes.  Longer
   signatures lead to a more precise search (scanning a smaller fraction of the index and
   fewer heap pages), at the cost of a larger index.
  </para>

  <para>
   Example of creating such an index with a signature length of 32 bytes:
<programlisting>
CREATE INDEX hidx ON testhstore USING GIST (h gist_hstore_ops(siglen=32));
</programlisting>
  </para>

  <para>
   <type>hstore</type> also supports <type>btree</type> or <type>hash</type> indexes for
   the <literal>=</literal> operator. This allows <type>hstore</type> columns to be
   declared <literal>UNIQUE</literal>, or to be used in <literal>GROUP BY</literal>,
   <literal>ORDER BY</literal> or <literal>DISTINCT</literal> expressions. The sort ordering
   for <type>hstore</type> values is not particularly useful, but these indexes
   may be useful for equivalence lookups. Create indexes for <literal>=</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 id="hstore-examples">
  <title>Examples</title>

  <para>
   Add a key, or update an existing key with a new value:
<programlisting>
UPDATE tab SET h['c'] = '3';
</programlisting>
   Another way to do the same thing is:
<programlisting>
UPDATE tab SET h = h || hstore('c', '3');
</programlisting>
   If multiple keys are to be added or changed in one operation,
   the concatenation approach is more efficient than subscripting:
<programlisting>
UPDATE tab SET h = h || hstore(array['q', 'w'], array['11', '12']);
</programlisting>
  </para>

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

  <para>
   Convert a <type>record</type> to an <type>hstore</type>:
<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</type> to a predefined <type>record</type> 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</type>:
<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 id="hstore-statistics">
  <title>Statistics</title>

  <para>
   The <type>hstore</type> 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>
CREATE TABLE stat AS SELECT (each(h)).key, (each(h)).value 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 id="hstore-compatibility">
  <title>Compatibility</title>

  <para>
   As of PostgreSQL 9.0, <type>hstore</type> 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</literal> 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</command> method requires an
   <literal>ACCESS EXCLUSIVE</literal> lock on the table,
   but does not result in bloating the table with old row versions.
  </para>

 </sect2>

 <sect2 id="hstore-transforms">
  <title>Transforms</title>

  <para>
   Additional extensions are available that implement transforms for
   the <type>hstore</type> type for the languages PL/Perl and PL/Python.  The
   extensions for PL/Perl are called <literal>hstore_plperl</literal>
   and <literal>hstore_plperlu</literal>, for trusted and untrusted PL/Perl.
   If you install these transforms and specify them when creating a
   function, <type>hstore</type> values are mapped to Perl hashes.  The
   extension for PL/Python is called <literal>hstore_plpython3u</literal>.
   If you use it, <type>hstore</type> values are mapped to Python dictionaries.
  </para>

  <caution>
   <para>
    It is strongly recommended that the transform extensions be installed in
    the same schema as <filename>hstore</filename>.  Otherwise there are
    installation-time security hazards if a transform extension's schema
    contains objects defined by a hostile user.
   </para>
  </caution>
 </sect2>

 <sect2 id="hstore-authors">
  <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>