Google Git
Sign in
apache/cloudberry/refs/heads/string_replace_fix/./doc/src/sgml/hstore.sgml
blob: 870063c288baab3d2ee91560be997aeb3448bb5b [file]
<!-- doc/src/sgml/hstore.sgml -->
<sect1 id="hstore" xreflabel="hstore">
<title>hstore</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>
<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>
<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>
<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>
<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>
<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>
<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>
<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
extensions for PL/Python are
called <literal>hstore_plpythonu</literal>, <literal>hstore_plpython2u</literal>,
and <literal>hstore_plpython3u</literal>
(see <xref linkend="plpython-python23"/> for the PL/Python naming
convention). If you use them, <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>
<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>