<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>hstore</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 8.5alpha3 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="Additional Supplied Modules"
HREF="contrib.html"><LINK
REL="PREVIOUS"
TITLE="fuzzystrmatch"
HREF="fuzzystrmatch.html"><LINK
REL="NEXT"
TITLE="intagg"
HREF="intagg.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2010-04-15T09:31:23"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
><A
HREF="index.html"
>PostgreSQL 8.5alpha3 Documentation</A
></TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
TITLE="fuzzystrmatch"
HREF="fuzzystrmatch.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
TITLE="Additional Supplied Modules"
HREF="contrib.html"
>Fast Backward</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Appendix F. Additional Supplied Modules</TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
TITLE="Additional Supplied Modules"
HREF="contrib.html"
>Fast Forward</A
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
TITLE="intagg"
HREF="intagg.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="HSTORE"
>F.13. hstore</A
></H1
><P
> This module implements the <TT
CLASS="TYPE"
>hstore</TT
> 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"
><H2
CLASS="SECT2"
><A
NAME="AEN114395"
>F.13.1. <TT
CLASS="TYPE"
>hstore</TT
> External Representation</A
></H2
><P
> The text representation of an <TT
CLASS="TYPE"
>hstore</TT
>, used for input and output,
includes zero or more <TT
CLASS="REPLACEABLE"
><I
>key</I
></TT
> <TT
CLASS="LITERAL"
>=></TT
>
<TT
CLASS="REPLACEABLE"
><I
>value</I
></TT
> pairs separated by commas. Some examples:
</P><PRE
CLASS="PROGRAMLISTING"
> 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 <TT
CLASS="LITERAL"
>=></TT
> sign is
ignored. Double-quote keys and values that include whitespace, commas,
<TT
CLASS="LITERAL"
>=</TT
>s or <TT
CLASS="LITERAL"
>></TT
>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 <TT
CLASS="TYPE"
>hstore</TT
> is unique. If you declare an <TT
CLASS="TYPE"
>hstore</TT
>
with duplicate keys, only one will be stored in the <TT
CLASS="TYPE"
>hstore</TT
> 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 <TT
CLASS="LITERAL"
>NULL</TT
>. For example:
</P><PRE
CLASS="PROGRAMLISTING"
> key => NULL
</PRE
><P>
The <TT
CLASS="LITERAL"
>NULL</TT
> keyword is case-insensitive. Double-quote the
<TT
CLASS="LITERAL"
>NULL</TT
> to treat it as the ordinary string "NULL".
</P
><DIV
CLASS="NOTE"
><BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
> Keep in mind that the <TT
CLASS="TYPE"
>hstore</TT
> text format, when used for input,
applies <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>before</I
></SPAN
> any required quoting or escaping. If you are
passing an <TT
CLASS="TYPE"
>hstore</TT
> 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 <TT
CLASS="VARNAME"
>standard_conforming_strings</TT
> configuration parameter)
backslash characters need to be escaped correctly. See
<A
HREF="sql-syntax-lexical.html#SQL-SYNTAX-STRINGS"
>Section 4.1.2.1</A
> for more on the handling of string
constants.
</P
></BLOCKQUOTE
></DIV
><P
> On output, double quotes always surround keys and values, even when it's
not strictly necessary.
</P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="AEN114425"
>F.13.2. <TT
CLASS="TYPE"
>hstore</TT
> Operators and Functions</A
></H2
><DIV
CLASS="TABLE"
><A
NAME="HSTORE-OP-TABLE"
></A
><P
><B
>Table F-5. <TT
CLASS="TYPE"
>hstore</TT
> Operators</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><COL><COL><COL><COL><THEAD
><TR
><TH
>Operator</TH
><TH
>Description</TH
><TH
>Example</TH
><TH
>Result</TH
></TR
></THEAD
><TBODY
><TR
><TD
><TT
CLASS="TYPE"
>hstore</TT
> <TT
CLASS="LITERAL"
>-></TT
> <TT
CLASS="TYPE"
>text</TT
></TD
><TD
>get value for key (<TT
CLASS="LITERAL"
>NULL</TT
> if not present)</TD
><TD
><TT
CLASS="LITERAL"
>'a=>x, b=>y'::hstore -> 'a'</TT
></TD
><TD
><TT
CLASS="LITERAL"
>x</TT
></TD
></TR
><TR
><TD
><TT
CLASS="TYPE"
>hstore</TT
> <TT
CLASS="LITERAL"
>-></TT
> <TT
CLASS="TYPE"
>text[]</TT
></TD
><TD
>get values for keys (<TT
CLASS="LITERAL"
>NULL</TT
> if not present)</TD
><TD
><TT
CLASS="LITERAL"
>'a=>x, b=>y, c=>z'::hstore -> ARRAY['c','a']</TT
></TD
><TD
><TT
CLASS="LITERAL"
>{"z","x"}</TT
></TD
></TR
><TR
><TD
><TT
CLASS="TYPE"
>text</TT
> <TT
CLASS="LITERAL"
>=></TT
> <TT
CLASS="TYPE"
>text</TT
></TD
><TD
>make single-pair <TT
CLASS="TYPE"
>hstore</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'a' => 'b'</TT
></TD
><TD
><TT
CLASS="LITERAL"
>"a"=>"b"</TT
></TD
></TR
><TR
><TD
><TT
CLASS="TYPE"
>text[]</TT
> <TT
CLASS="LITERAL"
>=></TT
> <TT
CLASS="TYPE"
>text[]</TT
></TD
><TD
>construct an <TT
CLASS="TYPE"
>hstore</TT
> from separate key and value arrays</TD
><TD
><TT
CLASS="LITERAL"
>ARRAY['a','b'] => ARRAY['1','2']</TT
></TD
><TD
><TT
CLASS="LITERAL"
>"a"=>"1","b"=>"2"</TT
></TD
></TR
><TR
><TD
><TT
CLASS="TYPE"
>hstore</TT
> <TT
CLASS="LITERAL"
>=></TT
> <TT
CLASS="TYPE"
>text[]</TT
></TD
><TD
>extract a subset of an <TT
CLASS="TYPE"
>hstore</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'a=>1,b=>2,c=>3'::hstore => ARRAY['b','c','x']</TT
></TD
><TD
><TT
CLASS="LITERAL"
>"b"=>"2", "c"=>"3"</TT
></TD
></TR
><TR
><TD
><TT
CLASS="TYPE"
>hstore</TT
> <TT
CLASS="LITERAL"
>||</TT
> <TT
CLASS="TYPE"
>hstore</TT
></TD
><TD
>concatenate <TT
CLASS="TYPE"
>hstore</TT
>s</TD
><TD
><TT
CLASS="LITERAL"
>'a=>b, c=>d'::hstore || 'c=>x, d=>q'::hstore</TT
></TD
><TD
><TT
CLASS="LITERAL"
>"a"=>"b", "c"=>"x", "d"=>"q"</TT
></TD
></TR
><TR
><TD
><TT
CLASS="TYPE"
>hstore</TT
> <TT
CLASS="LITERAL"
>?</TT
> <TT
CLASS="TYPE"
>text</TT
></TD
><TD
>does <TT
CLASS="TYPE"
>hstore</TT
> contain key?</TD
><TD
><TT
CLASS="LITERAL"
>'a=>1'::hstore ? 'a'</TT
></TD
><TD
><TT
CLASS="LITERAL"
>t</TT
></TD
></TR
><TR
><TD
><TT
CLASS="TYPE"
>hstore</TT
> <TT
CLASS="LITERAL"
>?&</TT
> <TT
CLASS="TYPE"
>text[]</TT
></TD
><TD
>does <TT
CLASS="TYPE"
>hstore</TT
> contain all specified keys?</TD
><TD
><TT
CLASS="LITERAL"
>'a=>1,b=>2'::hstore ?& ARRAY['a','b']</TT
></TD
><TD
><TT
CLASS="LITERAL"
>t</TT
></TD
></TR
><TR
><TD
><TT
CLASS="TYPE"
>hstore</TT
> <TT
CLASS="LITERAL"
>?|</TT
> <TT
CLASS="TYPE"
>text[]</TT
></TD
><TD
>does <TT
CLASS="TYPE"
>hstore</TT
> contain any of the specified keys?</TD
><TD
><TT
CLASS="LITERAL"
>'a=>1,b=>2'::hstore ?| ARRAY['b','c']</TT
></TD
><TD
><TT
CLASS="LITERAL"
>t</TT
></TD
></TR
><TR
><TD
><TT
CLASS="TYPE"
>hstore</TT
> <TT
CLASS="LITERAL"
>@></TT
> <TT
CLASS="TYPE"
>hstore</TT
></TD
><TD
>does left operand contain right?</TD
><TD
><TT
CLASS="LITERAL"
>'a=>b, b=>1, c=>NULL'::hstore @> 'b=>1'</TT
></TD
><TD
><TT
CLASS="LITERAL"
>t</TT
></TD
></TR
><TR
><TD
><TT
CLASS="TYPE"
>hstore</TT
> <TT
CLASS="LITERAL"
><@</TT
> <TT
CLASS="TYPE"
>hstore</TT
></TD
><TD
>is left operand contained in right?</TD
><TD
><TT
CLASS="LITERAL"
>'a=>c'::hstore <@ 'a=>b, b=>1, c=>NULL'</TT
></TD
><TD
><TT
CLASS="LITERAL"
>f</TT
></TD
></TR
><TR
><TD
><TT
CLASS="TYPE"
>hstore</TT
> <TT
CLASS="LITERAL"
>-</TT
> <TT
CLASS="TYPE"
>text</TT
></TD
><TD
>delete key from left operand</TD
><TD
><TT
CLASS="LITERAL"
>'a=>1, b=>2, c=>3'::hstore - 'b'::text</TT
></TD
><TD
><TT
CLASS="LITERAL"
>"a"=>"1", "c"=>"3"</TT
></TD
></TR
><TR
><TD
><TT
CLASS="TYPE"
>hstore</TT
> <TT
CLASS="LITERAL"
>-</TT
> <TT
CLASS="TYPE"
>text[]</TT
></TD
><TD
>delete keys from left operand</TD
><TD
><TT
CLASS="LITERAL"
>'a=>1, b=>2, c=>3'::hstore - ARRAY['a','b']</TT
></TD
><TD
><TT
CLASS="LITERAL"
>"c"=>"3"</TT
></TD
></TR
><TR
><TD
><TT
CLASS="TYPE"
>hstore</TT
> <TT
CLASS="LITERAL"
>-</TT
> <TT
CLASS="TYPE"
>hstore</TT
></TD
><TD
>delete matching pairs from left operand</TD
><TD
><TT
CLASS="LITERAL"
>'a=>1, b=>2, c=>3'::hstore - 'a=>4, b=>2'::hstore</TT
></TD
><TD
><TT
CLASS="LITERAL"
>"a"=>"1", "c"=>"3"</TT
></TD
></TR
><TR
><TD
><TT
CLASS="TYPE"
>record</TT
> <TT
CLASS="LITERAL"
>#=</TT
> <TT
CLASS="TYPE"
>hstore</TT
></TD
><TD
>replace fields in <TT
CLASS="TYPE"
>record</TT
> with matching values from <TT
CLASS="TYPE"
>hstore</TT
></TD
><TD
>see Examples section</TD
><TD
> </TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>%%</TT
> <TT
CLASS="TYPE"
>hstore</TT
></TD
><TD
>convert <TT
CLASS="TYPE"
>hstore</TT
> to array of alternating keys and values</TD
><TD
><TT
CLASS="LITERAL"
>%% 'a=>foo, b=>bar'::hstore</TT
></TD
><TD
><TT
CLASS="LITERAL"
>{a,foo,b,bar}</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>%#</TT
> <TT
CLASS="TYPE"
>hstore</TT
></TD
><TD
>convert <TT
CLASS="TYPE"
>hstore</TT
> to two-dimensional key/value array</TD
><TD
><TT
CLASS="LITERAL"
>%# 'a=>foo, b=>bar'::hstore</TT
></TD
><TD
><TT
CLASS="LITERAL"
>{{a,foo},{b,bar}}</TT
></TD
></TR
></TBODY
></TABLE
></DIV
><DIV
CLASS="NOTE"
><BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
> Prior to PostgreSQL 8.2, the containment operators <TT
CLASS="LITERAL"
>@></TT
>
and <TT
CLASS="LITERAL"
><@</TT
> were called <TT
CLASS="LITERAL"
>@</TT
> and <TT
CLASS="LITERAL"
>~</TT
>,
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 datatypes!
</P
></BLOCKQUOTE
></DIV
><DIV
CLASS="TABLE"
><A
NAME="HSTORE-FUNC-TABLE"
></A
><P
><B
>Table F-6. <TT
CLASS="TYPE"
>hstore</TT
> Functions</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><COL><COL><COL><COL><COL><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
></TD
><TD
><TT
CLASS="TYPE"
>hstore</TT
></TD
><TD
>construct an <TT
CLASS="TYPE"
>hstore</TT
> from a record or row</TD
><TD
><TT
CLASS="LITERAL"
>hstore(ROW(1,2))</TT
></TD
><TD
><TT
CLASS="LITERAL"
>f1=>1,f2=>2</TT
></TD
></TR
><TR
><TD
><CODE
CLASS="FUNCTION"
>hstore(text[])</CODE
></TD
><TD
><TT
CLASS="TYPE"
>hstore</TT
></TD
><TD
>construct an <TT
CLASS="TYPE"
>hstore</TT
> from an array, which may be either
a key/value array, or a two-dimensional array</TD
><TD
><TT
CLASS="LITERAL"
>hstore(ARRAY['a','1','b','2']) || hstore(ARRAY[['c','3'],['d','4']])</TT
></TD
><TD
><TT
CLASS="LITERAL"
>a=>1, b=>2, c=>3, d=>4</TT
></TD
></TR
><TR
><TD
><CODE
CLASS="FUNCTION"
>akeys(hstore)</CODE
></TD
><TD
><TT
CLASS="TYPE"
>text[]</TT
></TD
><TD
>get <TT
CLASS="TYPE"
>hstore</TT
>'s keys as an array</TD
><TD
><TT
CLASS="LITERAL"
>akeys('a=>1,b=>2')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>{a,b}</TT
></TD
></TR
><TR
><TD
><CODE
CLASS="FUNCTION"
>skeys(hstore)</CODE
></TD
><TD
><TT
CLASS="TYPE"
>setof text</TT
></TD
><TD
>get <TT
CLASS="TYPE"
>hstore</TT
>'s keys as a set</TD
><TD
><TT
CLASS="LITERAL"
>skeys('a=>1,b=>2')</TT
></TD
><TD
><PRE
CLASS="PROGRAMLISTING"
>a
b</PRE
></TD
></TR
><TR
><TD
><CODE
CLASS="FUNCTION"
>avals(hstore)</CODE
></TD
><TD
><TT
CLASS="TYPE"
>text[]</TT
></TD
><TD
>get <TT
CLASS="TYPE"
>hstore</TT
>'s values as an array</TD
><TD
><TT
CLASS="LITERAL"
>avals('a=>1,b=>2')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>{1,2}</TT
></TD
></TR
><TR
><TD
><CODE
CLASS="FUNCTION"
>svals(hstore)</CODE
></TD
><TD
><TT
CLASS="TYPE"
>setof text</TT
></TD
><TD
>get <TT
CLASS="TYPE"
>hstore</TT
>'s values as a set</TD
><TD
><TT
CLASS="LITERAL"
>svals('a=>1,b=>2')</TT
></TD
><TD
><PRE
CLASS="PROGRAMLISTING"
>1
2</PRE
></TD
></TR
><TR
><TD
><CODE
CLASS="FUNCTION"
>hstore_to_array(hstore)</CODE
></TD
><TD
><TT
CLASS="TYPE"
>text[]</TT
></TD
><TD
>get <TT
CLASS="TYPE"
>hstore</TT
>'s keys and values as an array of alternating
keys and values</TD
><TD
><TT
CLASS="LITERAL"
>hstore_to_array('a=>1,b=>2')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>{a,1,b,2}</TT
></TD
></TR
><TR
><TD
><CODE
CLASS="FUNCTION"
>hstore_to_matrix(hstore)</CODE
></TD
><TD
><TT
CLASS="TYPE"
>text[]</TT
></TD
><TD
>get <TT
CLASS="TYPE"
>hstore</TT
>'s keys and values as a two-dimensional array</TD
><TD
><TT
CLASS="LITERAL"
>hstore_to_matrix('a=>1,b=>2')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>{{a,1},{b,2}}</TT
></TD
></TR
><TR
><TD
><CODE
CLASS="FUNCTION"
>each(hstore)</CODE
></TD
><TD
><TT
CLASS="TYPE"
>setof(key text, value text)</TT
></TD
><TD
>get <TT
CLASS="TYPE"
>hstore</TT
>'s keys and values as a set</TD
><TD
><TT
CLASS="LITERAL"
>select * from each('a=>1,b=>2')</TT
></TD
><TD
><PRE
CLASS="PROGRAMLISTING"
> key | value
-----+-------
a | 1
b | 2</PRE
></TD
></TR
><TR
><TD
><CODE
CLASS="FUNCTION"
>exist(hstore,text)</CODE
></TD
><TD
><TT
CLASS="TYPE"
>boolean</TT
></TD
><TD
>does <TT
CLASS="TYPE"
>hstore</TT
> contain key?</TD
><TD
><TT
CLASS="LITERAL"
>exist('a=>1','a')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>t</TT
></TD
></TR
><TR
><TD
><CODE
CLASS="FUNCTION"
>defined(hstore,text)</CODE
></TD
><TD
><TT
CLASS="TYPE"
>boolean</TT
></TD
><TD
>does <TT
CLASS="TYPE"
>hstore</TT
> contain non-<TT
CLASS="LITERAL"
>NULL</TT
> value for key?</TD
><TD
><TT
CLASS="LITERAL"
>defined('a=>NULL','a')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>f</TT
></TD
></TR
><TR
><TD
><CODE
CLASS="FUNCTION"
>delete(hstore,text)</CODE
></TD
><TD
><TT
CLASS="TYPE"
>hstore</TT
></TD
><TD
>delete pair with matching key</TD
><TD
><TT
CLASS="LITERAL"
>delete('a=>1,b=>2','b')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>"a"=>"1"</TT
></TD
></TR
><TR
><TD
><CODE
CLASS="FUNCTION"
>delete(hstore,text[])</CODE
></TD
><TD
><TT
CLASS="TYPE"
>hstore</TT
></TD
><TD
>delete pairs with matching keys</TD
><TD
><TT
CLASS="LITERAL"
>delete('a=>1,b=>2,c=>3',ARRAY['a','b'])</TT
></TD
><TD
><TT
CLASS="LITERAL"
>"c"=>"3"</TT
></TD
></TR
><TR
><TD
><CODE
CLASS="FUNCTION"
>delete(hstore,hstore)</CODE
></TD
><TD
><TT
CLASS="TYPE"
>hstore</TT
></TD
><TD
>delete pairs matching those in the second argument</TD
><TD
><TT
CLASS="LITERAL"
>delete('a=>1,b=>2','a=>4,b=>2'::hstore)</TT
></TD
><TD
><TT
CLASS="LITERAL"
>"a"=>"1"</TT
></TD
></TR
><TR
><TD
><CODE
CLASS="FUNCTION"
>populate_record(record,hstore)</CODE
></TD
><TD
><TT
CLASS="TYPE"
>record</TT
></TD
><TD
>replace fields in <TT
CLASS="TYPE"
>record</TT
> with matching values from <TT
CLASS="TYPE"
>hstore</TT
></TD
><TD
>see Examples section</TD
><TD
> </TD
></TR
></TBODY
></TABLE
></DIV
><DIV
CLASS="NOTE"
><BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
> The function <CODE
CLASS="FUNCTION"
>populate_record</CODE
> is actually declared
with <TT
CLASS="TYPE"
>anyelement</TT
>, not <TT
CLASS="TYPE"
>record</TT
>, as its first argument,
but it will reject non-record types with a runtime error.
</P
></BLOCKQUOTE
></DIV
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="AEN114803"
>F.13.3. Indexes</A
></H2
><P
> <TT
CLASS="TYPE"
>hstore</TT
> has GiST and GIN index support for the <TT
CLASS="LITERAL"
>@></TT
>,
<TT
CLASS="LITERAL"
>?</TT
>, <TT
CLASS="LITERAL"
>?&</TT
> and <TT
CLASS="LITERAL"
>?|</TT
> 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
> <TT
CLASS="TYPE"
>hstore</TT
> also supports <TT
CLASS="TYPE"
>btree</TT
> or <TT
CLASS="TYPE"
>hash</TT
> indexes for
the <TT
CLASS="LITERAL"
>=</TT
> operator. This allows <TT
CLASS="TYPE"
>hstore</TT
> columns to be
declared <TT
CLASS="LITERAL"
>UNIQUE</TT
>, or to be used in <TT
CLASS="LITERAL"
>GROUP BY</TT
>,
<TT
CLASS="LITERAL"
>ORDER BY</TT
> or <TT
CLASS="LITERAL"
>DISTINCT</TT
> expressions. The sort ordering
for <TT
CLASS="TYPE"
>hstore</TT
> values is not particularly useful, but these indexes
may be useful for equivalence lookups. Create indexes for <TT
CLASS="LITERAL"
>=</TT
>
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"
><H2
CLASS="SECT2"
><A
NAME="AEN114825"
>F.13.4. Examples</A
></H2
><P
> Add a key, or update an existing key with a new value:
</P
><PRE
CLASS="PROGRAMLISTING"
>UPDATE tab SET h = h || ('c' => '3');
</PRE
><P
> Delete a key:
</P
><PRE
CLASS="PROGRAMLISTING"
>UPDATE tab SET h = delete(h, 'k1');
</PRE
><P
> Convert a <TT
CLASS="TYPE"
>record</TT
> to an <TT
CLASS="TYPE"
>hstore</TT
>:
</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
> Convert an <TT
CLASS="TYPE"
>hstore</TT
> to a predefined <TT
CLASS="TYPE"
>record</TT
> 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
> Modify an existing record using the values from an <TT
CLASS="TYPE"
>hstore</TT
>:
</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
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="AEN114842"
>F.13.5. Statistics</A
></H2
><P
> The <TT
CLASS="TYPE"
>hstore</TT
> 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
> Using a table:
</P
><PRE
CLASS="PROGRAMLISTING"
>SELECT (each(h)).key, (each(h)).value INTO stat FROM testhstore;
</PRE
><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
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="AEN114852"
>F.13.6. Compatibility</A
></H2
><P
> <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>When upgrading from older versions, always load the new
version of this module into the database before restoring a dump.
Otherwise, many new features will be unavailable.</I
></SPAN
>
</P
><P
> As of PostgreSQL 8.5, <TT
CLASS="TYPE"
>hstore</TT
> 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 <TT
CLASS="LITERAL"
>UPDATE</TT
> statement as follows:
</P
><PRE
CLASS="PROGRAMLISTING"
>UPDATE tablename SET hstorecol = hstorecol || '';
</PRE
><P
> Another way to do it is:
</P><PRE
CLASS="PROGRAMLISTING"
>ALTER TABLE tablename ALTER hstorecol TYPE hstore USING hstorecol || '';
</PRE
><P>
The <TT
CLASS="COMMAND"
>ALTER TABLE</TT
> 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"
><H2
CLASS="SECT2"
><A
NAME="AEN114864"
>F.13.7. Authors</A
></H2
><P
> Oleg Bartunov <CODE
CLASS="EMAIL"
><<A
HREF="mailto:oleg@sai.msu.su"
>oleg@sai.msu.su</A
>></CODE
>, Moscow, Moscow University, Russia
</P
><P
> Teodor Sigaev <CODE
CLASS="EMAIL"
><<A
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
HREF="mailto:andrew@tao11.riddles.org.uk"
>andrew@tao11.riddles.org.uk</A
>></CODE
>,
United Kingdom
</P
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="fuzzystrmatch.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="intagg.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>fuzzystrmatch</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="contrib.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>intagg</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>
