README

[hstore.git] / README

Nested hstore
=============

This is the nested hstore we developed for PostgreSQL 9.4, which community
decided not to include to release in favour to jsonb data type (read my 
message about this thread
http://www.postgresql.org/message-id/CAF4Au4xO=sXBK4orGiS0AiXpZkH6f=QWptit6g9AmsEaH5CEAQ@mail.gmail.com
Nevertheless, we decided to save the patch for history. You can download it
from http://www.sigaev.ru/git/gitweb.cgi?p=hstore.git;a=summary
The patch is compatible with PostgreSQL 9.3+.

Slides about nested hstore:

* Binary storage for nested data structures and application to hstore data type,
  PostgreSQL Conference Europe 2013, Oct 29-Nov 1, 2013, Dublin, Ireland. 
  http://www.sai.msu.su/~megera/postgres/talks/hstore-dublin-2013.pdf
* Nested hstore with arrays support, PGCon-2013 conference,
  May 24, 2013, Ottawa, Canada
  http://www.sai.msu.su/~megera/postgres/talks/hstore-pgcon-2013.pdf


Engine Yard has supported this work.

==============================================================================

This module implements the hstore data type for storing arbitrarily nested key/
value pairs and arrays within a single PostgreSQL value. This can be useful in
various scenarios, such as rows with many attributes that are rarely examined,
or semi-structured data. Keys are strings, while values can be strings,
numbers, booleans, or NULL.

The hstore type is similar to the core json data type, but, in the current
implementation, differs in a few key ways:

  * It's faster. hstore is stored in a binary representation, whereas json is
    stored as text, and so needs to be parsed every time it's accessed.

  * Better index support. hstore can be used in GiST and GIN indexes to allow
    searches on keys or even key paths.

That said, hstore includes interfaces to transparently convert values to and
from json. These allow the best of both worlds: store and query hstore values,
but convert them to json when fetching them, for easy parsing in your client
application code.

hstore External Representation
==============================

The text representation of an hstore, used for input and output, may be
formatted as scalar values, hash-like values, array-like values, and nested
array and hash values. Scalar values are simply strings, numeric values,
booleans, or NULL. Strings containing whitespace, commas, =s or >s must be
double-quoted. To include a double quote or a backslash in a key or value,
escape it with a backslash. Boolean values may be represented as true, t,
false, or f. Use quotation marks to represent these values as strings. The NULL
keyword is case-insensitive. Double-quote the NULL to treat it as the ordinary
string "NULL". Some examples:

=% SELECT 'foo'::hstore, '"hi \"bob\""'::hstore, '1.0'::hstore, 'true'::hstore, NULL::hstore;
 hstore |    hstore    | hstore | hstore | hstore
--------+--------------+--------+--------+--------
 "foo"  | "hi \"bob\"" | 1.0    | t      |

Arrays of values of any supported type may be constructed as square-bracketed
comma-separated lists. Some examples:

=% SELECT '[k,v]'::hstore, '[1.0, "hi there", false, null]'::hstore;
   hstore   |           hstore
------------+----------------------------
 ["k", "v"] | [1.0, "hi there", f, NULL]

Hashes include zero or more key => value pairs separated by commas, optionally
bracketed by curly braces. Keys must be strings and may not be NULL; values may
be any hstore type, including NULL. Examples:

=% SELECT 'k => v'::hstore
-%      , '{foo => "hi there"}'::hstore
-%      , '{one => 1, two => 2.0, three => true, four => null}'::hstore;
  hstore  |      hstore       |                     hstore
----------+-------------------+------------------------------------------------
 "k"=>"v" | "foo"=>"hi there" | "one"=>1, "two"=>2.0, "four"=>NULL, "three"=>t

The order of the pairs is not significant (and may not be reproduced on
output).

Each key in an hstore hash is unique. If you declare an hstore hash with
duplicate keys, only one will be stored in the hstore and there is no guarantee
as to which will be kept:

SELECT 'a=>1,a=>2'::hstore;
  hstore
----------
 "a"=>1

Hashes and arrays may be arbitrarily nested. In this case, brackets are
required for hash values. Here's an example adapted from the GeoJSON spec:

=% SET hstore.pretty_print=true;
=% SELECT '{
  "type" => "Feature",
  "bbox" => [-180.0, -90.0, 180.0, 90.0],
  "geometry" => {
    "type" => "Polygon",
    "coordinates" => [[
      [-180.0, 10.0], [20.0, 90.0], [180.0, -5.0], [-30.0, -90.0]
      ]]
    }
}'::hstore;
          hstore
--------------------------
 "bbox"=>                +
 [                       +
     -180.0,             +
     -90.0,              +
     180.0,              +
     90.0                +
 ],                      +
 "type"=>"Feature",      +
 "geometry"=>            +
 {                       +
     "type"=>"Polygon",  +
     "coordinates"=>     +
     [                   +
         [               +
             [           +
                 -180.0, +
                 10.0    +
             ],          +
             [           +
                 20.0,   +
                 90.0    +
             ],          +
             [           +
                 180.0,  +
                 -5.0    +
             ],          +
             [           +
                 -30.0,  +
                 -90.0   +
             ]           +
         ]               +
     ]                   +
 }


    Note: Keep in mind that the hstore text format, when used for input,
    applies before any required quoting or escaping. If you are passing an
    hstore 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
    standard_conforming_strings configuration parameter) backslash characters
    need to be escaped correctly.

On output, double quotes always surround keys and values, even when it's not
strictly necessary.

Output Format Configuration Parameters
======================================

There are several configuration parameters that control the output formatting
of hstore values.

hstore.pretty_print (boolean)

    By default, the text representation of hstore values includes no whitespace
    between the values it contains. Set hstore.pretty_print to true to add
    newlines between values and to indent nested hashes and arrays.

hstore Operators and Functions

The operators provided by the hstore module are shown in Table F-6, the
functions in Table F-7.

Table F-6. hstore Operators

+-----------------------------------------------------------------------------+
|Operator|Returns|       Description        |      Example      |   Result    |
|--------+-------+--------------------------+-------------------+-------------|
|hstore  |text   |get value for key (NULL if|'a=>x, b=>         |x            |
|-> text |       |not present)              |y'::hstore -> 'a'  |             |
|--------+-------+--------------------------+-------------------+-------------|
|hstore  |       |get value for array index |'[foo,bar,baz]     |             |
|->      |text   |(NULL if not present)     |'::hstore -> 1     |bar          |
|integer |       |                          |                   |             |
|--------+-------+--------------------------+-------------------+-------------|
|hstore  |       |get numeric value for key |'a=>42.0, b=>      |             |
|^> text |numeric|(NULL if not numeric or   |y'::hstore ^> 'a'  |42.0         |
|        |       |not present)              |                   |             |
|--------+-------+--------------------------+-------------------+-------------|
|hstore  |       |get numeric value for     |'[foo,null,44]     |             |
|^>      |numeric|array index (NULL if not  |'::hstore ^> 2     |44           |
|integer |       |numeric or not present)   |                   |             |
|--------+-------+--------------------------+-------------------+-------------|
|hstore  |       |get boolean value for key |'a => 42.0, b =>   |             |
|?> text |boolean|(NULL if not boolean or   |true'::hstore ?>   |true         |
|        |       |not present)              |'b'                |             |
|--------+-------+--------------------------+-------------------+-------------|
|hstore  |       |get boolean value for     |'[false,null,44]   |             |
|?>      |boolean|array index (NULL if not  |'::hstore ?> 0     |false        |
|integer |       |boolean or not present)   |                   |             |
|--------+-------+--------------------------+-------------------+-------------|
|hstore  |       |get value for key path    |'foo => {bar =>    |             |
|#> text |text   |(NULL if not present)     |yellow}'::hstore #>|yellow       |
|[]      |       |                          |'{foo,bar}'        |             |
|--------+-------+--------------------------+-------------------+-------------|
|hstore #|       |get numeric value for key |'foo => {bar => 99}|             |
|^> text |numeric|path (NULL if not numeric |'::hstore #^> '    |99           |
|[]      |       |or not present)           |{foo,bar}'         |             |
|--------+-------+--------------------------+-------------------+-------------|
|hstore  |       |get boolean value for key |'foo => {bar =>    |             |
|#?> text|boolean|path (NULL if not boolean |true}'::hstore #?> |true         |
|[]      |       |or not present)           |'{foo,bar}'        |             |
|--------+-------+--------------------------+-------------------+-------------|
|hstore  |hstore |get hstore value for key  |'foo => {bar => 99}|"bar"=>99    |
|%> text |       |(NULL if not present)     |'::hstore %> 'foo' |             |
|--------+-------+--------------------------+-------------------+-------------|
|hstore  |       |get hstore value array    |'[1, 2, {foo=>hi}] |             |
|%>      |hstore |index (NULL if not        |'::hstore %> 2     |"foo"=>"hi"  |
|integer |       |present)                  |                   |             |
|--------+-------+--------------------------+-------------------+-------------|
|hstore #|       |get hstore value for key  |'a => 1, b => {c =>|             |
|%> text |hstore |path (NULL if not present)|[44,44]}'::hstore #|[44, 44]     |
|[]      |       |                          |%> '{b,c}'         |             |
|--------+-------+--------------------------+-------------------+-------------|
|hstore  |       |get values for keys (NULL |'a=>x, b=>y, c=>   |             |
|-> text |text[] |if not present)           |z'::hstore -> ARRAY|{z,x}        |
|[]      |       |                          |['c','a']          |             |
|--------+-------+--------------------------+-------------------+-------------|
|hstore ||       |                          |'a=>b, c=>         |"a"=>"b", "c"|
|| hstore|hstore |concatenate hstores       |d'::hstore || 'c=> |=>"x", "d"=> |
|        |       |                          |x, d=>q'::hstore   |"q"          |
|--------+-------+--------------------------+-------------------+-------------|
|hstore ?|boolean|does hstore contain key?  |'a=>1'::hstore ?   |true         |
|text    |       |                          |'a'                |             |
|--------+-------+--------------------------+-------------------+-------------|
|hstore ?|boolean|does hstore contain array |'[a,b,c]'::hstore ?|true         |
|integer |       |index?                    |2                  |             |
|--------+-------+--------------------------+-------------------+-------------|
|hstore  |       |does hstore contain key   |'[1, 2, {foo=>hi}] |             |
|#? text |boolean|path?                     |'::hstore #? '     |true         |
|[]      |       |                          |{2,foo}'           |             |
|--------+-------+--------------------------+-------------------+-------------|
|hstore ?|boolean|does hstore contain all   |'a=>1,b=>2'::hstore|true         |
|& text[]|       |specified keys?           |?& ARRAY['a','b']  |             |
|--------+-------+--------------------------+-------------------+-------------|
|hstore ?|boolean|does hstore contain any of|'a=>1,b=>2'::hstore|true         |
|| text[]|       |the specified keys?       |?| ARRAY['b','c']  |             |
|--------+-------+--------------------------+-------------------+-------------|
|hstore  |       |does left operand contain |'a=>b, b=>1, c=>   |             |
|@>      |boolean|right?                    |NULL'::hstore @> 'b|true         |
|hstore  |       |                          |=>1'               |             |
|--------+-------+--------------------------+-------------------+-------------|
|hstore  |       |is left operand contained |'a=>c'::hstore <@  |             |
|<@      |boolean|in right?                 |'a=>b, b=>1, c=>   |false        |
|hstore  |       |                          |NULL'              |             |
|--------+-------+--------------------------+-------------------+-------------|
|hstore -|       |delete key from left      |'a=>1, b=>2, c=>   |"a"=>1, "c"=>|
|text    |hstore |operand                   |3'::hstore -       |3            |
|        |       |                          |'b'::text          |             |
|--------+-------+--------------------------+-------------------+-------------|
|hstore -|hstore |delete index from left    |'[2, 3, 4, 6, 8]   |[2, 4, 6, 8] |
|integer |       |operand                   |'::hstore - 1      |             |
|--------+-------+--------------------------+-------------------+-------------|
|hstore -|       |delete keys from left     |'a=>1, b=>2, c=>   |             |
|text[]  |hstore |operand                   |3'::hstore - ARRAY |"c"=>3       |
|        |       |                          |['a','b']          |             |
|--------+-------+--------------------------+-------------------+-------------|
|hstore -|       |delete matching pairs from|'a=>1, b=>2, c=>   |"a"=>1, "c"=>|
|hstore  |hstore |left operand              |3'::hstore - 'a=>4,|3            |
|        |       |                          |b=>2'::hstore      |             |
|--------+-------+--------------------------+-------------------+-------------|
|hstore #|       |delete key path from left |'{a => {b => { c =>|"a"=>{"b"=>  |
|- text[]|hstore |operand                   |[1,2]}}}'::hstore #|{"c"=>[2]}}  |
|        |       |                          |- '{a,b,c,0}'      |             |
|--------+-------+--------------------------+-------------------+-------------|
|record #|       |replace fields in record  |see Examples       |             |
|= hstore|record |with matching values from |section            |             |
|        |       |hstore                    |                   |             |
|--------+-------+--------------------------+-------------------+-------------|
|%%      |       |convert hstore to array of|%% 'a=>foo, b=>    |             |
|hstore  |text[] |alternating keys and      |bar'::hstore       |{a,foo,b,bar}|
|        |       |values                    |                   |             |
|--------+-------+--------------------------+-------------------+-------------|
|%#      |       |convert hstore to         |%# 'a=>foo, b=>    |{{a,foo},    |
|hstore  |text[] |two-dimensional key/value |bar'::hstore       |{b,bar}}     |
|        |       |array                     |                   |             |
+-----------------------------------------------------------------------------+


    Note: As of PostgreSQL 8.4, the @> and @< operators can go deep:

    postgres=# SELECT 'a=>[1,2,{c=>3, x=>4}], c=>b'::hstore @> 'a=>[{c=>3}]';
     ?column?
    ----------
     t

    Prior to PostgreSQL 8.2, the containment operators @> and <@ were called @
    and ~, 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!

Table F-7. hstore Functions

+--------------------------------------------------------------------------------------------+
|            Function             |Return |  Description  |      Example       |   Result    |
|                                 | Type  |               |                    |             |
|---------------------------------+-------+---------------+--------------------+-------------|
|                                 |       |construct an   |                    |             |
|hstore(record)                   |hstore |hstore from a  |hstore(ROW(1,2))    |f1=>1,f2=>2  |
|                                 |       |record or row  |                    |             |
|---------------------------------+-------+---------------+--------------------+-------------|
|                                 |       |construct an   |                    |             |
|                                 |       |hstore from an |hstore(ARRAY        |             |
|                                 |       |array, which   |['a','1','b','2']) ||a=>"1", b=>  |
|hstore(text[])                   |hstore |may be either a|| hstore(ARRAY      |"2", c=>"3", |
|                                 |       |key/value      |[['c','3'],         |d=>"4"       |
|                                 |       |array, or a    |['d','4']])         |             |
|                                 |       |two-dimensional|                    |             |
|                                 |       |array          |                    |             |
|---------------------------------+-------+---------------+--------------------+-------------|
|                                 |       |construct an   |                    |             |
|                                 |       |hstore from    |hstore(ARRAY        |"a"=>"1","b" |
|hstore(text[], text[])           |hstore |separate key   |['a','b'], ARRAY    |=>"2"        |
|                                 |       |and value      |['1','2'])          |             |
|                                 |       |arrays         |                    |             |
|---------------------------------+-------+---------------+--------------------+-------------|
|                                 |       |make a nested  |hstore('xxx', 'foo=>|"xxx"=>{"bar"|
|hstore(text, hstore)             |hstore |hstore         |t, bar=>            |=>3.14, "foo"|
|                                 |       |               |3.14'::hstore)      |=>t}         |
|---------------------------------+-------+---------------+--------------------+-------------|
|                                 |       |make           |                    |             |
|hstore(text, text)               |hstore |single-item    |hstore('a', 'b')    |"a"=>"b"     |
|                                 |       |hstore         |                    |             |
|---------------------------------+-------+---------------+--------------------+-------------|
|                                 |       |make           |                    |             |
|hstore(text, numeric)            |hstore |single-item    |hstore('a', 3.14)   |"a"=>3.14    |
|                                 |       |hstore         |                    |             |
|---------------------------------+-------+---------------+--------------------+-------------|
|                                 |       |make           |                    |             |
|hstore(text, boolean)            |hstore |single-item    |hstore('a', true)   |"a"=>t       |
|                                 |       |hstore         |                    |             |
|---------------------------------+-------+---------------+--------------------+-------------|
|hstore(text)                     |hstore |make scalar    |hstore('foo')       |"foo"        |
|                                 |       |string hstore  |                    |             |
|---------------------------------+-------+---------------+--------------------+-------------|
|hstore(numeric)                  |hstore |make scalar    |hstore(42)          |42           |
|                                 |       |numeric hstore |                    |             |
|---------------------------------+-------+---------------+--------------------+-------------|
|hstore(boolean)                  |hstore |make scalar    |hstore(false)       |f            |
|                                 |       |boolean hstore |                    |             |
|---------------------------------+-------+---------------+--------------------+-------------|
|                                 |       |construct an   |array_to_hstore('   |[[1, 1, 4],  |
|array_to_hstore(anyarray)        |hstore |array hstore   |{{1,1,4},{23,3,5}}  |[23, 3, 5]]  |
|                                 |       |from an array  |'::int[])           |             |
|---------------------------------+-------+---------------+--------------------+-------------|
|                                 |       |get hstore's   |                    |             |
|akeys(hstore)                    |text[] |keys as an     |akeys('a=>1,b=>2')  |{a,b}        |
|                                 |       |array          |                    |             |
|---------------------------------+-------+---------------+--------------------+-------------|
|skeys(hstore)                    |setof  |get hstore's   |skeys('a=>1,b=>2')  |a            |
|                                 |text   |keys as a set  |                    |b            |
|---------------------------------+-------+---------------+--------------------+-------------|
|                                 |       |get hstore's   |                    |             |
|avals(hstore)                    |text[] |values as an   |avals('a=>1,b=>2')  |{1,2}        |
|                                 |       |array          |                    |             |
|---------------------------------+-------+---------------+--------------------+-------------|
|svals(hstore)                    |setof  |get hstore's   |svals('a=>1,b=>2')  |1            |
|                                 |text   |values as a set|                    |2            |
|---------------------------------+-------+---------------+--------------------+-------------|
|                                 |setof  |get hstore's   |hvals('a=>[1,2],b=> |[1, 2]       |
|hvals(hstore)                    |hstore |values as a set|{foo=>1}')          |"foo"=>1     |
|                                 |       |of hstores     |                    |             |
|---------------------------------+-------+---------------+--------------------+-------------|
|                                 |       |get hstore's   |                    |             |
|                                 |       |keys and values|hstore_to_array('a=>|             |
|hstore_to_array(hstore)          |text[] |as an array of |1,b=>2')            |{a,1,b,2}    |
|                                 |       |alternating    |                    |             |
|                                 |       |keys and values|                    |             |
|---------------------------------+-------+---------------+--------------------+-------------|
|                                 |       |get hstore's   |                    |             |
|                                 |       |keys and values|hstore_to_matrix('a |             |
|hstore_to_matrix(hstore)         |text[] |as a           |=>1,b=>2')          |{{a,1},{b,2}}|
|                                 |       |two-dimensional|                    |             |
|                                 |       |array          |                    |             |
|---------------------------------+-------+---------------+--------------------+-------------|
|                                 |       |               |                    |{ "b": true, |
|                                 |       |               |hstore_to_json('"a  |"c": null,   |
|                                 |       |get hstore as a|key"=>1, b=>t, c=>  |"d": 12345,  |
|hstore_to_json(hstore)           |json   |json value     |null, d=>12345, e=> |"e": 12345,  |
|                                 |       |               |012345, f=>1.234, g |"f": 1.234,  |
|                                 |       |               |=>2.345e+4')        |"g": 23450,  |
|                                 |       |               |                    |"a key": 1}  |
|---------------------------------+-------+---------------+--------------------+-------------|
|                                 |       |get hstore as a|                    |             |
|                                 |       |json value, but|                    |{ "b": true, |
|                                 |       |attempt to     |hstore_to_json_loose|"c": null,   |
|                                 |       |distinguish    |('"a key"=>1, b=>t, |"d": 12345,  |
|hstore_to_json_loose(hstore)     |json   |numerical and  |c=>null, d=>12345, e|"e": 12345,  |
|                                 |       |Boolean values |=>012345, f=>1.234, |"f": 1.234,  |
|                                 |       |so they are    |g=>2.345e+4')       |"g": 23450,  |
|                                 |       |unquoted in the|                    |"a key": 1}  |
|                                 |       |JSON           |                    |             |
|---------------------------------+-------+---------------+--------------------+-------------|
|                                 |       |               |json_to_hstore('{"a |"b"=>"t", "c"|
|                                 |       |               |key": "1", "b": "t",|=>NULL, "d"=>|
|                                 |       |               |"c": null, "d":     |"12345", "e" |
|json_to_hstore(json)             |hstore |get json as an |"12345", "e":       |=>"012345",  |
|                                 |       |hstore value   |"012345", "f":      |"f"=>"1.234",|
|                                 |       |               |"1.234", "g":       |"g"=>        |
|                                 |       |               |"2.345e+4"}')       |"2.345e+4",  |
|                                 |       |               |                    |"a key"=>"1" |
|---------------------------------+-------+---------------+--------------------+-------------|
|                                 |       |extract a      |slice('a=>1,b=>2,c=>|"b"=>2, "c"=>|
|slice(hstore, text[])            |hstore |subset of an   |3'::hstore, ARRAY   |3            |
|                                 |       |hstore         |['b','c','x'])      |             |
|---------------------------------+-------+---------------+--------------------+-------------|
|                                 |setof  |               |                    | key | value |
|                                 |(key   |get hstore's   |select * from each  |-----+-------|
|each(hstore)                     |text,  |keys and values|('a=>1,b=>2')       | a   | 1     |
|                                 |value  |as a set       |                    | b   | 2     |
|                                 |text)  |               |                    |             |
|---------------------------------+-------+---------------+--------------------+-------------|
|                                 |setof  |               |                    | key | value |
|                                 |(key   |get hstore's   |select * from       |-----+-------|
|each_hstore(hstore)              |text,  |keys and values|each_hstore('a=>1,b | a   | 1     |
|                                 |value  |as a set       |=>2')               | b   | 2     |
|                                 |text)  |               |                    |             |
|---------------------------------+-------+---------------+--------------------+-------------|
|exist(hstore,text)               |boolean|does hstore    |exist('a=>1','a')   |t            |
|                                 |       |contain key?   |                    |             |
|---------------------------------+-------+---------------+--------------------+-------------|
|                                 |       |does hstore    |                    |             |
|defined(hstore,text)             |boolean|contain        |defined('a=>        |f            |
|                                 |       |non-NULL value |NULL','a')          |             |
|                                 |       |for key?       |                    |             |
|---------------------------------+-------+---------------+--------------------+-------------|
|                                 |       |get the type of|                    |             |
|                                 |       |an hstore      |                    |             |
|                                 |       |value, one of  |                    |             |
|hstore_typeof(hstore)            |text   |hash, array,   |hstore_typeof('[1]')|array        |
|                                 |       |string,        |                    |             |
|                                 |       |numeric, bool, |                    |             |
|                                 |       |or null        |                    |             |
|---------------------------------+-------+---------------+--------------------+-------------|
|                                 |       |replace value  |replace('a=>1,b=>{c |"a"=>1, "b"=>|
|replace(hstore,text[],hstore)    |hstore |at the         |=>3,d=>[4,5,6]}     |{"c"=>3, "d" |
|                                 |       |specified path |'::hstore,'{b,d}',  |=>1}         |
|                                 |       |               |'1')                |             |
|---------------------------------+-------+---------------+--------------------+-------------|
|                                 |       |concatenate    |concat_path('b=>{c=>|"b"=>{"c"=>3,|
|concat_path(hstore,text[],hstore)|hstore |hstore value at|3,d=>[4,5,6]}       |"d"=>[4, 5,  |
|                                 |       |the specified  |'::hstore,'{b,d}',  |6, 1]}       |
|                                 |       |path           |'1')                |             |
|---------------------------------+-------+---------------+--------------------+-------------|
|                                 |       |delete pair    |delete('a=>1,b=>    |             |
|delete(hstore,text)              |hstore |with matching  |2','b')             |"a"=>1       |
|                                 |       |key            |                    |             |
|---------------------------------+-------+---------------+--------------------+-------------|
|                                 |       |delete pairs   |delete('a=>1,b=>2,c |             |
|delete(hstore,text[])            |hstore |with matching  |=>3',ARRAY['a','b'])|"c"=>3       |
|                                 |       |keys           |                    |             |
|---------------------------------+-------+---------------+--------------------+-------------|
|                                 |       |delete pairs   |delete('a=>1,b=>    |             |
|delete(hstore,hstore)            |hstore |matching those |2','a=>4,b=>        |"a"=>1       |
|                                 |       |in the second  |2'::hstore)         |             |
|                                 |       |argument       |                    |             |
|---------------------------------+-------+---------------+--------------------+-------------|
|                                 |       |replace fields |                    |             |
|populate_record(record,hstore)   |record |in record with |see Populating      |             |
|                                 |       |matching values|Records section     |             |
|                                 |       |from hstore    |                    |             |
|---------------------------------+-------+---------------+--------------------+-------------|
|                                 |       |Format an      |                    |             |
|                                 |       |hstore value as|                    |             |
|hstore_print                     |text   |text with      |see Printing section|             |
|(hstore,bool,bool,bool,bool,bool)|       |various        |                    |             |
|                                 |       |formatting     |                    |             |
|                                 |       |options        |                    |             |
+--------------------------------------------------------------------------------------------+


    Note: The function hstore_to_json is used when an hstore value is cast to
    json. Conversely, the function json_to_hstore is used when a json value is
    cast to hstore.

    Note: The function populate_record is actually declared with anyelement,
    not record, as its first argument, but it will reject non-record types with
    a run-time error.

    Note: The hstore_typeof function's null return value should not be confused
    with a SQL NULL. While calling hstore_typeof('null'::hstore) will return
    null, calling hstore_typeof(NULL::hstore) will return a SQL NULL.

Indexes
=======

hstore has GiST and GIN index support for the @>, ?, ?& and ?| operators. For
example:

CREATE INDEX hidx ON testhstore USING GIST (h);

CREATE INDEX hidx ON testhstore USING GIN (h);

GIN index opclass gin_hstore_hash_ops supports @> operator.

CREATE INDEX hidx ON testhstore USING GIN (h gin_hstore_hash_ops);

hstore also supports btree or hash indexes for the = operator. This allows
hstore columns to be declared UNIQUE, or to be used in GROUP BY, ORDER BY or
DISTINCT expressions. The sort ordering for hstore values is not particularly
useful, but these indexes may be useful for equivalence lookups. Create indexes
for = comparisons as follows:

CREATE INDEX hidx ON testhstore USING BTREE (h);

CREATE INDEX hidx ON testhstore USING HASH (h);

Printing
========

The hstore_print() function takes a single hstore value and formats it as text.
By default, the returned value is identical to the text format used to return
hstore values in queries. However, hstore_print() also accepts a number of
optional parameters, passed as boolean values, to format an hstore in various
ways. The parameters include:

Table F-8. hstore_print() Parameters

+------------------------------------------------------------------------------+
|     Parameter     |     Description     |       Example       |    Result    |
|-------------------+---------------------+---------------------+--------------|
|                   |                     |                     | hstore_print |
|                   |                     |                     |--------------|
|                   |                     |                     | "a"=>t,     +|
|                   |Adds newlines between|hstore_print('a=>t, t| "t"=>"f",   +|
|pretty_print       |values and indents   |=>"f", arr=>[1,2,"3"]| "arr"=>     +|
|                   |nested hashes and    |', pretty_print :=   | [           +|
|                   |arrays.              |true)                |     1,      +|
|                   |                     |                     |     2,      +|
|                   |                     |                     |     "3"     +|
|                   |                     |                     | ]            |
|-------------------+---------------------+---------------------+--------------|
|                   |Wraps arrays in curly|hstore_print('arr=>  |              |
|array_curly_braces |braces instead of    |[1,2,"3"]',          |"arr"=>{1, 2, |
|                   |brackets             |array_curly_braces :=|"3"}          |
|                   |                     |true)                |              |
|-------------------+---------------------+---------------------+--------------|
|                   |Wraps the root has   |hstore_print('arr=>  |              |
|root_hash_decorated|object, if three is  |[1,2,"3"]',          |{"arr"=>[1, 2,|
|                   |one, in curly braces |root_hash_decorated :|"3"]}         |
|                   |                     |= true)              |              |
|-------------------+---------------------+---------------------+--------------|
|                   |Returns the value as |hstore_print('arr=>  |"arr": [1, 2, |
|json               |a JSON string        |[1,2,"3"]', json :=  |"3"]          |
|                   |                     |true)                |              |
|-------------------+---------------------+---------------------+--------------|
|                   |Try to parse numbers |hstore_print('arr=>  |"arr"=>[1, 2, |
|loose              |and booleans         |[1,"2","t"]', loose :|t]            |
|                   |                     |= true)              |              |
+------------------------------------------------------------------------------+

These options can be combined for different effects. For example, to
pretty-print an hstore value with the root hash decorated and array curly
braces, simply pass all three values:

# SELECT hstore_print(
    'arr=>[1,2,"3"]',
    root_hash_decorated := true,
    array_curly_braces  := true,
    pretty_print        := true
);
 hstore_print
--------------
 {           +
     "arr"=> +
     {       +
         1,  +
         2,  +
         "3" +
     }       +
 }
(1 row)

Populating Records
==================

The populate_record() converts an hstore hash value to a pre-defined record
type. Pass any record value (even NULL) as the first argument and the hstore to
convert to that type as the second argument. At its simplest populate_record()
simply maps keys to column names and values to record values:

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)

But populate_record() supports more complicated records and nested hstore
values, as well. It makes an effort to convert from hstore data types to
PostgreSQL types, including arrays, json, and hstore values:

CREATE type stuff AS (i int, h hstore, a int[], j json);

SELECT * FROM populate_record(
    null::stuff,
    'i=>2, h=>{b=>3}, a=>{7,8,9}, j=>{a=>{1,2,3}}'
);
 i |   h    |    a    |        j
---+--------+---------+------------------
 2 | "b"=>3 | {7,8,9} | {"a": [1, 2, 3]}

Examples
========

Add a key, or update an existing key with a new value:

UPDATE tab SET h = h || hstore('c', '3');

Delete a key:

UPDATE tab SET h = delete(h, 'k1');

Convert a record to an hstore:

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)

Modify an existing record using the values from an hstore:

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)

Statistics
==========

The hstore 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.

Simple example:

SELECT * FROM each('aaa=>bq, b=>NULL, ""=>1');

Using a table:

SELECT (each(h)).key, (each(h)).value INTO stat FROM testhstore;

Online statistics:

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
...................

Compatibility
=============

The internal representation of hstore has been updated a couple of times in its
history. Data types and nested structures were added in PostgreSQL 9.4, while
capacity and improved index support were introduced in Postgrsql 9.0. These
changes present no obstacle for dump/restore upgrades since the text
representation (used in the dump) is unchanged. However, hstore values dumped
from 9.4 cannot be loaded into earlier versions of PostgreSQL if they contain
nested values or typed data.

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
UPDATE statement as follows:

UPDATE tablename SET hstorecol = hstorecol || '';

Another way to do it is:

ALTER TABLE tablename ALTER hstorecol TYPE hstore USING hstorecol || '';

The ALTER TABLE method requires an exclusive lock on the table, but does not
result in bloating the table with old row versions.

Authors
=======
        
Oleg Bartunov <oleg@sai.msu.su>, Moscow, Moscow University, Russia

Teodor Sigaev <teodor@sigaev.ru>, Moscow, Delta-Soft Ltd., Russia

Additional enhancements by Andrew Gierth <andrew@tao11.riddles.org.uk>, United
Kingdom