diff options
Diffstat (limited to 'doc/src/sgml/html/hstore.html')
| -rw-r--r-- | doc/src/sgml/html/hstore.html | 699 |
1 files changed, 699 insertions, 0 deletions
diff --git a/doc/src/sgml/html/hstore.html b/doc/src/sgml/html/hstore.html new file mode 100644 index 0000000..1b5c7ff --- /dev/null +++ b/doc/src/sgml/html/hstore.html @@ -0,0 +1,699 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>F.18. hstore</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="fuzzystrmatch.html" title="F.17. fuzzystrmatch" /><link rel="next" href="intagg.html" title="F.19. intagg" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">F.18. hstore</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="fuzzystrmatch.html" title="F.17. fuzzystrmatch">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules">Up</a></td><th width="60%" align="center">Appendix F. Additional Supplied Modules</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="intagg.html" title="F.19. intagg">Next</a></td></tr></table><hr /></div><div class="sect1" id="HSTORE"><div class="titlepage"><div><div><h2 class="title" style="clear: both">F.18. hstore</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="hstore.html#id-1.11.7.27.5">F.18.1. <code class="type">hstore</code> External Representation</a></span></dt><dt><span class="sect2"><a href="hstore.html#id-1.11.7.27.6">F.18.2. <code class="type">hstore</code> Operators and Functions</a></span></dt><dt><span class="sect2"><a href="hstore.html#id-1.11.7.27.7">F.18.3. Indexes</a></span></dt><dt><span class="sect2"><a href="hstore.html#id-1.11.7.27.8">F.18.4. Examples</a></span></dt><dt><span class="sect2"><a href="hstore.html#id-1.11.7.27.9">F.18.5. Statistics</a></span></dt><dt><span class="sect2"><a href="hstore.html#id-1.11.7.27.10">F.18.6. Compatibility</a></span></dt><dt><span class="sect2"><a href="hstore.html#id-1.11.7.27.11">F.18.7. Transforms</a></span></dt><dt><span class="sect2"><a href="hstore.html#id-1.11.7.27.12">F.18.8. Authors</a></span></dt></dl></div><a id="id-1.11.7.27.2" class="indexterm"></a><p> + This module implements the <code class="type">hstore</code> data type for storing sets of + key/value pairs within a single <span class="productname">PostgreSQL</span> 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. + </p><p> + This module is considered <span class="quote">“<span class="quote">trusted</span>”</span>, that is, it can be + installed by non-superusers who have <code class="literal">CREATE</code> privilege + on the current database. + </p><div class="sect2" id="id-1.11.7.27.5"><div class="titlepage"><div><div><h3 class="title">F.18.1. <code class="type">hstore</code> External Representation</h3></div></div></div><p> + + The text representation of an <code class="type">hstore</code>, used for input and output, + includes zero or more <em class="replaceable"><code>key</code></em> <code class="literal">=></code> + <em class="replaceable"><code>value</code></em> pairs separated by commas. Some examples: + +</p><pre class="synopsis"> +k => v +foo => bar, baz => whatever +"1-a" => "anything at all" +</pre><p> + + The order of the pairs is not significant (and may not be reproduced on + output). Whitespace between pairs or around the <code class="literal">=></code> sign is + ignored. Double-quote keys and values that include whitespace, commas, + <code class="literal">=</code>s or <code class="literal">></code>s. To include a double quote or a + backslash in a key or value, escape it with a backslash. + </p><p> + Each key in an <code class="type">hstore</code> is unique. If you declare an <code class="type">hstore</code> + with duplicate keys, only one will be stored in the <code class="type">hstore</code> and + there is no guarantee as to which will be kept: + +</p><pre class="programlisting"> +SELECT 'a=>1,a=>2'::hstore; + hstore +---------- + "a"=>"1" +</pre><p> + </p><p> + A value (but not a key) can be an SQL <code class="literal">NULL</code>. For example: + +</p><pre class="programlisting"> +key => NULL +</pre><p> + + The <code class="literal">NULL</code> keyword is case-insensitive. Double-quote the + <code class="literal">NULL</code> to treat it as the ordinary string <span class="quote">“<span class="quote">NULL</span>”</span>. + </p><div class="note"><h3 class="title">Note</h3><p> + Keep in mind that the <code class="type">hstore</code> text format, when used for input, + applies <span class="emphasis"><em>before</em></span> any required quoting or escaping. If you are + passing an <code class="type">hstore</code> 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 <code class="varname">standard_conforming_strings</code> configuration parameter) + backslash characters need to be escaped correctly. See + <a class="xref" href="sql-syntax-lexical.html#SQL-SYNTAX-STRINGS" title="4.1.2.1. String Constants">Section 4.1.2.1</a> for more on the handling of string + constants. + </p></div><p> + On output, double quotes always surround keys and values, even when it's + not strictly necessary. + </p></div><div class="sect2" id="id-1.11.7.27.6"><div class="titlepage"><div><div><h3 class="title">F.18.2. <code class="type">hstore</code> Operators and Functions</h3></div></div></div><p> + The operators provided by the <code class="literal">hstore</code> module are + shown in <a class="xref" href="hstore.html#HSTORE-OP-TABLE" title="Table F.7. hstore Operators">Table F.7</a>, the functions + in <a class="xref" href="hstore.html#HSTORE-FUNC-TABLE" title="Table F.8. hstore Functions">Table F.8</a>. + </p><div class="table" id="HSTORE-OP-TABLE"><p class="title"><strong>Table F.7. <code class="type">hstore</code> Operators</strong></p><div class="table-contents"><table class="table" summary="hstore Operators" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature"> + Operator + </p> + <p> + Description + </p> + <p> + Example(s) + </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature"> + <code class="type">hstore</code> <code class="literal">-></code> <code class="type">text</code> + → <code class="returnvalue">text</code> + </p> + <p> + Returns value associated with given key, or <code class="literal">NULL</code> if + not present. + </p> + <p> + <code class="literal">'a=>x, b=>y'::hstore -> 'a'</code> + → <code class="returnvalue">x</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="type">hstore</code> <code class="literal">-></code> <code class="type">text[]</code> + → <code class="returnvalue">text[]</code> + </p> + <p> + Returns values associated with given keys, or <code class="literal">NULL</code> + if not present. + </p> + <p> + <code class="literal">'a=>x, b=>y, c=>z'::hstore -> ARRAY['c','a']</code> + → <code class="returnvalue">{"z","x"}</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="type">hstore</code> <code class="literal">||</code> <code class="type">hstore</code> + → <code class="returnvalue">hstore</code> + </p> + <p> + Concatenates two <code class="type">hstore</code>s. + </p> + <p> + <code class="literal">'a=>b, c=>d'::hstore || 'c=>x, d=>q'::hstore</code> + → <code class="returnvalue">"a"=>"b", "c"=>"x", "d"=>"q"</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="type">hstore</code> <code class="literal">?</code> <code class="type">text</code> + → <code class="returnvalue">boolean</code> + </p> + <p> + Does <code class="type">hstore</code> contain key? + </p> + <p> + <code class="literal">'a=>1'::hstore ? 'a'</code> + → <code class="returnvalue">t</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="type">hstore</code> <code class="literal">?&</code> <code class="type">text[]</code> + → <code class="returnvalue">boolean</code> + </p> + <p> + Does <code class="type">hstore</code> contain all the specified keys? + </p> + <p> + <code class="literal">'a=>1,b=>2'::hstore ?& ARRAY['a','b']</code> + → <code class="returnvalue">t</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="type">hstore</code> <code class="literal">?|</code> <code class="type">text[]</code> + → <code class="returnvalue">boolean</code> + </p> + <p> + Does <code class="type">hstore</code> contain any of the specified keys? + </p> + <p> + <code class="literal">'a=>1,b=>2'::hstore ?| ARRAY['b','c']</code> + → <code class="returnvalue">t</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="type">hstore</code> <code class="literal">@></code> <code class="type">hstore</code> + → <code class="returnvalue">boolean</code> + </p> + <p> + Does left operand contain right? + </p> + <p> + <code class="literal">'a=>b, b=>1, c=>NULL'::hstore @> 'b=>1'</code> + → <code class="returnvalue">t</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="type">hstore</code> <code class="literal"><@</code> <code class="type">hstore</code> + → <code class="returnvalue">boolean</code> + </p> + <p> + Is left operand contained in right? + </p> + <p> + <code class="literal">'a=>c'::hstore <@ 'a=>b, b=>1, c=>NULL'</code> + → <code class="returnvalue">f</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="type">hstore</code> <code class="literal">-</code> <code class="type">text</code> + → <code class="returnvalue">hstore</code> + </p> + <p> + Deletes key from left operand. + </p> + <p> + <code class="literal">'a=>1, b=>2, c=>3'::hstore - 'b'::text</code> + → <code class="returnvalue">"a"=>"1", "c"=>"3"</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="type">hstore</code> <code class="literal">-</code> <code class="type">text[]</code> + → <code class="returnvalue">hstore</code> + </p> + <p> + Deletes keys from left operand. + </p> + <p> + <code class="literal">'a=>1, b=>2, c=>3'::hstore - ARRAY['a','b']</code> + → <code class="returnvalue">"c"=>"3"</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="type">hstore</code> <code class="literal">-</code> <code class="type">hstore</code> + → <code class="returnvalue">hstore</code> + </p> + <p> + Deletes pairs from left operand that match pairs in the right operand. + </p> + <p> + <code class="literal">'a=>1, b=>2, c=>3'::hstore - 'a=>4, b=>2'::hstore</code> + → <code class="returnvalue">"a"=>"1", "c"=>"3"</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="type">anyelement</code> <code class="literal">#=</code> <code class="type">hstore</code> + → <code class="returnvalue">anyelement</code> + </p> + <p> + Replaces fields in the left operand (which must be a composite type) + with matching values from <code class="type">hstore</code>. + </p> + <p> + <code class="literal">ROW(1,3) #= 'f1=>11'::hstore</code> + → <code class="returnvalue">(11,3)</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="literal">%%</code> <code class="type">hstore</code> + → <code class="returnvalue">text[]</code> + </p> + <p> + Converts <code class="type">hstore</code> to an array of alternating keys and + values. + </p> + <p> + <code class="literal">%% 'a=>foo, b=>bar'::hstore</code> + → <code class="returnvalue">{a,foo,b,bar}</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="literal">%#</code> <code class="type">hstore</code> + → <code class="returnvalue">text[]</code> + </p> + <p> + Converts <code class="type">hstore</code> to a two-dimensional key/value array. + </p> + <p> + <code class="literal">%# 'a=>foo, b=>bar'::hstore</code> + → <code class="returnvalue">{{a,foo},{b,bar}}</code> + </p></td></tr></tbody></table></div></div><br class="table-break" /><div class="table" id="HSTORE-FUNC-TABLE"><p class="title"><strong>Table F.8. <code class="type">hstore</code> Functions</strong></p><div class="table-contents"><table class="table" summary="hstore Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature"> + Function + </p> + <p> + Description + </p> + <p> + Example(s) + </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.11.7.27.6.4.2.2.1.1.1.1" class="indexterm"></a> + <code class="function">hstore</code> ( <code class="type">record</code> ) + → <code class="returnvalue">hstore</code> + </p> + <p> + Constructs an <code class="type">hstore</code> from a record or row. + </p> + <p> + <code class="literal">hstore(ROW(1,2))</code> + → <code class="returnvalue">"f1"=>"1", "f2"=>"2"</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="function">hstore</code> ( <code class="type">text[]</code> ) + → <code class="returnvalue">hstore</code> + </p> + <p> + Constructs an <code class="type">hstore</code> from an array, which may be either + a key/value array, or a two-dimensional array. + </p> + <p> + <code class="literal">hstore(ARRAY['a','1','b','2'])</code> + → <code class="returnvalue">"a"=>"1", "b"=>"2"</code> + </p> + <p> + <code class="literal">hstore(ARRAY[['c','3'],['d','4']])</code> + → <code class="returnvalue">"c"=>"3", "d"=>"4"</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="function">hstore</code> ( <code class="type">text[]</code>, <code class="type">text[]</code> ) + → <code class="returnvalue">hstore</code> + </p> + <p> + Constructs an <code class="type">hstore</code> from separate key and value arrays. + </p> + <p> + <code class="literal">hstore(ARRAY['a','b'], ARRAY['1','2'])</code> + → <code class="returnvalue">"a"=>"1", "b"=>"2"</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="function">hstore</code> ( <code class="type">text</code>, <code class="type">text</code> ) + → <code class="returnvalue">hstore</code> + </p> + <p> + Makes a single-item <code class="type">hstore</code>. + </p> + <p> + <code class="literal">hstore('a', 'b')</code> + → <code class="returnvalue">"a"=>"b"</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.11.7.27.6.4.2.2.5.1.1.1" class="indexterm"></a> + <code class="function">akeys</code> ( <code class="type">hstore</code> ) + → <code class="returnvalue">text[]</code> + </p> + <p> + Extracts an <code class="type">hstore</code>'s keys as an array. + </p> + <p> + <code class="literal">akeys('a=>1,b=>2')</code> + → <code class="returnvalue">{a,b}</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.11.7.27.6.4.2.2.6.1.1.1" class="indexterm"></a> + <code class="function">skeys</code> ( <code class="type">hstore</code> ) + → <code class="returnvalue">setof text</code> + </p> + <p> + Extracts an <code class="type">hstore</code>'s keys as a set. + </p> + <p> + <code class="literal">skeys('a=>1,b=>2')</code> + → <code class="returnvalue"></code> +</p><pre class="programlisting"> +a +b +</pre><p> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.11.7.27.6.4.2.2.7.1.1.1" class="indexterm"></a> + <code class="function">avals</code> ( <code class="type">hstore</code> ) + → <code class="returnvalue">text[]</code> + </p> + <p> + Extracts an <code class="type">hstore</code>'s values as an array. + </p> + <p> + <code class="literal">avals('a=>1,b=>2')</code> + → <code class="returnvalue">{1,2}</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.11.7.27.6.4.2.2.8.1.1.1" class="indexterm"></a> + <code class="function">svals</code> ( <code class="type">hstore</code> ) + → <code class="returnvalue">setof text</code> + </p> + <p> + Extracts an <code class="type">hstore</code>'s values as a set. + </p> + <p> + <code class="literal">svals('a=>1,b=>2')</code> + → <code class="returnvalue"></code> +</p><pre class="programlisting"> +1 +2 +</pre><p> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.11.7.27.6.4.2.2.9.1.1.1" class="indexterm"></a> + <code class="function">hstore_to_array</code> ( <code class="type">hstore</code> ) + → <code class="returnvalue">text[]</code> + </p> + <p> + Extracts an <code class="type">hstore</code>'s keys and values as an array of + alternating keys and values. + </p> + <p> + <code class="literal">hstore_to_array('a=>1,b=>2')</code> + → <code class="returnvalue">{a,1,b,2}</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.11.7.27.6.4.2.2.10.1.1.1" class="indexterm"></a> + <code class="function">hstore_to_matrix</code> ( <code class="type">hstore</code> ) + → <code class="returnvalue">text[]</code> + </p> + <p> + Extracts an <code class="type">hstore</code>'s keys and values as a two-dimensional + array. + </p> + <p> + <code class="literal">hstore_to_matrix('a=>1,b=>2')</code> + → <code class="returnvalue">{{a,1},{b,2}}</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.11.7.27.6.4.2.2.11.1.1.1" class="indexterm"></a> + <code class="function">hstore_to_json</code> ( <code class="type">hstore</code> ) + → <code class="returnvalue">json</code> + </p> + <p> + Converts an <code class="type">hstore</code> to a <code class="type">json</code> value, + converting all non-null values to JSON strings. + </p> + <p> + This function is used implicitly when an <code class="type">hstore</code> value is + cast to <code class="type">json</code>. + </p> + <p> + <code class="literal">hstore_to_json('"a key"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>2.345e+4')</code> + → <code class="returnvalue">{"a key": "1", "b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4"}</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.11.7.27.6.4.2.2.12.1.1.1" class="indexterm"></a> + <code class="function">hstore_to_jsonb</code> ( <code class="type">hstore</code> ) + → <code class="returnvalue">jsonb</code> + </p> + <p> + Converts an <code class="type">hstore</code> to a <code class="type">jsonb</code> value, + converting all non-null values to JSON strings. + </p> + <p> + This function is used implicitly when an <code class="type">hstore</code> value is + cast to <code class="type">jsonb</code>. + </p> + <p> + <code class="literal">hstore_to_jsonb('"a key"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>2.345e+4')</code> + → <code class="returnvalue">{"a key": "1", "b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4"}</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.11.7.27.6.4.2.2.13.1.1.1" class="indexterm"></a> + <code class="function">hstore_to_json_loose</code> ( <code class="type">hstore</code> ) + → <code class="returnvalue">json</code> + </p> + <p> + Converts an <code class="type">hstore</code> to a <code class="type">json</code> value, but + attempts to distinguish numerical and Boolean values so they are + unquoted in the JSON. + </p> + <p> + <code class="literal">hstore_to_json_loose('"a key"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>2.345e+4')</code> + → <code class="returnvalue">{"a key": 1, "b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4}</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.11.7.27.6.4.2.2.14.1.1.1" class="indexterm"></a> + <code class="function">hstore_to_jsonb_loose</code> ( <code class="type">hstore</code> ) + → <code class="returnvalue">jsonb</code> + </p> + <p> + Converts an <code class="type">hstore</code> to a <code class="type">jsonb</code> value, but + attempts to distinguish numerical and Boolean values so they are + unquoted in the JSON. + </p> + <p> + <code class="literal">hstore_to_jsonb_loose('"a key"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>2.345e+4')</code> + → <code class="returnvalue">{"a key": 1, "b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4}</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.11.7.27.6.4.2.2.15.1.1.1" class="indexterm"></a> + <code class="function">slice</code> ( <code class="type">hstore</code>, <code class="type">text[]</code> ) + → <code class="returnvalue">hstore</code> + </p> + <p> + Extracts a subset of an <code class="type">hstore</code> containing only the + specified keys. + </p> + <p> + <code class="literal">slice('a=>1,b=>2,c=>3'::hstore, ARRAY['b','c','x'])</code> + → <code class="returnvalue">"b"=>"2", "c"=>"3"</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.11.7.27.6.4.2.2.16.1.1.1" class="indexterm"></a> + <code class="function">each</code> ( <code class="type">hstore</code> ) + → <code class="returnvalue">setof record</code> + ( <em class="parameter"><code>key</code></em> <code class="type">text</code>, + <em class="parameter"><code>value</code></em> <code class="type">text</code> ) + </p> + <p> + Extracts an <code class="type">hstore</code>'s keys and values as a set of records. + </p> + <p> + <code class="literal">select * from each('a=>1,b=>2')</code> + → <code class="returnvalue"></code> +</p><pre class="programlisting"> + key | value +-----+------- + a | 1 + b | 2 +</pre><p> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.11.7.27.6.4.2.2.17.1.1.1" class="indexterm"></a> + <code class="function">exist</code> ( <code class="type">hstore</code>, <code class="type">text</code> ) + → <code class="returnvalue">boolean</code> + </p> + <p> + Does <code class="type">hstore</code> contain key? + </p> + <p> + <code class="literal">exist('a=>1', 'a')</code> + → <code class="returnvalue">t</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.11.7.27.6.4.2.2.18.1.1.1" class="indexterm"></a> + <code class="function">defined</code> ( <code class="type">hstore</code>, <code class="type">text</code> ) + → <code class="returnvalue">boolean</code> + </p> + <p> + Does <code class="type">hstore</code> contain a non-<code class="literal">NULL</code> value + for key? + </p> + <p> + <code class="literal">defined('a=>NULL', 'a')</code> + → <code class="returnvalue">f</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.11.7.27.6.4.2.2.19.1.1.1" class="indexterm"></a> + <code class="function">delete</code> ( <code class="type">hstore</code>, <code class="type">text</code> ) + → <code class="returnvalue">hstore</code> + </p> + <p> + Deletes pair with matching key. + </p> + <p> + <code class="literal">delete('a=>1,b=>2', 'b')</code> + → <code class="returnvalue">"a"=>"1"</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="function">delete</code> ( <code class="type">hstore</code>, <code class="type">text[]</code> ) + → <code class="returnvalue">hstore</code> + </p> + <p> + Deletes pairs with matching keys. + </p> + <p> + <code class="literal">delete('a=>1,b=>2,c=>3', ARRAY['a','b'])</code> + → <code class="returnvalue">"c"=>"3"</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="function">delete</code> ( <code class="type">hstore</code>, <code class="type">hstore</code> ) + → <code class="returnvalue">hstore</code> + </p> + <p> + Deletes pairs matching those in the second argument. + </p> + <p> + <code class="literal">delete('a=>1,b=>2', 'a=>4,b=>2'::hstore)</code> + → <code class="returnvalue">"a"=>"1"</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.11.7.27.6.4.2.2.22.1.1.1" class="indexterm"></a> + <code class="function">populate_record</code> ( <code class="type">anyelement</code>, <code class="type">hstore</code> ) + → <code class="returnvalue">anyelement</code> + </p> + <p> + Replaces fields in the left operand (which must be a composite type) + with matching values from <code class="type">hstore</code>. + </p> + <p> + <code class="literal">populate_record(ROW(1,2), 'f1=>42'::hstore)</code> + → <code class="returnvalue">(42,2)</code> + </p></td></tr></tbody></table></div></div><br class="table-break" /><p> + In addition to these operators and functions, values of + the <code class="type">hstore</code> type can be subscripted, allowing them to act + like associative arrays. Only a single subscript of type <code class="type">text</code> + can be specified; it is interpreted as a key and the corresponding + value is fetched or stored. For example, + +</p><pre class="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) +</pre><p> + + A subscripted fetch returns <code class="literal">NULL</code> if the subscript + is <code class="literal">NULL</code> or that key does not exist in + the <code class="type">hstore</code>. (Thus, a subscripted fetch is not greatly + different from the <code class="literal">-></code> operator.) + A subscripted update fails if the subscript is <code class="literal">NULL</code>; + otherwise, it replaces the value for that key, adding an entry to + the <code class="type">hstore</code> if the key does not already exist. + </p></div><div class="sect2" id="id-1.11.7.27.7"><div class="titlepage"><div><div><h3 class="title">F.18.3. Indexes</h3></div></div></div><p> + <code class="type">hstore</code> has GiST and GIN index support for the <code class="literal">@></code>, + <code class="literal">?</code>, <code class="literal">?&</code> and <code class="literal">?|</code> operators. For example: + </p><pre class="programlisting"> +CREATE INDEX hidx ON testhstore USING GIST (h); + +CREATE INDEX hidx ON testhstore USING GIN (h); +</pre><p> + <code class="literal">gist_hstore_ops</code> GiST opclass approximates a set of + key/value pairs as a bitmap signature. Its optional integer parameter + <code class="literal">siglen</code> 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. + </p><p> + Example of creating such an index with a signature length of 32 bytes: +</p><pre class="programlisting"> +CREATE INDEX hidx ON testhstore USING GIST (h gist_hstore_ops(siglen=32)); +</pre><p> + </p><p> + <code class="type">hstore</code> also supports <code class="type">btree</code> or <code class="type">hash</code> indexes for + the <code class="literal">=</code> operator. This allows <code class="type">hstore</code> columns to be + declared <code class="literal">UNIQUE</code>, or to be used in <code class="literal">GROUP BY</code>, + <code class="literal">ORDER BY</code> or <code class="literal">DISTINCT</code> expressions. The sort ordering + for <code class="type">hstore</code> values is not particularly useful, but these indexes + may be useful for equivalence lookups. Create indexes for <code class="literal">=</code> + comparisons as follows: + </p><pre class="programlisting"> +CREATE INDEX hidx ON testhstore USING BTREE (h); + +CREATE INDEX hidx ON testhstore USING HASH (h); +</pre></div><div class="sect2" id="id-1.11.7.27.8"><div class="titlepage"><div><div><h3 class="title">F.18.4. Examples</h3></div></div></div><p> + Add a key, or update an existing key with a new value: +</p><pre class="programlisting"> +UPDATE tab SET h['c'] = '3'; +</pre><p> + Another way to do the same thing is: +</p><pre class="programlisting"> +UPDATE tab SET h = h || hstore('c', '3'); +</pre><p> + If multiple keys are to be added or changed in one operation, + the concatenation approach is more efficient than subscripting: +</p><pre class="programlisting"> +UPDATE tab SET h = h || hstore(array['q', 'w'], array['11', '12']); +</pre><p> + </p><p> + Delete a key: +</p><pre class="programlisting"> +UPDATE tab SET h = delete(h, 'k1'); +</pre><p> + </p><p> + Convert a <code class="type">record</code> to an <code class="type">hstore</code>: +</p><pre class="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) +</pre><p> + </p><p> + Convert an <code class="type">hstore</code> to a predefined <code class="type">record</code> type: +</p><pre class="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) +</pre><p> + </p><p> + Modify an existing record using the values from an <code class="type">hstore</code>: +</p><pre class="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) +</pre><p> + </p></div><div class="sect2" id="id-1.11.7.27.9"><div class="titlepage"><div><div><h3 class="title">F.18.5. Statistics</h3></div></div></div><p> + The <code class="type">hstore</code> 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. + </p><p> + Simple example: +</p><pre class="programlisting"> +SELECT * FROM each('aaa=>bq, b=>NULL, ""=>1'); +</pre><p> + </p><p> + Using a table: +</p><pre class="programlisting"> +CREATE TABLE stat AS SELECT (each(h)).key, (each(h)).value FROM testhstore; +</pre><p> + </p><p> + Online statistics: +</p><pre class="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 +................... +</pre><p> + </p></div><div class="sect2" id="id-1.11.7.27.10"><div class="titlepage"><div><div><h3 class="title">F.18.6. Compatibility</h3></div></div></div><p> + As of PostgreSQL 9.0, <code class="type">hstore</code> 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. + </p><p> + 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 <code class="literal">UPDATE</code> statement as follows: +</p><pre class="programlisting"> +UPDATE tablename SET hstorecol = hstorecol || ''; +</pre><p> + </p><p> + Another way to do it is: +</p><pre class="programlisting"> +ALTER TABLE tablename ALTER hstorecol TYPE hstore USING hstorecol || ''; +</pre><p> + The <code class="command">ALTER TABLE</code> method requires an + <code class="literal">ACCESS EXCLUSIVE</code> lock on the table, + but does not result in bloating the table with old row versions. + </p></div><div class="sect2" id="id-1.11.7.27.11"><div class="titlepage"><div><div><h3 class="title">F.18.7. Transforms</h3></div></div></div><p> + Additional extensions are available that implement transforms for + the <code class="type">hstore</code> type for the languages PL/Perl and PL/Python. The + extensions for PL/Perl are called <code class="literal">hstore_plperl</code> + and <code class="literal">hstore_plperlu</code>, for trusted and untrusted PL/Perl. + If you install these transforms and specify them when creating a + function, <code class="type">hstore</code> values are mapped to Perl hashes. The + extension for PL/Python is called <code class="literal">hstore_plpython3u</code>. + If you use it, <code class="type">hstore</code> values are mapped to Python dictionaries. + </p><div class="caution"><h3 class="title">Caution</h3><p> + It is strongly recommended that the transform extensions be installed in + the same schema as <code class="filename">hstore</code>. Otherwise there are + installation-time security hazards if a transform extension's schema + contains objects defined by a hostile user. + </p></div></div><div class="sect2" id="id-1.11.7.27.12"><div class="titlepage"><div><div><h3 class="title">F.18.8. Authors</h3></div></div></div><p> + Oleg Bartunov <code class="email"><<a class="email" href="mailto:oleg@sai.msu.su">oleg@sai.msu.su</a>></code>, Moscow, Moscow University, Russia + </p><p> + Teodor Sigaev <code class="email"><<a class="email" href="mailto:teodor@sigaev.ru">teodor@sigaev.ru</a>></code>, Moscow, Delta-Soft Ltd., Russia + </p><p> + Additional enhancements by Andrew Gierth <code class="email"><<a class="email" href="mailto:andrew@tao11.riddles.org.uk">andrew@tao11.riddles.org.uk</a>></code>, + United Kingdom + </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="fuzzystrmatch.html" title="F.17. fuzzystrmatch">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="intagg.html" title="F.19. intagg">Next</a></td></tr><tr><td width="40%" align="left" valign="top">F.17. fuzzystrmatch </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> F.19. intagg</td></tr></table></div></body></html>
\ No newline at end of file |