diff options
Diffstat (limited to 'doc/src/sgml/hstore.sgml')
| -rw-r--r-- | doc/src/sgml/hstore.sgml | 977 |
1 files changed, 977 insertions, 0 deletions
diff --git a/doc/src/sgml/hstore.sgml b/doc/src/sgml/hstore.sgml new file mode 100644 index 0000000..335d646 --- /dev/null +++ b/doc/src/sgml/hstore.sgml @@ -0,0 +1,977 @@ +<!-- 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>=></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> + <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> + <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> + <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> + <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> + <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 + 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> + <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> |