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 , Moscow, Moscow University, Russia Teodor Sigaev , Moscow, Delta-Soft Ltd., Russia Additional enhancements by Andrew Gierth , United Kingdom