summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/html/hstore.html
diff options
context:
space:
mode:
Diffstat (limited to 'doc/src/sgml/html/hstore.html')
-rw-r--r--doc/src/sgml/html/hstore.html669
1 files changed, 669 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..44cd1ae
--- /dev/null
+++ b/doc/src/sgml/html/hstore.html
@@ -0,0 +1,669 @@
+<?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.16. 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 V1.79.1" /><link rel="prev" href="fuzzystrmatch.html" title="F.15. fuzzystrmatch" /><link rel="next" href="intagg.html" title="F.17. intagg" /></head><body id="docContent" class="container-fluid col-10"><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">F.16. hstore</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="fuzzystrmatch.html" title="F.15. 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 13.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="intagg.html" title="F.17. intagg">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="HSTORE"><div class="titlepage"><div><div><h2 class="title" style="clear: both">F.16. hstore</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="hstore.html#id-1.11.7.25.5">F.16.1. <code class="type">hstore</code> External Representation</a></span></dt><dt><span class="sect2"><a href="hstore.html#id-1.11.7.25.6">F.16.2. <code class="type">hstore</code> Operators and Functions</a></span></dt><dt><span class="sect2"><a href="hstore.html#id-1.11.7.25.7">F.16.3. Indexes</a></span></dt><dt><span class="sect2"><a href="hstore.html#id-1.11.7.25.8">F.16.4. Examples</a></span></dt><dt><span class="sect2"><a href="hstore.html#id-1.11.7.25.9">F.16.5. Statistics</a></span></dt><dt><span class="sect2"><a href="hstore.html#id-1.11.7.25.10">F.16.6. Compatibility</a></span></dt><dt><span class="sect2"><a href="hstore.html#id-1.11.7.25.11">F.16.7. Transforms</a></span></dt><dt><span class="sect2"><a href="hstore.html#id-1.11.7.25.12">F.16.8. Authors</a></span></dt></dl></div><a id="id-1.11.7.25.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.25.5"><div class="titlepage"><div><div><h3 class="title">F.16.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">=&gt;</code>
+ <em class="replaceable"><code>value</code></em> pairs separated by commas. Some examples:
+
+</p><pre class="synopsis">
+k =&gt; v
+foo =&gt; bar, baz =&gt; whatever
+"1-a" =&gt; "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">=&gt;</code> sign is
+ ignored. Double-quote keys and values that include whitespace, commas,
+ <code class="literal">=</code>s or <code class="literal">&gt;</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=&gt;1,a=&gt;2'::hstore;
+ hstore
+----------
+ "a"=&gt;"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 =&gt; 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.25.6"><div class="titlepage"><div><div><h3 class="title">F.16.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">-&gt;</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=&gt;x, b=&gt;y'::hstore -&gt; '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">-&gt;</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=&gt;x, b=&gt;y, c=&gt;z'::hstore -&gt; 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=&gt;b, c=&gt;d'::hstore || 'c=&gt;x, d=&gt;q'::hstore</code>
+ → <code class="returnvalue">"a"=&gt;"b", "c"=&gt;"x", "d"=&gt;"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=&gt;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">?&amp;</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=&gt;1,b=&gt;2'::hstore ?&amp; 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=&gt;1,b=&gt;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">@&gt;</code> <code class="type">hstore</code>
+ → <code class="returnvalue">boolean</code>
+ </p>
+ <p>
+ Does left operand contain right?
+ </p>
+ <p>
+ <code class="literal">'a=&gt;b, b=&gt;1, c=&gt;NULL'::hstore @&gt; 'b=&gt;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">&lt;@</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=&gt;c'::hstore &lt;@ 'a=&gt;b, b=&gt;1, c=&gt;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=&gt;1, b=&gt;2, c=&gt;3'::hstore - 'b'::text</code>
+ → <code class="returnvalue">"a"=&gt;"1", "c"=&gt;"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=&gt;1, b=&gt;2, c=&gt;3'::hstore - ARRAY['a','b']</code>
+ → <code class="returnvalue">"c"=&gt;"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=&gt;1, b=&gt;2, c=&gt;3'::hstore - 'a=&gt;4, b=&gt;2'::hstore</code>
+ → <code class="returnvalue">"a"=&gt;"1", "c"=&gt;"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=&gt;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=&gt;foo, b=&gt;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=&gt;foo, b=&gt;bar'::hstore</code>
+ → <code class="returnvalue">{{a,foo},{b,bar}}</code>
+ </p></td></tr></tbody></table></div></div><br class="table-break" /><div class="note"><h3 class="title">Note</h3><p>
+ Prior to PostgreSQL 8.2, the containment operators <code class="literal">@&gt;</code>
+ and <code class="literal">&lt;@</code> were called <code class="literal">@</code> and <code class="literal">~</code>,
+ respectively. These names are still available, but are deprecated and will
+ eventually be removed. Notice that the old names are reversed from the
+ convention formerly followed by the core geometric data types!
+ </p></div><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.25.6.5.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"=&gt;"1", "f2"=&gt;"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"=&gt;"1", "b"=&gt;"2"</code>
+ </p>
+ <p>
+ <code class="literal">hstore(ARRAY[['c','3'],['d','4']])</code>
+ → <code class="returnvalue">"c"=&gt;"3", "d"=&gt;"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"=&gt;"1", "b"=&gt;"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"=&gt;"b"</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <a id="id-1.11.7.25.6.5.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=&gt;1,b=&gt;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.25.6.5.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=&gt;1,b=&gt;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.25.6.5.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=&gt;1,b=&gt;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.25.6.5.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=&gt;1,b=&gt;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.25.6.5.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=&gt;1,b=&gt;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.25.6.5.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=&gt;1,b=&gt;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.25.6.5.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"=&gt;1, b=&gt;t, c=&gt;null, d=&gt;12345, e=&gt;012345, f=&gt;1.234, g=&gt;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.25.6.5.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"=&gt;1, b=&gt;t, c=&gt;null, d=&gt;12345, e=&gt;012345, f=&gt;1.234, g=&gt;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.25.6.5.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"=&gt;1, b=&gt;t, c=&gt;null, d=&gt;12345, e=&gt;012345, f=&gt;1.234, g=&gt;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.25.6.5.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"=&gt;1, b=&gt;t, c=&gt;null, d=&gt;12345, e=&gt;012345, f=&gt;1.234, g=&gt;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.25.6.5.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=&gt;1,b=&gt;2,c=&gt;3'::hstore, ARRAY['b','c','x'])</code>
+ → <code class="returnvalue">"b"=&gt;"2", "c"=&gt;"3"</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <a id="id-1.11.7.25.6.5.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=&gt;1,b=&gt;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.25.6.5.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=&gt;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.25.6.5.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=&gt;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.25.6.5.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=&gt;1,b=&gt;2', 'b')</code>
+ → <code class="returnvalue">"a"=&gt;"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=&gt;1,b=&gt;2,c=&gt;3', ARRAY['a','b'])</code>
+ → <code class="returnvalue">"c"=&gt;"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=&gt;1,b=&gt;2', 'a=&gt;4,b=&gt;2'::hstore)</code>
+ → <code class="returnvalue">"a"=&gt;"1"</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <a id="id-1.11.7.25.6.5.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=&gt;42'::hstore)</code>
+ → <code class="returnvalue">(42,2)</code>
+ </p></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="id-1.11.7.25.7"><div class="titlepage"><div><div><h3 class="title">F.16.3. Indexes</h3></div></div></div><p>
+ <code class="type">hstore</code> has GiST and GIN index support for the <code class="literal">@&gt;</code>,
+ <code class="literal">?</code>, <code class="literal">?&amp;</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.25.8"><div class="titlepage"><div><div><h3 class="title">F.16.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 = h || hstore('c', '3');
+</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"=&gt;"123", "col2"=&gt;"foo", "col3"=&gt;"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"=&gt;"456", "col2"=&gt;"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"=&gt;"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.25.9"><div class="titlepage"><div><div><h3 class="title">F.16.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=&gt;bq, b=&gt;NULL, ""=&gt;1');
+</pre><p>
+ </p><p>
+ Using a table:
+</p><pre class="programlisting">
+SELECT (each(h)).key, (each(h)).value INTO stat 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.25.10"><div class="titlepage"><div><div><h3 class="title">F.16.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.25.11"><div class="titlepage"><div><div><h3 class="title">F.16.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
+ extensions for PL/Python are
+ called <code class="literal">hstore_plpythonu</code>, <code class="literal">hstore_plpython2u</code>,
+ and <code class="literal">hstore_plpython3u</code>
+ (see <a class="xref" href="plpython-python23.html" title="45.1. Python 2 vs. Python 3">Section 45.1</a> for the PL/Python naming
+ convention). If you use them, <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.25.12"><div class="titlepage"><div><div><h3 class="title">F.16.8. Authors</h3></div></div></div><p>
+ Oleg Bartunov <code class="email">&lt;<a class="email" href="mailto:oleg@sai.msu.su">oleg@sai.msu.su</a>&gt;</code>, Moscow, Moscow University, Russia
+ </p><p>
+ Teodor Sigaev <code class="email">&lt;<a class="email" href="mailto:teodor@sigaev.ru">teodor@sigaev.ru</a>&gt;</code>, Moscow, Delta-Soft Ltd., Russia
+ </p><p>
+ Additional enhancements by Andrew Gierth <code class="email">&lt;<a class="email" href="mailto:andrew@tao11.riddles.org.uk">andrew@tao11.riddles.org.uk</a>&gt;</code>,
+ United Kingdom
+ </p></div></div><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="fuzzystrmatch.html" title="F.15. 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.17. intagg">Next</a></td></tr><tr><td width="40%" align="left" valign="top">F.15. fuzzystrmatch </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 13.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> F.17. intagg</td></tr></table></div></body></html> \ No newline at end of file