<!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.4.12 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="2012-05-31T23:30:11"></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" >PostgreSQL 8.4.12 Documentation</TH ></TR ><TR ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="fuzzystrmatch.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A 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 HREF="contrib.html" >Fast Forward</A ></TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A 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 ><A NAME="AEN115798" ></A ><P > This module implements a data type <TT CLASS="TYPE" >hstore</TT > for storing sets of (key,value) pairs within a single <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > data field. This can be useful in various scenarios, such as rows with many attributes that are rarely examined, or semi-structured data. </P ><P > In the current implementation, neither the key nor the value string can exceed 65535 bytes in length; an error will be thrown if this limit is exceeded. These maximum lengths may change in future releases. </P ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="AEN115804" >F.13.1. <TT CLASS="TYPE" >hstore</TT > External Representation</A ></H2 ><P > The text representation of an <TT CLASS="TYPE" >hstore</TT > value includes zero or more <TT CLASS="REPLACEABLE" ><I >key</I ></TT > <TT CLASS="LITERAL" >=></TT > <TT CLASS="REPLACEABLE" ><I >value</I ></TT > items, separated by commas. For example: </P><PRE CLASS="PROGRAMLISTING" > k => v foo => bar, baz => whatever "1-a" => "anything at all" </PRE ><P> The order of the items is not considered significant (and may not be reproduced on output). Whitespace between items or around the <TT CLASS="LITERAL" >=></TT > sign is ignored. Use double quotes if a key or value includes whitespace, comma, <TT CLASS="LITERAL" >=</TT > or <TT CLASS="LITERAL" >></TT >. To include a double quote or a backslash in a key or value, precede it with another backslash. (Keep in mind that depending on the setting of <TT CLASS="VARNAME" >standard_conforming_strings</TT >, you may need to double backslashes in SQL literal strings.) </P ><P > A value (but not a key) can be a SQL NULL. This is represented as </P><PRE CLASS="PROGRAMLISTING" > key => NULL </PRE ><P> The <TT CLASS="LITERAL" >NULL</TT > keyword is not case-sensitive. Again, use double quotes if you want the string <TT CLASS="LITERAL" >null</TT > to be treated as an ordinary data value. </P ><P > Currently, double quotes are always used to surround key and value strings on output, even when this is not strictly necessary. </P ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="AEN115822" >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 (null 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" >text</TT > <TT CLASS="LITERAL" >=></TT > <TT CLASS="TYPE" >text</TT ></TD ><TD >make single-item <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" >hstore</TT > <TT CLASS="LITERAL" >||</TT > <TT CLASS="TYPE" >hstore</TT ></TD ><TD >concatenation</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" >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 ></TBODY ></TABLE ></DIV ><P > (Before PostgreSQL 8.2, the containment operators @> and <@ were respectively called @ and ~. These names are still available, but are deprecated and will eventually be retired. Notice that the old names are reversed from the convention formerly followed by the core geometric datatypes!) </P ><DIV CLASS="NOTE" ><BLOCKQUOTE CLASS="NOTE" ><P ><B >Note: </B > The <TT CLASS="LITERAL" >=></TT > operator is deprecated and may be removed in a future release. Use the <TT CLASS="LITERAL" >hstore(text, text)</TT > function instead. </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(text, text)</CODE ></TD ><TD ><TT CLASS="TYPE" >hstore</TT ></TD ><TD >make single-item <TT CLASS="TYPE" >hstore</TT ></TD ><TD ><TT CLASS="LITERAL" >hstore('a', 'b')</TT ></TD ><TD ><TT CLASS="LITERAL" >"a"=>"b"</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 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 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 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 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" >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 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-null 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 any item matching key</TD ><TD ><TT CLASS="LITERAL" >delete('a=>1,b=>2','b')</TT ></TD ><TD ><TT CLASS="LITERAL" >"a"=>"1"</TT ></TD ></TR ></TBODY ></TABLE ></DIV ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="AEN116013" >F.13.3. Indexes</A ></H2 ><P > <TT CLASS="TYPE" >hstore</TT > has index support for <TT CLASS="LITERAL" >@></TT > and <TT CLASS="LITERAL" >?</TT > operators. You can use either GiST or GIN index types. For example: </P ><PRE CLASS="PROGRAMLISTING" >CREATE INDEX hidx ON testhstore USING GIST(h); CREATE INDEX hidx ON testhstore USING GIN(h); </PRE ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="AEN116020" >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 ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="AEN116026" >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. Examples below 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="AEN116036" >F.13.6. 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 ></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 >