--- /dev/null
+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
projects / hstore.git / commitdiff