4 This is the nested hstore we developed for PostgreSQL 9.4, which community
5 decided not to include to release in favour to jsonb data type (read my
6 message about this thread
7 http://www.postgresql.org/message-id/CAF4Au4xO=sXBK4orGiS0AiXpZkH6f=QWptit6g9AmsEaH5CEAQ@mail.gmail.com
8 Nevertheless, we decided to save the patch for history. You can download it
9 from http://www.sigaev.ru/git/gitweb.cgi?p=hstore.git;a=summary
10 The patch is compatible with PostgreSQL 9.3+.
12 Slides about nested hstore:
14 * Binary storage for nested data structures and application to hstore data type,
15 PostgreSQL Conference Europe 2013, Oct 29-Nov 1, 2013, Dublin, Ireland.
16 http://www.sai.msu.su/~megera/postgres/talks/hstore-dublin-2013.pdf
17 * Nested hstore with arrays support, PGCon-2013 conference,
18 May 24, 2013, Ottawa, Canada
19 http://www.sai.msu.su/~megera/postgres/talks/hstore-pgcon-2013.pdf
22 Engine Yard has supported this work.
24 ==============================================================================
26 This module implements the hstore data type for storing arbitrarily nested key/
27 value pairs and arrays within a single PostgreSQL value. This can be useful in
28 various scenarios, such as rows with many attributes that are rarely examined,
29 or semi-structured data. Keys are strings, while values can be strings,
30 numbers, booleans, or NULL.
32 The hstore type is similar to the core json data type, but, in the current
33 implementation, differs in a few key ways:
35 * It's faster. hstore is stored in a binary representation, whereas json is
36 stored as text, and so needs to be parsed every time it's accessed.
38 * Better index support. hstore can be used in GiST and GIN indexes to allow
39 searches on keys or even key paths.
41 That said, hstore includes interfaces to transparently convert values to and
42 from json. These allow the best of both worlds: store and query hstore values,
43 but convert them to json when fetching them, for easy parsing in your client
46 hstore External Representation
47 ==============================
49 The text representation of an hstore, used for input and output, may be
50 formatted as scalar values, hash-like values, array-like values, and nested
51 array and hash values. Scalar values are simply strings, numeric values,
52 booleans, or NULL. Strings containing whitespace, commas, =s or >s must be
53 double-quoted. To include a double quote or a backslash in a key or value,
54 escape it with a backslash. Boolean values may be represented as true, t,
55 false, or f. Use quotation marks to represent these values as strings. The NULL
56 keyword is case-insensitive. Double-quote the NULL to treat it as the ordinary
57 string "NULL". Some examples:
59 =% SELECT 'foo'::hstore, '"hi \"bob\""'::hstore, '1.0'::hstore, 'true'::hstore, NULL::hstore;
60 hstore | hstore | hstore | hstore | hstore
61 --------+--------------+--------+--------+--------
62 "foo" | "hi \"bob\"" | 1.0 | t |
64 Arrays of values of any supported type may be constructed as square-bracketed
65 comma-separated lists. Some examples:
67 =% SELECT '[k,v]'::hstore, '[1.0, "hi there", false, null]'::hstore;
69 ------------+----------------------------
70 ["k", "v"] | [1.0, "hi there", f, NULL]
72 Hashes include zero or more key => value pairs separated by commas, optionally
73 bracketed by curly braces. Keys must be strings and may not be NULL; values may
74 be any hstore type, including NULL. Examples:
76 =% SELECT 'k => v'::hstore
77 -% , '{foo => "hi there"}'::hstore
78 -% , '{one => 1, two => 2.0, three => true, four => null}'::hstore;
79 hstore | hstore | hstore
80 ----------+-------------------+------------------------------------------------
81 "k"=>"v" | "foo"=>"hi there" | "one"=>1, "two"=>2.0, "four"=>NULL, "three"=>t
83 The order of the pairs is not significant (and may not be reproduced on
86 Each key in an hstore hash is unique. If you declare an hstore hash with
87 duplicate keys, only one will be stored in the hstore and there is no guarantee
88 as to which will be kept:
90 SELECT 'a=>1,a=>2'::hstore;
95 Hashes and arrays may be arbitrarily nested. In this case, brackets are
96 required for hash values. Here's an example adapted from the GeoJSON spec:
98 =% SET hstore.pretty_print=true;
101 "bbox" => [-180.0, -90.0, 180.0, 90.0],
105 [-180.0, 10.0], [20.0, 90.0], [180.0, -5.0], [-30.0, -90.0]
110 --------------------------
146 Note: Keep in mind that the hstore text format, when used for input,
147 applies before any required quoting or escaping. If you are passing an
148 hstore literal via a parameter, then no additional processing is needed.
149 But if you're passing it as a quoted literal constant, then any
150 single-quote characters and (depending on the setting of the
151 standard_conforming_strings configuration parameter) backslash characters
152 need to be escaped correctly.
154 On output, double quotes always surround keys and values, even when it's not
157 Output Format Configuration Parameters
158 ======================================
160 There are several configuration parameters that control the output formatting
163 hstore.pretty_print (boolean)
165 By default, the text representation of hstore values includes no whitespace
166 between the values it contains. Set hstore.pretty_print to true to add
167 newlines between values and to indent nested hashes and arrays.
169 hstore Operators and Functions
171 The operators provided by the hstore module are shown in Table F-6, the
172 functions in Table F-7.
174 Table F-6. hstore Operators
176 +-----------------------------------------------------------------------------+
177 |Operator|Returns| Description | Example | Result |
178 |--------+-------+--------------------------+-------------------+-------------|
179 |hstore |text |get value for key (NULL if|'a=>x, b=> |x |
180 |-> text | |not present) |y'::hstore -> 'a' | |
181 |--------+-------+--------------------------+-------------------+-------------|
182 |hstore | |get value for array index |'[foo,bar,baz] | |
183 |-> |text |(NULL if not present) |'::hstore -> 1 |bar |
185 |--------+-------+--------------------------+-------------------+-------------|
186 |hstore | |get numeric value for key |'a=>42.0, b=> | |
187 |^> text |numeric|(NULL if not numeric or |y'::hstore ^> 'a' |42.0 |
188 | | |not present) | | |
189 |--------+-------+--------------------------+-------------------+-------------|
190 |hstore | |get numeric value for |'[foo,null,44] | |
191 |^> |numeric|array index (NULL if not |'::hstore ^> 2 |44 |
192 |integer | |numeric or not present) | | |
193 |--------+-------+--------------------------+-------------------+-------------|
194 |hstore | |get boolean value for key |'a => 42.0, b => | |
195 |?> text |boolean|(NULL if not boolean or |true'::hstore ?> |true |
196 | | |not present) |'b' | |
197 |--------+-------+--------------------------+-------------------+-------------|
198 |hstore | |get boolean value for |'[false,null,44] | |
199 |?> |boolean|array index (NULL if not |'::hstore ?> 0 |false |
200 |integer | |boolean or not present) | | |
201 |--------+-------+--------------------------+-------------------+-------------|
202 |hstore | |get value for key path |'foo => {bar => | |
203 |#> text |text |(NULL if not present) |yellow}'::hstore #>|yellow |
204 |[] | | |'{foo,bar}' | |
205 |--------+-------+--------------------------+-------------------+-------------|
206 |hstore #| |get numeric value for key |'foo => {bar => 99}| |
207 |^> text |numeric|path (NULL if not numeric |'::hstore #^> ' |99 |
208 |[] | |or not present) |{foo,bar}' | |
209 |--------+-------+--------------------------+-------------------+-------------|
210 |hstore | |get boolean value for key |'foo => {bar => | |
211 |#?> text|boolean|path (NULL if not boolean |true}'::hstore #?> |true |
212 |[] | |or not present) |'{foo,bar}' | |
213 |--------+-------+--------------------------+-------------------+-------------|
214 |hstore |hstore |get hstore value for key |'foo => {bar => 99}|"bar"=>99 |
215 |%> text | |(NULL if not present) |'::hstore %> 'foo' | |
216 |--------+-------+--------------------------+-------------------+-------------|
217 |hstore | |get hstore value array |'[1, 2, {foo=>hi}] | |
218 |%> |hstore |index (NULL if not |'::hstore %> 2 |"foo"=>"hi" |
219 |integer | |present) | | |
220 |--------+-------+--------------------------+-------------------+-------------|
221 |hstore #| |get hstore value for key |'a => 1, b => {c =>| |
222 |%> text |hstore |path (NULL if not present)|[44,44]}'::hstore #|[44, 44] |
223 |[] | | |%> '{b,c}' | |
224 |--------+-------+--------------------------+-------------------+-------------|
225 |hstore | |get values for keys (NULL |'a=>x, b=>y, c=> | |
226 |-> text |text[] |if not present) |z'::hstore -> ARRAY|{z,x} |
227 |[] | | |['c','a'] | |
228 |--------+-------+--------------------------+-------------------+-------------|
229 |hstore || | |'a=>b, c=> |"a"=>"b", "c"|
230 || hstore|hstore |concatenate hstores |d'::hstore || 'c=> |=>"x", "d"=> |
231 | | | |x, d=>q'::hstore |"q" |
232 |--------+-------+--------------------------+-------------------+-------------|
233 |hstore ?|boolean|does hstore contain key? |'a=>1'::hstore ? |true |
235 |--------+-------+--------------------------+-------------------+-------------|
236 |hstore ?|boolean|does hstore contain array |'[a,b,c]'::hstore ?|true |
237 |integer | |index? |2 | |
238 |--------+-------+--------------------------+-------------------+-------------|
239 |hstore | |does hstore contain key |'[1, 2, {foo=>hi}] | |
240 |#? text |boolean|path? |'::hstore #? ' |true |
241 |[] | | |{2,foo}' | |
242 |--------+-------+--------------------------+-------------------+-------------|
243 |hstore ?|boolean|does hstore contain all |'a=>1,b=>2'::hstore|true |
244 |& text[]| |specified keys? |?& ARRAY['a','b'] | |
245 |--------+-------+--------------------------+-------------------+-------------|
246 |hstore ?|boolean|does hstore contain any of|'a=>1,b=>2'::hstore|true |
247 || text[]| |the specified keys? |?| ARRAY['b','c'] | |
248 |--------+-------+--------------------------+-------------------+-------------|
249 |hstore | |does left operand contain |'a=>b, b=>1, c=> | |
250 |@> |boolean|right? |NULL'::hstore @> 'b|true |
251 |hstore | | |=>1' | |
252 |--------+-------+--------------------------+-------------------+-------------|
253 |hstore | |is left operand contained |'a=>c'::hstore <@ | |
254 |<@ |boolean|in right? |'a=>b, b=>1, c=> |false |
255 |hstore | | |NULL' | |
256 |--------+-------+--------------------------+-------------------+-------------|
257 |hstore -| |delete key from left |'a=>1, b=>2, c=> |"a"=>1, "c"=>|
258 |text |hstore |operand |3'::hstore - |3 |
260 |--------+-------+--------------------------+-------------------+-------------|
261 |hstore -|hstore |delete index from left |'[2, 3, 4, 6, 8] |[2, 4, 6, 8] |
262 |integer | |operand |'::hstore - 1 | |
263 |--------+-------+--------------------------+-------------------+-------------|
264 |hstore -| |delete keys from left |'a=>1, b=>2, c=> | |
265 |text[] |hstore |operand |3'::hstore - ARRAY |"c"=>3 |
267 |--------+-------+--------------------------+-------------------+-------------|
268 |hstore -| |delete matching pairs from|'a=>1, b=>2, c=> |"a"=>1, "c"=>|
269 |hstore |hstore |left operand |3'::hstore - 'a=>4,|3 |
270 | | | |b=>2'::hstore | |
271 |--------+-------+--------------------------+-------------------+-------------|
272 |hstore #| |delete key path from left |'{a => {b => { c =>|"a"=>{"b"=> |
273 |- text[]|hstore |operand |[1,2]}}}'::hstore #|{"c"=>[2]}} |
274 | | | |- '{a,b,c,0}' | |
275 |--------+-------+--------------------------+-------------------+-------------|
276 |record #| |replace fields in record |see Examples | |
277 |= hstore|record |with matching values from |section | |
279 |--------+-------+--------------------------+-------------------+-------------|
280 |%% | |convert hstore to array of|%% 'a=>foo, b=> | |
281 |hstore |text[] |alternating keys and |bar'::hstore |{a,foo,b,bar}|
283 |--------+-------+--------------------------+-------------------+-------------|
284 |%# | |convert hstore to |%# 'a=>foo, b=> |{{a,foo}, |
285 |hstore |text[] |two-dimensional key/value |bar'::hstore |{b,bar}} |
287 +-----------------------------------------------------------------------------+
290 Note: As of PostgreSQL 8.4, the @> and @< operators can go deep:
292 postgres=# SELECT 'a=>[1,2,{c=>3, x=>4}], c=>b'::hstore @> 'a=>[{c=>3}]';
297 Prior to PostgreSQL 8.2, the containment operators @> and <@ were called @
298 and ~, respectively. These names are still available, but are deprecated
299 and will eventually be removed. Notice that the old names are reversed from
300 the convention formerly followed by the core geometric data types!
302 Table F-7. hstore Functions
304 +--------------------------------------------------------------------------------------------+
305 | Function |Return | Description | Example | Result |
307 |---------------------------------+-------+---------------+--------------------+-------------|
308 | | |construct an | | |
309 |hstore(record) |hstore |hstore from a |hstore(ROW(1,2)) |f1=>1,f2=>2 |
310 | | |record or row | | |
311 |---------------------------------+-------+---------------+--------------------+-------------|
312 | | |construct an | | |
313 | | |hstore from an |hstore(ARRAY | |
314 | | |array, which |['a','1','b','2']) ||a=>"1", b=> |
315 |hstore(text[]) |hstore |may be either a|| hstore(ARRAY |"2", c=>"3", |
316 | | |key/value |[['c','3'], |d=>"4" |
317 | | |array, or a |['d','4']]) | |
318 | | |two-dimensional| | |
320 |---------------------------------+-------+---------------+--------------------+-------------|
321 | | |construct an | | |
322 | | |hstore from |hstore(ARRAY |"a"=>"1","b" |
323 |hstore(text[], text[]) |hstore |separate key |['a','b'], ARRAY |=>"2" |
324 | | |and value |['1','2']) | |
326 |---------------------------------+-------+---------------+--------------------+-------------|
327 | | |make a nested |hstore('xxx', 'foo=>|"xxx"=>{"bar"|
328 |hstore(text, hstore) |hstore |hstore |t, bar=> |=>3.14, "foo"|
329 | | | |3.14'::hstore) |=>t} |
330 |---------------------------------+-------+---------------+--------------------+-------------|
332 |hstore(text, text) |hstore |single-item |hstore('a', 'b') |"a"=>"b" |
334 |---------------------------------+-------+---------------+--------------------+-------------|
336 |hstore(text, numeric) |hstore |single-item |hstore('a', 3.14) |"a"=>3.14 |
338 |---------------------------------+-------+---------------+--------------------+-------------|
340 |hstore(text, boolean) |hstore |single-item |hstore('a', true) |"a"=>t |
342 |---------------------------------+-------+---------------+--------------------+-------------|
343 |hstore(text) |hstore |make scalar |hstore('foo') |"foo" |
344 | | |string hstore | | |
345 |---------------------------------+-------+---------------+--------------------+-------------|
346 |hstore(numeric) |hstore |make scalar |hstore(42) |42 |
347 | | |numeric hstore | | |
348 |---------------------------------+-------+---------------+--------------------+-------------|
349 |hstore(boolean) |hstore |make scalar |hstore(false) |f |
350 | | |boolean hstore | | |
351 |---------------------------------+-------+---------------+--------------------+-------------|
352 | | |construct an |array_to_hstore(' |[[1, 1, 4], |
353 |array_to_hstore(anyarray) |hstore |array hstore |{{1,1,4},{23,3,5}} |[23, 3, 5]] |
354 | | |from an array |'::int[]) | |
355 |---------------------------------+-------+---------------+--------------------+-------------|
356 | | |get hstore's | | |
357 |akeys(hstore) |text[] |keys as an |akeys('a=>1,b=>2') |{a,b} |
359 |---------------------------------+-------+---------------+--------------------+-------------|
360 |skeys(hstore) |setof |get hstore's |skeys('a=>1,b=>2') |a |
361 | |text |keys as a set | |b |
362 |---------------------------------+-------+---------------+--------------------+-------------|
363 | | |get hstore's | | |
364 |avals(hstore) |text[] |values as an |avals('a=>1,b=>2') |{1,2} |
366 |---------------------------------+-------+---------------+--------------------+-------------|
367 |svals(hstore) |setof |get hstore's |svals('a=>1,b=>2') |1 |
368 | |text |values as a set| |2 |
369 |---------------------------------+-------+---------------+--------------------+-------------|
370 | |setof |get hstore's |hvals('a=>[1,2],b=> |[1, 2] |
371 |hvals(hstore) |hstore |values as a set|{foo=>1}') |"foo"=>1 |
372 | | |of hstores | | |
373 |---------------------------------+-------+---------------+--------------------+-------------|
374 | | |get hstore's | | |
375 | | |keys and values|hstore_to_array('a=>| |
376 |hstore_to_array(hstore) |text[] |as an array of |1,b=>2') |{a,1,b,2} |
377 | | |alternating | | |
378 | | |keys and values| | |
379 |---------------------------------+-------+---------------+--------------------+-------------|
380 | | |get hstore's | | |
381 | | |keys and values|hstore_to_matrix('a | |
382 |hstore_to_matrix(hstore) |text[] |as a |=>1,b=>2') |{{a,1},{b,2}}|
383 | | |two-dimensional| | |
385 |---------------------------------+-------+---------------+--------------------+-------------|
386 | | | | |{ "b": true, |
387 | | | |hstore_to_json('"a |"c": null, |
388 | | |get hstore as a|key"=>1, b=>t, c=> |"d": 12345, |
389 |hstore_to_json(hstore) |json |json value |null, d=>12345, e=> |"e": 12345, |
390 | | | |012345, f=>1.234, g |"f": 1.234, |
391 | | | |=>2.345e+4') |"g": 23450, |
392 | | | | |"a key": 1} |
393 |---------------------------------+-------+---------------+--------------------+-------------|
394 | | |get hstore as a| | |
395 | | |json value, but| |{ "b": true, |
396 | | |attempt to |hstore_to_json_loose|"c": null, |
397 | | |distinguish |('"a key"=>1, b=>t, |"d": 12345, |
398 |hstore_to_json_loose(hstore) |json |numerical and |c=>null, d=>12345, e|"e": 12345, |
399 | | |Boolean values |=>012345, f=>1.234, |"f": 1.234, |
400 | | |so they are |g=>2.345e+4') |"g": 23450, |
401 | | |unquoted in the| |"a key": 1} |
403 |---------------------------------+-------+---------------+--------------------+-------------|
404 | | | |json_to_hstore('{"a |"b"=>"t", "c"|
405 | | | |key": "1", "b": "t",|=>NULL, "d"=>|
406 | | | |"c": null, "d": |"12345", "e" |
407 |json_to_hstore(json) |hstore |get json as an |"12345", "e": |=>"012345", |
408 | | |hstore value |"012345", "f": |"f"=>"1.234",|
409 | | | |"1.234", "g": |"g"=> |
410 | | | |"2.345e+4"}') |"2.345e+4", |
411 | | | | |"a key"=>"1" |
412 |---------------------------------+-------+---------------+--------------------+-------------|
413 | | |extract a |slice('a=>1,b=>2,c=>|"b"=>2, "c"=>|
414 |slice(hstore, text[]) |hstore |subset of an |3'::hstore, ARRAY |3 |
415 | | |hstore |['b','c','x']) | |
416 |---------------------------------+-------+---------------+--------------------+-------------|
417 | |setof | | | key | value |
418 | |(key |get hstore's |select * from each |-----+-------|
419 |each(hstore) |text, |keys and values|('a=>1,b=>2') | a | 1 |
420 | |value |as a set | | b | 2 |
422 |---------------------------------+-------+---------------+--------------------+-------------|
423 | |setof | | | key | value |
424 | |(key |get hstore's |select * from |-----+-------|
425 |each_hstore(hstore) |text, |keys and values|each_hstore('a=>1,b | a | 1 |
426 | |value |as a set |=>2') | b | 2 |
428 |---------------------------------+-------+---------------+--------------------+-------------|
429 |exist(hstore,text) |boolean|does hstore |exist('a=>1','a') |t |
430 | | |contain key? | | |
431 |---------------------------------+-------+---------------+--------------------+-------------|
432 | | |does hstore | | |
433 |defined(hstore,text) |boolean|contain |defined('a=> |f |
434 | | |non-NULL value |NULL','a') | |
436 |---------------------------------+-------+---------------+--------------------+-------------|
437 | | |get the type of| | |
439 | | |value, one of | | |
440 |hstore_typeof(hstore) |text |hash, array, |hstore_typeof('[1]')|array |
442 | | |numeric, bool, | | |
444 |---------------------------------+-------+---------------+--------------------+-------------|
445 | | |replace value |replace('a=>1,b=>{c |"a"=>1, "b"=>|
446 |replace(hstore,text[],hstore) |hstore |at the |=>3,d=>[4,5,6]} |{"c"=>3, "d" |
447 | | |specified path |'::hstore,'{b,d}', |=>1} |
449 |---------------------------------+-------+---------------+--------------------+-------------|
450 | | |concatenate |concat_path('b=>{c=>|"b"=>{"c"=>3,|
451 |concat_path(hstore,text[],hstore)|hstore |hstore value at|3,d=>[4,5,6]} |"d"=>[4, 5, |
452 | | |the specified |'::hstore,'{b,d}', |6, 1]} |
454 |---------------------------------+-------+---------------+--------------------+-------------|
455 | | |delete pair |delete('a=>1,b=> | |
456 |delete(hstore,text) |hstore |with matching |2','b') |"a"=>1 |
458 |---------------------------------+-------+---------------+--------------------+-------------|
459 | | |delete pairs |delete('a=>1,b=>2,c | |
460 |delete(hstore,text[]) |hstore |with matching |=>3',ARRAY['a','b'])|"c"=>3 |
462 |---------------------------------+-------+---------------+--------------------+-------------|
463 | | |delete pairs |delete('a=>1,b=> | |
464 |delete(hstore,hstore) |hstore |matching those |2','a=>4,b=> |"a"=>1 |
465 | | |in the second |2'::hstore) | |
467 |---------------------------------+-------+---------------+--------------------+-------------|
468 | | |replace fields | | |
469 |populate_record(record,hstore) |record |in record with |see Populating | |
470 | | |matching values|Records section | |
471 | | |from hstore | | |
472 |---------------------------------+-------+---------------+--------------------+-------------|
474 | | |hstore value as| | |
475 |hstore_print |text |text with |see Printing section| |
476 |(hstore,bool,bool,bool,bool,bool)| |various | | |
477 | | |formatting | | |
479 +--------------------------------------------------------------------------------------------+
482 Note: The function hstore_to_json is used when an hstore value is cast to
483 json. Conversely, the function json_to_hstore is used when a json value is
486 Note: The function populate_record is actually declared with anyelement,
487 not record, as its first argument, but it will reject non-record types with
490 Note: The hstore_typeof function's null return value should not be confused
491 with a SQL NULL. While calling hstore_typeof('null'::hstore) will return
492 null, calling hstore_typeof(NULL::hstore) will return a SQL NULL.
497 hstore has GiST and GIN index support for the @>, ?, ?& and ?| operators. For
500 CREATE INDEX hidx ON testhstore USING GIST (h);
502 CREATE INDEX hidx ON testhstore USING GIN (h);
504 GIN index opclass gin_hstore_hash_ops supports @> operator.
506 CREATE INDEX hidx ON testhstore USING GIN (h gin_hstore_hash_ops);
508 hstore also supports btree or hash indexes for the = operator. This allows
509 hstore columns to be declared UNIQUE, or to be used in GROUP BY, ORDER BY or
510 DISTINCT expressions. The sort ordering for hstore values is not particularly
511 useful, but these indexes may be useful for equivalence lookups. Create indexes
512 for = comparisons as follows:
514 CREATE INDEX hidx ON testhstore USING BTREE (h);
516 CREATE INDEX hidx ON testhstore USING HASH (h);
521 The hstore_print() function takes a single hstore value and formats it as text.
522 By default, the returned value is identical to the text format used to return
523 hstore values in queries. However, hstore_print() also accepts a number of
524 optional parameters, passed as boolean values, to format an hstore in various
525 ways. The parameters include:
527 Table F-8. hstore_print() Parameters
529 +------------------------------------------------------------------------------+
530 | Parameter | Description | Example | Result |
531 |-------------------+---------------------+---------------------+--------------|
532 | | | | hstore_print |
533 | | | |--------------|
535 | |Adds newlines between|hstore_print('a=>t, t| "t"=>"f", +|
536 |pretty_print |values and indents |=>"f", arr=>[1,2,"3"]| "arr"=> +|
537 | |nested hashes and |', pretty_print := | [ +|
538 | |arrays. |true) | 1, +|
542 |-------------------+---------------------+---------------------+--------------|
543 | |Wraps arrays in curly|hstore_print('arr=> | |
544 |array_curly_braces |braces instead of |[1,2,"3"]', |"arr"=>{1, 2, |
545 | |brackets |array_curly_braces :=|"3"} |
547 |-------------------+---------------------+---------------------+--------------|
548 | |Wraps the root has |hstore_print('arr=> | |
549 |root_hash_decorated|object, if three is |[1,2,"3"]', |{"arr"=>[1, 2,|
550 | |one, in curly braces |root_hash_decorated :|"3"]} |
552 |-------------------+---------------------+---------------------+--------------|
553 | |Returns the value as |hstore_print('arr=> |"arr": [1, 2, |
554 |json |a JSON string |[1,2,"3"]', json := |"3"] |
556 |-------------------+---------------------+---------------------+--------------|
557 | |Try to parse numbers |hstore_print('arr=> |"arr"=>[1, 2, |
558 |loose |and booleans |[1,"2","t"]', loose :|t] |
560 +------------------------------------------------------------------------------+
562 These options can be combined for different effects. For example, to
563 pretty-print an hstore value with the root hash decorated and array curly
564 braces, simply pass all three values:
566 # SELECT hstore_print(
568 root_hash_decorated := true,
569 array_curly_braces := true,
587 The populate_record() converts an hstore hash value to a pre-defined record
588 type. Pass any record value (even NULL) as the first argument and the hstore to
589 convert to that type as the second argument. At its simplest populate_record()
590 simply maps keys to column names and values to record values:
592 CREATE TABLE test (col1 integer, col2 text, col3 text);
594 SELECT * FROM populate_record(
596 '"col1"=>"456", "col2"=>"zzz"'
603 But populate_record() supports more complicated records and nested hstore
604 values, as well. It makes an effort to convert from hstore data types to
605 PostgreSQL types, including arrays, json, and hstore values:
607 CREATE type stuff AS (i int, h hstore, a int[], j json);
609 SELECT * FROM populate_record(
611 'i=>2, h=>{b=>3}, a=>{7,8,9}, j=>{a=>{1,2,3}}'
614 ---+--------+---------+------------------
615 2 | "b"=>3 | {7,8,9} | {"a": [1, 2, 3]}
620 Add a key, or update an existing key with a new value:
622 UPDATE tab SET h = h || hstore('c', '3');
626 UPDATE tab SET h = delete(h, 'k1');
628 Convert a record to an hstore:
630 CREATE TABLE test (col1 integer, col2 text, col3 text);
631 INSERT INTO test VALUES (123, 'foo', 'bar');
633 SELECT hstore(t) FROM test AS t;
635 ---------------------------------------------
636 "col1"=>123, "col2"=>"foo", "col3"=>"bar"
639 Modify an existing record using the values from an hstore:
641 CREATE TABLE test (col1 integer, col2 text, col3 text);
642 INSERT INTO test VALUES (123, 'foo', 'bar');
644 SELECT (r).* FROM (SELECT t #= '"col3"=>"baz"' AS r FROM test t) s;
653 The hstore type, because of its intrinsic liberality, could contain a lot of
654 different keys. Checking for valid keys is the task of the application. The
655 following examples demonstrate several techniques for checking keys and
656 obtaining statistics.
660 SELECT * FROM each('aaa=>bq, b=>NULL, ""=>1');
664 SELECT (each(h)).key, (each(h)).value INTO stat FROM testhstore;
668 SELECT key, count(*) FROM
669 (SELECT (each(h)).key FROM testhstore) AS stat
671 ORDER BY count DESC, key;
688 The internal representation of hstore has been updated a couple of times in its
689 history. Data types and nested structures were added in PostgreSQL 9.4, while
690 capacity and improved index support were introduced in Postgrsql 9.0. These
691 changes present no obstacle for dump/restore upgrades since the text
692 representation (used in the dump) is unchanged. However, hstore values dumped
693 from 9.4 cannot be loaded into earlier versions of PostgreSQL if they contain
694 nested values or typed data.
696 In the event of a binary upgrade, upward compatibility is maintained by having
697 the new code recognize old-format data. This will entail a slight performance
698 penalty when processing data that has not yet been modified by the new code. It
699 is possible to force an upgrade of all values in a table column by doing an
700 UPDATE statement as follows:
702 UPDATE tablename SET hstorecol = hstorecol || '';
704 Another way to do it is:
706 ALTER TABLE tablename ALTER hstorecol TYPE hstore USING hstorecol || '';
708 The ALTER TABLE method requires an exclusive lock on the table, but does not
709 result in bloating the table with old row versions.
714 Oleg Bartunov <oleg@sai.msu.su>, Moscow, Moscow University, Russia
716 Teodor Sigaev <teodor@sigaev.ru>, Moscow, Delta-Soft Ltd., Russia
718 Additional enhancements by Andrew Gierth <andrew@tao11.riddles.org.uk>, United