978 lines
31 KiB
Plaintext
978 lines
31 KiB
Plaintext
<!-- doc/src/sgml/hstore.sgml -->
|
|
|
|
<sect1 id="hstore" xreflabel="hstore">
|
|
<title>hstore — 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>=></literal>
|
|
<replaceable>value</replaceable> pairs separated by commas. Some examples:
|
|
|
|
<synopsis>
|
|
k => v
|
|
foo => bar, baz => whatever
|
|
"1-a" => "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>=></literal> sign is
|
|
ignored. Double-quote keys and values that include whitespace, commas,
|
|
<literal>=</literal>s or <literal>></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=>1,a=>2'::hstore;
|
|
hstore
|
|
----------
|
|
"a"=>"1"
|
|
</programlisting>
|
|
</para>
|
|
|
|
<para>
|
|
A value (but not a key) can be an SQL <literal>NULL</literal>. For example:
|
|
|
|
<programlisting>
|
|
key => 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>-></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=>x, b=>y'::hstore -> 'a'</literal>
|
|
<returnvalue>x</returnvalue>
|
|
</para></entry>
|
|
</row>
|
|
|
|
<row>
|
|
<entry role="func_table_entry"><para role="func_signature">
|
|
<type>hstore</type> <literal>-></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=>x, b=>y, c=>z'::hstore -> 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=>b, c=>d'::hstore || 'c=>x, d=>q'::hstore</literal>
|
|
<returnvalue>"a"=>"b", "c"=>"x", "d"=>"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=>1'::hstore ? 'a'</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 all the specified keys?
|
|
</para>
|
|
<para>
|
|
<literal>'a=>1,b=>2'::hstore ?& 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=>1,b=>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>@></literal> <type>hstore</type>
|
|
<returnvalue>boolean</returnvalue>
|
|
</para>
|
|
<para>
|
|
Does left operand contain right?
|
|
</para>
|
|
<para>
|
|
<literal>'a=>b, b=>1, c=>NULL'::hstore @> 'b=>1'</literal>
|
|
<returnvalue>t</returnvalue>
|
|
</para></entry>
|
|
</row>
|
|
|
|
<row>
|
|
<entry role="func_table_entry"><para role="func_signature">
|
|
<type>hstore</type> <literal><@</literal> <type>hstore</type>
|
|
<returnvalue>boolean</returnvalue>
|
|
</para>
|
|
<para>
|
|
Is left operand contained in right?
|
|
</para>
|
|
<para>
|
|
<literal>'a=>c'::hstore <@ 'a=>b, b=>1, c=>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=>1, b=>2, c=>3'::hstore - 'b'::text</literal>
|
|
<returnvalue>"a"=>"1", "c"=>"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=>1, b=>2, c=>3'::hstore - ARRAY['a','b']</literal>
|
|
<returnvalue>"c"=>"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=>1, b=>2, c=>3'::hstore - 'a=>4, b=>2'::hstore</literal>
|
|
<returnvalue>"a"=>"1", "c"=>"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=>foo, b=>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=>foo, b=>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"=>"1", "f2"=>"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"=>"1", "b"=>"2"</returnvalue>
|
|
</para>
|
|
<para>
|
|
<literal>hstore(ARRAY[['c','3'],['d','4']])</literal>
|
|
<returnvalue>"c"=>"3", "d"=>"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"=>"1", "b"=>"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"=>"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=>1,b=>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=>1,b=>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=>1,b=>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=>1,b=>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=>1,b=>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=>1,b=>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"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>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"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>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"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>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"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>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=>1,b=>2,c=>3'::hstore, ARRAY['b','c','x'])</literal>
|
|
<returnvalue>"b"=>"2", "c"=>"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=>1,b=>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=>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=>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=>1,b=>2', 'b')</literal>
|
|
<returnvalue>"a"=>"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=>1,b=>2,c=>3', ARRAY['a','b'])</literal>
|
|
<returnvalue>"c"=>"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=>1,b=>2', 'a=>4,b=>2'::hstore)</literal>
|
|
<returnvalue>"a"=>"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>-></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>@></literal>,
|
|
<literal>?</literal>, <literal>?&</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"=>"123", "col2"=>"foo", "col3"=>"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"=>"456", "col2"=>"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"=>"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=>bq, b=>NULL, ""=>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>
|