<?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 Vsnapshot" /><link rel="prev" href="fuzzystrmatch.html" title="F.15. fuzzystrmatch" /><link rel="next" href="intagg.html" title="F.17. intagg" /></head><body><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 11.5 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.4">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.5">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.6">F.16.3. Indexes</a></span></dt><dt><span class="sect2"><a href="hstore.html#id-1.11.7.25.7">F.16.4. Examples</a></span></dt><dt><span class="sect2"><a href="hstore.html#id-1.11.7.25.8">F.16.5. Statistics</a></span></dt><dt><span class="sect2"><a href="hstore.html#id-1.11.7.25.9">F.16.6. Compatibility</a></span></dt><dt><span class="sect2"><a href="hstore.html#id-1.11.7.25.10">F.16.7. Transforms</a></span></dt><dt><span class="sect2"><a href="hstore.html#id-1.11.7.25.11">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><div class="sect2" id="id-1.11.7.25.4"><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">=></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.25.5"><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 /><col /><col /><col /></colgroup><thead><tr><th>Operator</th><th>Description</th><th>Example</th><th>Result</th></tr></thead><tbody><tr><td><code class="type">hstore</code> <code class="literal">-></code> <code class="type">text</code></td><td>get value for key (<code class="literal">NULL</code> if not present)</td><td><code class="literal">'a=>x, b=>y'::hstore -> 'a'</code></td><td><code class="literal">x</code></td></tr><tr><td><code class="type">hstore</code> <code class="literal">-></code> <code class="type">text[]</code></td><td>get values for keys (<code class="literal">NULL</code> if not present)</td><td><code class="literal">'a=>x, b=>y, c=>z'::hstore -> ARRAY['c','a']</code></td><td><code class="literal">{"z","x"}</code></td></tr><tr><td><code class="type">hstore</code> <code class="literal">||</code> <code class="type">hstore</code></td><td>concatenate <code class="type">hstore</code>s</td><td><code class="literal">'a=>b, c=>d'::hstore || 'c=>x, d=>q'::hstore</code></td><td><code class="literal">"a"=>"b", "c"=>"x", "d"=>"q"</code></td></tr><tr><td><code class="type">hstore</code> <code class="literal">?</code> <code class="type">text</code></td><td>does <code class="type">hstore</code> contain key?</td><td><code class="literal">'a=>1'::hstore ? 'a'</code></td><td><code class="literal">t</code></td></tr><tr><td><code class="type">hstore</code> <code class="literal">?&</code> <code class="type">text[]</code></td><td>does <code class="type">hstore</code> contain all specified keys?</td><td><code class="literal">'a=>1,b=>2'::hstore ?& ARRAY['a','b']</code></td><td><code class="literal">t</code></td></tr><tr><td><code class="type">hstore</code> <code class="literal">?|</code> <code class="type">text[]</code></td><td>does <code class="type">hstore</code> contain any of the specified keys?</td><td><code class="literal">'a=>1,b=>2'::hstore ?| ARRAY['b','c']</code></td><td><code class="literal">t</code></td></tr><tr><td><code class="type">hstore</code> <code class="literal">@></code> <code class="type">hstore</code></td><td>does left operand contain right?</td><td><code class="literal">'a=>b, b=>1, c=>NULL'::hstore @> 'b=>1'</code></td><td><code class="literal">t</code></td></tr><tr><td><code class="type">hstore</code> <code class="literal"><@</code> <code class="type">hstore</code></td><td>is left operand contained in right?</td><td><code class="literal">'a=>c'::hstore <@ 'a=>b, b=>1, c=>NULL'</code></td><td><code class="literal">f</code></td></tr><tr><td><code class="type">hstore</code> <code class="literal">-</code> <code class="type">text</code></td><td>delete key from left operand</td><td><code class="literal">'a=>1, b=>2, c=>3'::hstore - 'b'::text</code></td><td><code class="literal">"a"=>"1", "c"=>"3"</code></td></tr><tr><td><code class="type">hstore</code> <code class="literal">-</code> <code class="type">text[]</code></td><td>delete keys from left operand</td><td><code class="literal">'a=>1, b=>2, c=>3'::hstore - ARRAY['a','b']</code></td><td><code class="literal">"c"=>"3"</code></td></tr><tr><td><code class="type">hstore</code> <code class="literal">-</code> <code class="type">hstore</code></td><td>delete matching pairs from left operand</td><td><code class="literal">'a=>1, b=>2, c=>3'::hstore - 'a=>4, b=>2'::hstore</code></td><td><code class="literal">"a"=>"1", "c"=>"3"</code></td></tr><tr><td><code class="type">record</code> <code class="literal">#=</code> <code class="type">hstore</code></td><td>replace fields in <code class="type">record</code> with matching values from <code class="type">hstore</code></td><td>see Examples section</td><td> </td></tr><tr><td><code class="literal">%%</code> <code class="type">hstore</code></td><td>convert <code class="type">hstore</code> to array of alternating keys and values</td><td><code class="literal">%% 'a=>foo, b=>bar'::hstore</code></td><td><code class="literal">{a,foo,b,bar}</code></td></tr><tr><td><code class="literal">%#</code> <code class="type">hstore</code></td><td>convert <code class="type">hstore</code> to two-dimensional key/value array</td><td><code class="literal">%# 'a=>foo, b=>bar'::hstore</code></td><td><code class="literal">{{a,foo},{b,bar}}</code></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">@></code>
and <code class="literal"><@</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 /><col /><col /><col /><col /></colgroup><thead><tr><th>Function</th><th>Return Type</th><th>Description</th><th>Example</th><th>Result</th></tr></thead><tbody><tr><td><code class="function">hstore(record)</code><a id="id-1.11.7.25.5.5.2.2.1.1.2" class="indexterm"></a></td><td><code class="type">hstore</code></td><td>construct an <code class="type">hstore</code> from a record or row</td><td><code class="literal">hstore(ROW(1,2))</code></td><td><code class="literal">f1=>1,f2=>2</code></td></tr><tr><td><code class="function">hstore(text[])</code></td><td><code class="type">hstore</code></td><td>construct an <code class="type">hstore</code> from an array, which may be either
a key/value array, or a two-dimensional array</td><td><code class="literal">hstore(ARRAY['a','1','b','2']) || hstore(ARRAY[['c','3'],['d','4']])</code></td><td><code class="literal">a=>1, b=>2, c=>3, d=>4</code></td></tr><tr><td><code class="function">hstore(text[], text[])</code></td><td><code class="type">hstore</code></td><td>construct an <code class="type">hstore</code> from separate key and value arrays</td><td><code class="literal">hstore(ARRAY['a','b'], ARRAY['1','2'])</code></td><td><code class="literal">"a"=>"1","b"=>"2"</code></td></tr><tr><td><code class="function">hstore(text, text)</code></td><td><code class="type">hstore</code></td><td>make single-item <code class="type">hstore</code></td><td><code class="literal">hstore('a', 'b')</code></td><td><code class="literal">"a"=>"b"</code></td></tr><tr><td><code class="function">akeys(hstore)</code><a id="id-1.11.7.25.5.5.2.2.5.1.2" class="indexterm"></a></td><td><code class="type">text[]</code></td><td>get <code class="type">hstore</code>'s keys as an array</td><td><code class="literal">akeys('a=>1,b=>2')</code></td><td><code class="literal">{a,b}</code></td></tr><tr><td><code class="function">skeys(hstore)</code><a id="id-1.11.7.25.5.5.2.2.6.1.2" class="indexterm"></a></td><td><code class="type">setof text</code></td><td>get <code class="type">hstore</code>'s keys as a set</td><td><code class="literal">skeys('a=>1,b=>2')</code></td><td>
<pre class="programlisting">
a
b
</pre></td></tr><tr><td><code class="function">avals(hstore)</code><a id="id-1.11.7.25.5.5.2.2.7.1.2" class="indexterm"></a></td><td><code class="type">text[]</code></td><td>get <code class="type">hstore</code>'s values as an array</td><td><code class="literal">avals('a=>1,b=>2')</code></td><td><code class="literal">{1,2}</code></td></tr><tr><td><code class="function">svals(hstore)</code><a id="id-1.11.7.25.5.5.2.2.8.1.2" class="indexterm"></a></td><td><code class="type">setof text</code></td><td>get <code class="type">hstore</code>'s values as a set</td><td><code class="literal">svals('a=>1,b=>2')</code></td><td>
<pre class="programlisting">
1
2
</pre></td></tr><tr><td><code class="function">hstore_to_array(hstore)</code><a id="id-1.11.7.25.5.5.2.2.9.1.2" class="indexterm"></a></td><td><code class="type">text[]</code></td><td>get <code class="type">hstore</code>'s keys and values as an array of alternating
keys and values</td><td><code class="literal">hstore_to_array('a=>1,b=>2')</code></td><td><code class="literal">{a,1,b,2}</code></td></tr><tr><td><code class="function">hstore_to_matrix(hstore)</code><a id="id-1.11.7.25.5.5.2.2.10.1.2" class="indexterm"></a></td><td><code class="type">text[]</code></td><td>get <code class="type">hstore</code>'s keys and values as a two-dimensional array</td><td><code class="literal">hstore_to_matrix('a=>1,b=>2')</code></td><td><code class="literal">{{a,1},{b,2}}</code></td></tr><tr><td><code class="function">hstore_to_json(hstore)</code><a id="id-1.11.7.25.5.5.2.2.11.1.2" class="indexterm"></a></td><td><code class="type">json</code></td><td>get <code class="type">hstore</code> as a <code class="type">json</code> value, converting
all non-null values to JSON strings</td><td><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></td><td><code class="literal">{"a key": "1", "b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4"}</code></td></tr><tr><td><code class="function">hstore_to_jsonb(hstore)</code><a id="id-1.11.7.25.5.5.2.2.12.1.2" class="indexterm"></a></td><td><code class="type">jsonb</code></td><td>get <code class="type">hstore</code> as a <code class="type">jsonb</code> value, converting
all non-null values to JSON strings</td><td><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></td><td><code class="literal">{"a key": "1", "b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4"}</code></td></tr><tr><td><code class="function">hstore_to_json_loose(hstore)</code><a id="id-1.11.7.25.5.5.2.2.13.1.2" class="indexterm"></a></td><td><code class="type">json</code></td><td>get <code class="type">hstore</code> as a <code class="type">json</code> value, but attempt to distinguish numerical and Boolean values so they are unquoted in the JSON</td><td><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></td><td><code class="literal">{"a key": 1, "b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4}</code></td></tr><tr><td><code class="function">hstore_to_jsonb_loose(hstore)</code><a id="id-1.11.7.25.5.5.2.2.14.1.2" class="indexterm"></a></td><td><code class="type">jsonb</code></td><td>get <code class="type">hstore</code> as a <code class="type">jsonb</code> value, but attempt to distinguish numerical and Boolean values so they are unquoted in the JSON</td><td><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></td><td><code class="literal">{"a key": 1, "b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4}</code></td></tr><tr><td><code class="function">slice(hstore, text[])</code><a id="id-1.11.7.25.5.5.2.2.15.1.2" class="indexterm"></a></td><td><code class="type">hstore</code></td><td>extract a subset of an <code class="type">hstore</code></td><td><code class="literal">slice('a=>1,b=>2,c=>3'::hstore, ARRAY['b','c','x'])</code></td><td><code class="literal">"b"=>"2", "c"=>"3"</code></td></tr><tr><td><code class="function">each(hstore)</code><a id="id-1.11.7.25.5.5.2.2.16.1.2" class="indexterm"></a></td><td><code class="type">setof(key text, value text)</code></td><td>get <code class="type">hstore</code>'s keys and values as a set</td><td><code class="literal">select * from each('a=>1,b=>2')</code></td><td>
<pre class="programlisting">
key | value
-----+-------
a | 1
b | 2
</pre></td></tr><tr><td><code class="function">exist(hstore,text)</code><a id="id-1.11.7.25.5.5.2.2.17.1.2" class="indexterm"></a></td><td><code class="type">boolean</code></td><td>does <code class="type">hstore</code> contain key?</td><td><code class="literal">exist('a=>1','a')</code></td><td><code class="literal">t</code></td></tr><tr><td><code class="function">defined(hstore,text)</code><a id="id-1.11.7.25.5.5.2.2.18.1.2" class="indexterm"></a></td><td><code class="type">boolean</code></td><td>does <code class="type">hstore</code> contain non-<code class="literal">NULL</code> value for key?</td><td><code class="literal">defined('a=>NULL','a')</code></td><td><code class="literal">f</code></td></tr><tr><td><code class="function">delete(hstore,text)</code><a id="id-1.11.7.25.5.5.2.2.19.1.2" class="indexterm"></a></td><td><code class="type">hstore</code></td><td>delete pair with matching key</td><td><code class="literal">delete('a=>1,b=>2','b')</code></td><td><code class="literal">"a"=>"1"</code></td></tr><tr><td><code class="function">delete(hstore,text[])</code></td><td><code class="type">hstore</code></td><td>delete pairs with matching keys</td><td><code class="literal">delete('a=>1,b=>2,c=>3',ARRAY['a','b'])</code></td><td><code class="literal">"c"=>"3"</code></td></tr><tr><td><code class="function">delete(hstore,hstore)</code></td><td><code class="type">hstore</code></td><td>delete pairs matching those in the second argument</td><td><code class="literal">delete('a=>1,b=>2','a=>4,b=>2'::hstore)</code></td><td><code class="literal">"a"=>"1"</code></td></tr><tr><td><code class="function">populate_record(record,hstore)</code><a id="id-1.11.7.25.5.5.2.2.22.1.2" class="indexterm"></a></td><td><code class="type">record</code></td><td>replace fields in <code class="type">record</code> with matching values from <code class="type">hstore</code></td><td>see Examples section</td><td> </td></tr></tbody></table></div></div><br class="table-break" /><div class="note"><h3 class="title">Note</h3><p>
The function <code class="function">hstore_to_json</code> is used when
an <code class="type">hstore</code> value is cast to <code class="type">json</code>.
Likewise, <code class="function">hstore_to_jsonb</code> is used when
an <code class="type">hstore</code> value is cast to <code class="type">jsonb</code>.
</p></div><div class="note"><h3 class="title">Note</h3><p>
The function <code class="function">populate_record</code> is actually declared
with <code class="type">anyelement</code>, not <code class="type">record</code>, as its first argument,
but it will reject non-record types with a run-time error.
</p></div></div><div class="sect2" id="id-1.11.7.25.6"><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">@></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="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.7"><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"=>"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.25.8"><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=>bq, b=>NULL, ""=>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.9"><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 exclusive 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.10"><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="46.1. Python 2 vs. Python 3">Section 46.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><div class="sect2" id="id-1.11.7.25.11"><div class="titlepage"><div><div><h3 class="title">F.16.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">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="contrib.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="intagg.html">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">Home</a></td><td width="40%" align="right" valign="top"> F.17. intagg</td></tr></table></div></body></html>
