| CREATE EXTENSION hstore; |
| -- Check whether any of our opclasses fail amvalidate |
| SELECT amname, opcname |
| FROM pg_opclass opc LEFT JOIN pg_am am ON am.oid = opcmethod |
| WHERE opc.oid >= 16384 AND NOT amvalidate(opc.oid); |
| amname | opcname |
| --------+--------- |
| (0 rows) |
| |
| set escape_string_warning=off; |
| --hstore; |
| select ''::hstore; |
| hstore |
| -------- |
| |
| (1 row) |
| |
| select 'a=>b'::hstore; |
| hstore |
| ---------- |
| "a"=>"b" |
| (1 row) |
| |
| select ' a=>b'::hstore; |
| hstore |
| ---------- |
| "a"=>"b" |
| (1 row) |
| |
| select 'a =>b'::hstore; |
| hstore |
| ---------- |
| "a"=>"b" |
| (1 row) |
| |
| select 'a=>b '::hstore; |
| hstore |
| ---------- |
| "a"=>"b" |
| (1 row) |
| |
| select 'a=> b'::hstore; |
| hstore |
| ---------- |
| "a"=>"b" |
| (1 row) |
| |
| select '"a"=>"b"'::hstore; |
| hstore |
| ---------- |
| "a"=>"b" |
| (1 row) |
| |
| select ' "a"=>"b"'::hstore; |
| hstore |
| ---------- |
| "a"=>"b" |
| (1 row) |
| |
| select '"a" =>"b"'::hstore; |
| hstore |
| ---------- |
| "a"=>"b" |
| (1 row) |
| |
| select '"a"=>"b" '::hstore; |
| hstore |
| ---------- |
| "a"=>"b" |
| (1 row) |
| |
| select '"a"=> "b"'::hstore; |
| hstore |
| ---------- |
| "a"=>"b" |
| (1 row) |
| |
| select 'aa=>bb'::hstore; |
| hstore |
| ------------ |
| "aa"=>"bb" |
| (1 row) |
| |
| select ' aa=>bb'::hstore; |
| hstore |
| ------------ |
| "aa"=>"bb" |
| (1 row) |
| |
| select 'aa =>bb'::hstore; |
| hstore |
| ------------ |
| "aa"=>"bb" |
| (1 row) |
| |
| select 'aa=>bb '::hstore; |
| hstore |
| ------------ |
| "aa"=>"bb" |
| (1 row) |
| |
| select 'aa=> bb'::hstore; |
| hstore |
| ------------ |
| "aa"=>"bb" |
| (1 row) |
| |
| select '"aa"=>"bb"'::hstore; |
| hstore |
| ------------ |
| "aa"=>"bb" |
| (1 row) |
| |
| select ' "aa"=>"bb"'::hstore; |
| hstore |
| ------------ |
| "aa"=>"bb" |
| (1 row) |
| |
| select '"aa" =>"bb"'::hstore; |
| hstore |
| ------------ |
| "aa"=>"bb" |
| (1 row) |
| |
| select '"aa"=>"bb" '::hstore; |
| hstore |
| ------------ |
| "aa"=>"bb" |
| (1 row) |
| |
| select '"aa"=> "bb"'::hstore; |
| hstore |
| ------------ |
| "aa"=>"bb" |
| (1 row) |
| |
| select 'aa=>bb, cc=>dd'::hstore; |
| hstore |
| ------------------------ |
| "aa"=>"bb", "cc"=>"dd" |
| (1 row) |
| |
| select 'aa=>bb , cc=>dd'::hstore; |
| hstore |
| ------------------------ |
| "aa"=>"bb", "cc"=>"dd" |
| (1 row) |
| |
| select 'aa=>bb ,cc=>dd'::hstore; |
| hstore |
| ------------------------ |
| "aa"=>"bb", "cc"=>"dd" |
| (1 row) |
| |
| select 'aa=>bb, "cc"=>dd'::hstore; |
| hstore |
| ------------------------ |
| "aa"=>"bb", "cc"=>"dd" |
| (1 row) |
| |
| select 'aa=>bb , "cc"=>dd'::hstore; |
| hstore |
| ------------------------ |
| "aa"=>"bb", "cc"=>"dd" |
| (1 row) |
| |
| select 'aa=>bb ,"cc"=>dd'::hstore; |
| hstore |
| ------------------------ |
| "aa"=>"bb", "cc"=>"dd" |
| (1 row) |
| |
| select 'aa=>"bb", cc=>dd'::hstore; |
| hstore |
| ------------------------ |
| "aa"=>"bb", "cc"=>"dd" |
| (1 row) |
| |
| select 'aa=>"bb" , cc=>dd'::hstore; |
| hstore |
| ------------------------ |
| "aa"=>"bb", "cc"=>"dd" |
| (1 row) |
| |
| select 'aa=>"bb" ,cc=>dd'::hstore; |
| hstore |
| ------------------------ |
| "aa"=>"bb", "cc"=>"dd" |
| (1 row) |
| |
| select 'aa=>null'::hstore; |
| hstore |
| ------------ |
| "aa"=>NULL |
| (1 row) |
| |
| select 'aa=>NuLl'::hstore; |
| hstore |
| ------------ |
| "aa"=>NULL |
| (1 row) |
| |
| select 'aa=>"NuLl"'::hstore; |
| hstore |
| -------------- |
| "aa"=>"NuLl" |
| (1 row) |
| |
| select e'\\=a=>q=w'::hstore; |
| hstore |
| ------------- |
| "=a"=>"q=w" |
| (1 row) |
| |
| select e'"=a"=>q\\=w'::hstore; |
| hstore |
| ------------- |
| "=a"=>"q=w" |
| (1 row) |
| |
| select e'"\\"a"=>q>w'::hstore; |
| hstore |
| -------------- |
| "\"a"=>"q>w" |
| (1 row) |
| |
| select e'\\"a=>q"w'::hstore; |
| hstore |
| --------------- |
| "\"a"=>"q\"w" |
| (1 row) |
| |
| select ''::hstore; |
| hstore |
| -------- |
| |
| (1 row) |
| |
| select ' '::hstore; |
| hstore |
| -------- |
| |
| (1 row) |
| |
| -- -> operator |
| select 'aa=>b, c=>d , b=>16'::hstore->'c'; |
| ?column? |
| ---------- |
| d |
| (1 row) |
| |
| select 'aa=>b, c=>d , b=>16'::hstore->'b'; |
| ?column? |
| ---------- |
| 16 |
| (1 row) |
| |
| select 'aa=>b, c=>d , b=>16'::hstore->'aa'; |
| ?column? |
| ---------- |
| b |
| (1 row) |
| |
| select ('aa=>b, c=>d , b=>16'::hstore->'gg') is null; |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| select ('aa=>NULL, c=>d , b=>16'::hstore->'aa') is null; |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| select ('aa=>"NULL", c=>d , b=>16'::hstore->'aa') is null; |
| ?column? |
| ---------- |
| f |
| (1 row) |
| |
| -- -> array operator |
| select 'aa=>"NULL", c=>d , b=>16'::hstore -> ARRAY['aa','c']; |
| ?column? |
| ------------ |
| {"NULL",d} |
| (1 row) |
| |
| select 'aa=>"NULL", c=>d , b=>16'::hstore -> ARRAY['c','aa']; |
| ?column? |
| ------------ |
| {d,"NULL"} |
| (1 row) |
| |
| select 'aa=>NULL, c=>d , b=>16'::hstore -> ARRAY['aa','c',null]; |
| ?column? |
| --------------- |
| {NULL,d,NULL} |
| (1 row) |
| |
| select 'aa=>1, c=>3, b=>2, d=>4'::hstore -> ARRAY[['b','d'],['aa','c']]; |
| ?column? |
| --------------- |
| {{2,4},{1,3}} |
| (1 row) |
| |
| -- exists/defined |
| select exist('a=>NULL, b=>qq', 'a'); |
| exist |
| ------- |
| t |
| (1 row) |
| |
| select exist('a=>NULL, b=>qq', 'b'); |
| exist |
| ------- |
| t |
| (1 row) |
| |
| select exist('a=>NULL, b=>qq', 'c'); |
| exist |
| ------- |
| f |
| (1 row) |
| |
| select exist('a=>"NULL", b=>qq', 'a'); |
| exist |
| ------- |
| t |
| (1 row) |
| |
| select defined('a=>NULL, b=>qq', 'a'); |
| defined |
| --------- |
| f |
| (1 row) |
| |
| select defined('a=>NULL, b=>qq', 'b'); |
| defined |
| --------- |
| t |
| (1 row) |
| |
| select defined('a=>NULL, b=>qq', 'c'); |
| defined |
| --------- |
| f |
| (1 row) |
| |
| select defined('a=>"NULL", b=>qq', 'a'); |
| defined |
| --------- |
| t |
| (1 row) |
| |
| select hstore 'a=>NULL, b=>qq' ? 'a'; |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| select hstore 'a=>NULL, b=>qq' ? 'b'; |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| select hstore 'a=>NULL, b=>qq' ? 'c'; |
| ?column? |
| ---------- |
| f |
| (1 row) |
| |
| select hstore 'a=>"NULL", b=>qq' ? 'a'; |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| select hstore 'a=>NULL, b=>qq' ?| ARRAY['a','b']; |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| select hstore 'a=>NULL, b=>qq' ?| ARRAY['b','a']; |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| select hstore 'a=>NULL, b=>qq' ?| ARRAY['c','a']; |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| select hstore 'a=>NULL, b=>qq' ?| ARRAY['c','d']; |
| ?column? |
| ---------- |
| f |
| (1 row) |
| |
| select hstore 'a=>NULL, b=>qq' ?| '{}'::text[]; |
| ?column? |
| ---------- |
| f |
| (1 row) |
| |
| select hstore 'a=>NULL, b=>qq' ?& ARRAY['a','b']; |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| select hstore 'a=>NULL, b=>qq' ?& ARRAY['b','a']; |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| select hstore 'a=>NULL, b=>qq' ?& ARRAY['c','a']; |
| ?column? |
| ---------- |
| f |
| (1 row) |
| |
| select hstore 'a=>NULL, b=>qq' ?& ARRAY['c','d']; |
| ?column? |
| ---------- |
| f |
| (1 row) |
| |
| select hstore 'a=>NULL, b=>qq' ?& '{}'::text[]; |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| -- delete |
| select delete('a=>1 , b=>2, c=>3'::hstore, 'a'); |
| delete |
| -------------------- |
| "b"=>"2", "c"=>"3" |
| (1 row) |
| |
| select delete('a=>null , b=>2, c=>3'::hstore, 'a'); |
| delete |
| -------------------- |
| "b"=>"2", "c"=>"3" |
| (1 row) |
| |
| select delete('a=>1 , b=>2, c=>3'::hstore, 'b'); |
| delete |
| -------------------- |
| "a"=>"1", "c"=>"3" |
| (1 row) |
| |
| select delete('a=>1 , b=>2, c=>3'::hstore, 'c'); |
| delete |
| -------------------- |
| "a"=>"1", "b"=>"2" |
| (1 row) |
| |
| select delete('a=>1 , b=>2, c=>3'::hstore, 'd'); |
| delete |
| ------------------------------ |
| "a"=>"1", "b"=>"2", "c"=>"3" |
| (1 row) |
| |
| select 'a=>1 , b=>2, c=>3'::hstore - 'a'::text; |
| ?column? |
| -------------------- |
| "b"=>"2", "c"=>"3" |
| (1 row) |
| |
| select 'a=>null , b=>2, c=>3'::hstore - 'a'::text; |
| ?column? |
| -------------------- |
| "b"=>"2", "c"=>"3" |
| (1 row) |
| |
| select 'a=>1 , b=>2, c=>3'::hstore - 'b'::text; |
| ?column? |
| -------------------- |
| "a"=>"1", "c"=>"3" |
| (1 row) |
| |
| select 'a=>1 , b=>2, c=>3'::hstore - 'c'::text; |
| ?column? |
| -------------------- |
| "a"=>"1", "b"=>"2" |
| (1 row) |
| |
| select 'a=>1 , b=>2, c=>3'::hstore - 'd'::text; |
| ?column? |
| ------------------------------ |
| "a"=>"1", "b"=>"2", "c"=>"3" |
| (1 row) |
| |
| select pg_column_size('a=>1 , b=>2, c=>3'::hstore - 'b'::text) |
| = pg_column_size('a=>1, b=>2'::hstore); |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| -- delete (array) |
| select delete('a=>1 , b=>2, c=>3'::hstore, ARRAY['d','e']); |
| delete |
| ------------------------------ |
| "a"=>"1", "b"=>"2", "c"=>"3" |
| (1 row) |
| |
| select delete('a=>1 , b=>2, c=>3'::hstore, ARRAY['d','b']); |
| delete |
| -------------------- |
| "a"=>"1", "c"=>"3" |
| (1 row) |
| |
| select delete('a=>1 , b=>2, c=>3'::hstore, ARRAY['a','c']); |
| delete |
| ---------- |
| "b"=>"2" |
| (1 row) |
| |
| select delete('a=>1 , b=>2, c=>3'::hstore, ARRAY[['b'],['c'],['a']]); |
| delete |
| -------- |
| |
| (1 row) |
| |
| select delete('a=>1 , b=>2, c=>3'::hstore, '{}'::text[]); |
| delete |
| ------------------------------ |
| "a"=>"1", "b"=>"2", "c"=>"3" |
| (1 row) |
| |
| select 'a=>1 , b=>2, c=>3'::hstore - ARRAY['d','e']; |
| ?column? |
| ------------------------------ |
| "a"=>"1", "b"=>"2", "c"=>"3" |
| (1 row) |
| |
| select 'a=>1 , b=>2, c=>3'::hstore - ARRAY['d','b']; |
| ?column? |
| -------------------- |
| "a"=>"1", "c"=>"3" |
| (1 row) |
| |
| select 'a=>1 , b=>2, c=>3'::hstore - ARRAY['a','c']; |
| ?column? |
| ---------- |
| "b"=>"2" |
| (1 row) |
| |
| select 'a=>1 , b=>2, c=>3'::hstore - ARRAY[['b'],['c'],['a']]; |
| ?column? |
| ---------- |
| |
| (1 row) |
| |
| select 'a=>1 , b=>2, c=>3'::hstore - '{}'::text[]; |
| ?column? |
| ------------------------------ |
| "a"=>"1", "b"=>"2", "c"=>"3" |
| (1 row) |
| |
| select pg_column_size('a=>1 , b=>2, c=>3'::hstore - ARRAY['a','c']) |
| = pg_column_size('b=>2'::hstore); |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| select pg_column_size('a=>1 , b=>2, c=>3'::hstore - '{}'::text[]) |
| = pg_column_size('a=>1, b=>2, c=>3'::hstore); |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| -- delete (hstore) |
| select delete('aa=>1 , b=>2, c=>3'::hstore, 'aa=>4, b=>2'::hstore); |
| delete |
| --------------------- |
| "c"=>"3", "aa"=>"1" |
| (1 row) |
| |
| select delete('aa=>1 , b=>2, c=>3'::hstore, 'aa=>NULL, c=>3'::hstore); |
| delete |
| --------------------- |
| "b"=>"2", "aa"=>"1" |
| (1 row) |
| |
| select delete('aa=>1 , b=>2, c=>3'::hstore, 'aa=>1, b=>2, c=>3'::hstore); |
| delete |
| -------- |
| |
| (1 row) |
| |
| select delete('aa=>1 , b=>2, c=>3'::hstore, 'b=>2'::hstore); |
| delete |
| --------------------- |
| "c"=>"3", "aa"=>"1" |
| (1 row) |
| |
| select delete('aa=>1 , b=>2, c=>3'::hstore, ''::hstore); |
| delete |
| ------------------------------- |
| "b"=>"2", "c"=>"3", "aa"=>"1" |
| (1 row) |
| |
| select 'aa=>1 , b=>2, c=>3'::hstore - 'aa=>4, b=>2'::hstore; |
| ?column? |
| --------------------- |
| "c"=>"3", "aa"=>"1" |
| (1 row) |
| |
| select 'aa=>1 , b=>2, c=>3'::hstore - 'aa=>NULL, c=>3'::hstore; |
| ?column? |
| --------------------- |
| "b"=>"2", "aa"=>"1" |
| (1 row) |
| |
| select 'aa=>1 , b=>2, c=>3'::hstore - 'aa=>1, b=>2, c=>3'::hstore; |
| ?column? |
| ---------- |
| |
| (1 row) |
| |
| select 'aa=>1 , b=>2, c=>3'::hstore - 'b=>2'::hstore; |
| ?column? |
| --------------------- |
| "c"=>"3", "aa"=>"1" |
| (1 row) |
| |
| select 'aa=>1 , b=>2, c=>3'::hstore - ''::hstore; |
| ?column? |
| ------------------------------- |
| "b"=>"2", "c"=>"3", "aa"=>"1" |
| (1 row) |
| |
| select pg_column_size('a=>1 , b=>2, c=>3'::hstore - 'b=>2'::hstore) |
| = pg_column_size('a=>1, c=>3'::hstore); |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| select pg_column_size('a=>1 , b=>2, c=>3'::hstore - ''::hstore) |
| = pg_column_size('a=>1, b=>2, c=>3'::hstore); |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| -- || |
| select 'aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>f'; |
| ?column? |
| ------------------------------------------- |
| "b"=>"g", "aa"=>"1", "cq"=>"l", "fg"=>"f" |
| (1 row) |
| |
| select 'aa=>1 , b=>2, cq=>3'::hstore || 'aq=>l'; |
| ?column? |
| ------------------------------------------- |
| "b"=>"2", "aa"=>"1", "aq"=>"l", "cq"=>"3" |
| (1 row) |
| |
| select 'aa=>1 , b=>2, cq=>3'::hstore || 'aa=>l'; |
| ?column? |
| -------------------------------- |
| "b"=>"2", "aa"=>"l", "cq"=>"3" |
| (1 row) |
| |
| select 'aa=>1 , b=>2, cq=>3'::hstore || ''; |
| ?column? |
| -------------------------------- |
| "b"=>"2", "aa"=>"1", "cq"=>"3" |
| (1 row) |
| |
| select ''::hstore || 'cq=>l, b=>g, fg=>f'; |
| ?column? |
| -------------------------------- |
| "b"=>"g", "cq"=>"l", "fg"=>"f" |
| (1 row) |
| |
| select pg_column_size(''::hstore || ''::hstore) = pg_column_size(''::hstore); |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| select pg_column_size('aa=>1'::hstore || 'b=>2'::hstore) |
| = pg_column_size('aa=>1, b=>2'::hstore); |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| select pg_column_size('aa=>1, b=>2'::hstore || ''::hstore) |
| = pg_column_size('aa=>1, b=>2'::hstore); |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| select pg_column_size(''::hstore || 'aa=>1, b=>2'::hstore) |
| = pg_column_size('aa=>1, b=>2'::hstore); |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| -- hstore(text,text) |
| select 'a=>g, b=>c'::hstore || hstore('asd', 'gf'); |
| ?column? |
| --------------------------------- |
| "a"=>"g", "b"=>"c", "asd"=>"gf" |
| (1 row) |
| |
| select 'a=>g, b=>c'::hstore || hstore('b', 'gf'); |
| ?column? |
| --------------------- |
| "a"=>"g", "b"=>"gf" |
| (1 row) |
| |
| select 'a=>g, b=>c'::hstore || hstore('b', 'NULL'); |
| ?column? |
| ----------------------- |
| "a"=>"g", "b"=>"NULL" |
| (1 row) |
| |
| select 'a=>g, b=>c'::hstore || hstore('b', NULL); |
| ?column? |
| --------------------- |
| "a"=>"g", "b"=>NULL |
| (1 row) |
| |
| select ('a=>g, b=>c'::hstore || hstore(NULL, 'b')) is null; |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| select pg_column_size(hstore('b', 'gf')) |
| = pg_column_size('b=>gf'::hstore); |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| select pg_column_size('a=>g, b=>c'::hstore || hstore('b', 'gf')) |
| = pg_column_size('a=>g, b=>gf'::hstore); |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| -- slice() |
| select slice(hstore 'aa=>1, b=>2, c=>3', ARRAY['g','h','i']); |
| slice |
| ------- |
| |
| (1 row) |
| |
| select slice(hstore 'aa=>1, b=>2, c=>3', ARRAY['c','b']); |
| slice |
| -------------------- |
| "b"=>"2", "c"=>"3" |
| (1 row) |
| |
| select slice(hstore 'aa=>1, b=>2, c=>3', ARRAY['aa','b']); |
| slice |
| --------------------- |
| "b"=>"2", "aa"=>"1" |
| (1 row) |
| |
| select slice(hstore 'aa=>1, b=>2, c=>3', ARRAY['c','b','aa']); |
| slice |
| ------------------------------- |
| "b"=>"2", "c"=>"3", "aa"=>"1" |
| (1 row) |
| |
| select pg_column_size(slice(hstore 'aa=>1, b=>2, c=>3', ARRAY['c','b'])) |
| = pg_column_size('b=>2, c=>3'::hstore); |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| select pg_column_size(slice(hstore 'aa=>1, b=>2, c=>3', ARRAY['c','b','aa'])) |
| = pg_column_size('aa=>1, b=>2, c=>3'::hstore); |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| -- array input |
| select '{}'::text[]::hstore; |
| hstore |
| -------- |
| |
| (1 row) |
| |
| select ARRAY['a','g','b','h','asd']::hstore; |
| ERROR: array must have even number of elements |
| select ARRAY['a','g','b','h','asd','i']::hstore; |
| array |
| -------------------------------- |
| "a"=>"g", "b"=>"h", "asd"=>"i" |
| (1 row) |
| |
| select ARRAY[['a','g'],['b','h'],['asd','i']]::hstore; |
| array |
| -------------------------------- |
| "a"=>"g", "b"=>"h", "asd"=>"i" |
| (1 row) |
| |
| select ARRAY[['a','g','b'],['h','asd','i']]::hstore; |
| ERROR: array must have two columns |
| select ARRAY[[['a','g'],['b','h'],['asd','i']]]::hstore; |
| ERROR: wrong number of array subscripts |
| select hstore('{}'::text[]); |
| hstore |
| -------- |
| |
| (1 row) |
| |
| select hstore(ARRAY['a','g','b','h','asd']); |
| ERROR: array must have even number of elements |
| select hstore(ARRAY['a','g','b','h','asd','i']); |
| hstore |
| -------------------------------- |
| "a"=>"g", "b"=>"h", "asd"=>"i" |
| (1 row) |
| |
| select hstore(ARRAY[['a','g'],['b','h'],['asd','i']]); |
| hstore |
| -------------------------------- |
| "a"=>"g", "b"=>"h", "asd"=>"i" |
| (1 row) |
| |
| select hstore(ARRAY[['a','g','b'],['h','asd','i']]); |
| ERROR: array must have two columns |
| select hstore(ARRAY[[['a','g'],['b','h'],['asd','i']]]); |
| ERROR: wrong number of array subscripts |
| select hstore('[0:5]={a,g,b,h,asd,i}'::text[]); |
| hstore |
| -------------------------------- |
| "a"=>"g", "b"=>"h", "asd"=>"i" |
| (1 row) |
| |
| select hstore('[0:2][1:2]={{a,g},{b,h},{asd,i}}'::text[]); |
| hstore |
| -------------------------------- |
| "a"=>"g", "b"=>"h", "asd"=>"i" |
| (1 row) |
| |
| -- pairs of arrays |
| select hstore(ARRAY['a','b','asd'], ARRAY['g','h','i']); |
| hstore |
| -------------------------------- |
| "a"=>"g", "b"=>"h", "asd"=>"i" |
| (1 row) |
| |
| select hstore(ARRAY['a','b','asd'], ARRAY['g','h',NULL]); |
| hstore |
| --------------------------------- |
| "a"=>"g", "b"=>"h", "asd"=>NULL |
| (1 row) |
| |
| select hstore(ARRAY['z','y','x'], ARRAY['1','2','3']); |
| hstore |
| ------------------------------ |
| "x"=>"3", "y"=>"2", "z"=>"1" |
| (1 row) |
| |
| select hstore(ARRAY['aaa','bb','c','d'], ARRAY[null::text,null,null,null]); |
| hstore |
| ----------------------------------------------- |
| "c"=>NULL, "d"=>NULL, "bb"=>NULL, "aaa"=>NULL |
| (1 row) |
| |
| select hstore(ARRAY['aaa','bb','c','d'], null); |
| hstore |
| ----------------------------------------------- |
| "c"=>NULL, "d"=>NULL, "bb"=>NULL, "aaa"=>NULL |
| (1 row) |
| |
| select quote_literal(hstore('{}'::text[], '{}'::text[])); |
| quote_literal |
| --------------- |
| '' |
| (1 row) |
| |
| select quote_literal(hstore('{}'::text[], null)); |
| quote_literal |
| --------------- |
| '' |
| (1 row) |
| |
| select hstore(ARRAY['a'], '{}'::text[]); -- error |
| ERROR: arrays must have same bounds |
| select hstore('{}'::text[], ARRAY['a']); -- error |
| ERROR: arrays must have same bounds |
| select pg_column_size(hstore(ARRAY['a','b','asd'], ARRAY['g','h','i'])) |
| = pg_column_size('a=>g, b=>h, asd=>i'::hstore); |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| -- records |
| select hstore(v) from (values (1, 'foo', 1.2, 3::float8)) v(a,b,c,d); |
| hstore |
| -------------------------------------------- |
| "a"=>"1", "b"=>"foo", "c"=>"1.2", "d"=>"3" |
| (1 row) |
| |
| create domain hstestdom1 as integer not null default 0; |
| create table testhstore0 (a integer, b text, c numeric, d float8); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| create table testhstore1 (a integer, b text, c numeric, d float8, e hstestdom1); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| insert into testhstore0 values (1, 'foo', 1.2, 3::float8); |
| insert into testhstore1 values (1, 'foo', 1.2, 3::float8); |
| select hstore(v) from testhstore1 v; |
| hstore |
| ------------------------------------------------------ |
| "a"=>"1", "b"=>"foo", "c"=>"1.2", "d"=>"3", "e"=>"0" |
| (1 row) |
| |
| select hstore(null::testhstore0); |
| hstore |
| -------------------------------------------- |
| "a"=>NULL, "b"=>NULL, "c"=>NULL, "d"=>NULL |
| (1 row) |
| |
| select hstore(null::testhstore1); |
| hstore |
| ------------------------------------------------------- |
| "a"=>NULL, "b"=>NULL, "c"=>NULL, "d"=>NULL, "e"=>NULL |
| (1 row) |
| |
| select pg_column_size(hstore(v)) |
| = pg_column_size('a=>1, b=>"foo", c=>"1.2", d=>"3", e=>"0"'::hstore) |
| from testhstore1 v; |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| select populate_record(v, hstore('c', '3.45')) from testhstore1 v; |
| populate_record |
| ------------------ |
| (1,foo,3.45,3,0) |
| (1 row) |
| |
| select populate_record(v, hstore('d', '3.45')) from testhstore1 v; |
| populate_record |
| -------------------- |
| (1,foo,1.2,3.45,0) |
| (1 row) |
| |
| select populate_record(v, hstore('e', '123')) from testhstore1 v; |
| populate_record |
| ------------------- |
| (1,foo,1.2,3,123) |
| (1 row) |
| |
| select populate_record(v, hstore('e', null)) from testhstore1 v; |
| ERROR: domain hstestdom1 does not allow null values |
| select populate_record(v, hstore('c', null)) from testhstore1 v; |
| populate_record |
| ----------------- |
| (1,foo,,3,0) |
| (1 row) |
| |
| select populate_record(v, hstore('b', 'foo') || hstore('a', '123')) from testhstore1 v; |
| populate_record |
| ------------------- |
| (123,foo,1.2,3,0) |
| (1 row) |
| |
| select populate_record(v, hstore('b', 'foo') || hstore('e', null)) from testhstore0 v; |
| populate_record |
| ----------------- |
| (1,foo,1.2,3) |
| (1 row) |
| |
| select populate_record(v, hstore('b', 'foo') || hstore('e', null)) from testhstore1 v; |
| ERROR: domain hstestdom1 does not allow null values |
| select populate_record(v, '') from testhstore0 v; |
| populate_record |
| ----------------- |
| (1,foo,1.2,3) |
| (1 row) |
| |
| select populate_record(v, '') from testhstore1 v; |
| populate_record |
| ----------------- |
| (1,foo,1.2,3,0) |
| (1 row) |
| |
| select populate_record(null::testhstore1, hstore('c', '3.45') || hstore('a', '123')); |
| ERROR: domain hstestdom1 does not allow null values |
| select populate_record(null::testhstore1, hstore('c', '3.45') || hstore('e', '123')); |
| populate_record |
| ----------------- |
| (,,3.45,,123) |
| (1 row) |
| |
| select populate_record(null::testhstore0, ''); |
| populate_record |
| ----------------- |
| (,,,) |
| (1 row) |
| |
| select populate_record(null::testhstore1, ''); |
| ERROR: domain hstestdom1 does not allow null values |
| select v #= hstore('c', '3.45') from testhstore1 v; |
| ?column? |
| ------------------ |
| (1,foo,3.45,3,0) |
| (1 row) |
| |
| select v #= hstore('d', '3.45') from testhstore1 v; |
| ?column? |
| -------------------- |
| (1,foo,1.2,3.45,0) |
| (1 row) |
| |
| select v #= hstore('e', '123') from testhstore1 v; |
| ?column? |
| ------------------- |
| (1,foo,1.2,3,123) |
| (1 row) |
| |
| select v #= hstore('c', null) from testhstore1 v; |
| ?column? |
| -------------- |
| (1,foo,,3,0) |
| (1 row) |
| |
| select v #= hstore('e', null) from testhstore0 v; |
| ?column? |
| --------------- |
| (1,foo,1.2,3) |
| (1 row) |
| |
| select v #= hstore('e', null) from testhstore1 v; |
| ERROR: domain hstestdom1 does not allow null values |
| select v #= (hstore('b', 'foo') || hstore('a', '123')) from testhstore1 v; |
| ?column? |
| ------------------- |
| (123,foo,1.2,3,0) |
| (1 row) |
| |
| select v #= (hstore('b', 'foo') || hstore('e', '123')) from testhstore1 v; |
| ?column? |
| ------------------- |
| (1,foo,1.2,3,123) |
| (1 row) |
| |
| select v #= hstore '' from testhstore0 v; |
| ?column? |
| --------------- |
| (1,foo,1.2,3) |
| (1 row) |
| |
| select v #= hstore '' from testhstore1 v; |
| ?column? |
| ----------------- |
| (1,foo,1.2,3,0) |
| (1 row) |
| |
| select null::testhstore1 #= (hstore('c', '3.45') || hstore('a', '123')); |
| ERROR: domain hstestdom1 does not allow null values |
| select null::testhstore1 #= (hstore('c', '3.45') || hstore('e', '123')); |
| ?column? |
| --------------- |
| (,,3.45,,123) |
| (1 row) |
| |
| select null::testhstore0 #= hstore ''; |
| ?column? |
| ---------- |
| (,,,) |
| (1 row) |
| |
| select null::testhstore1 #= hstore ''; |
| ERROR: domain hstestdom1 does not allow null values |
| select v #= h from testhstore1 v, (values (hstore 'a=>123',1),('b=>foo,c=>3.21',2),('a=>null',3),('e=>123',4),('f=>blah',5)) x(h,i) order by i; |
| ?column? |
| ------------------- |
| (123,foo,1.2,3,0) |
| (1,foo,3.21,3,0) |
| (,foo,1.2,3,0) |
| (1,foo,1.2,3,123) |
| (1,foo,1.2,3,0) |
| (5 rows) |
| |
| -- keys/values |
| select akeys('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>f'); |
| akeys |
| -------------- |
| {b,aa,cq,fg} |
| (1 row) |
| |
| select akeys('""=>1'); |
| akeys |
| ------- |
| {""} |
| (1 row) |
| |
| select akeys(''); |
| akeys |
| ------- |
| {} |
| (1 row) |
| |
| select avals('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>f'); |
| avals |
| ----------- |
| {g,1,l,f} |
| (1 row) |
| |
| select avals('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>NULL'); |
| avals |
| -------------- |
| {g,1,l,NULL} |
| (1 row) |
| |
| select avals('""=>1'); |
| avals |
| ------- |
| {1} |
| (1 row) |
| |
| select avals(''); |
| avals |
| ------- |
| {} |
| (1 row) |
| |
| select hstore_to_array('aa=>1, cq=>l, b=>g, fg=>NULL'::hstore); |
| hstore_to_array |
| ------------------------- |
| {b,g,aa,1,cq,l,fg,NULL} |
| (1 row) |
| |
| select %% 'aa=>1, cq=>l, b=>g, fg=>NULL'; |
| ?column? |
| ------------------------- |
| {b,g,aa,1,cq,l,fg,NULL} |
| (1 row) |
| |
| select hstore_to_matrix('aa=>1, cq=>l, b=>g, fg=>NULL'::hstore); |
| hstore_to_matrix |
| --------------------------------- |
| {{b,g},{aa,1},{cq,l},{fg,NULL}} |
| (1 row) |
| |
| select %# 'aa=>1, cq=>l, b=>g, fg=>NULL'; |
| ?column? |
| --------------------------------- |
| {{b,g},{aa,1},{cq,l},{fg,NULL}} |
| (1 row) |
| |
| select * from skeys('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>f'); |
| skeys |
| ------- |
| b |
| aa |
| cq |
| fg |
| (4 rows) |
| |
| select * from skeys('""=>1'); |
| skeys |
| ------- |
| |
| (1 row) |
| |
| select * from skeys(''); |
| skeys |
| ------- |
| (0 rows) |
| |
| select * from svals('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>f'); |
| svals |
| ------- |
| g |
| 1 |
| l |
| f |
| (4 rows) |
| |
| select *, svals is null from svals('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>NULL'); |
| svals | ?column? |
| -------+---------- |
| g | f |
| 1 | f |
| l | f |
| | t |
| (4 rows) |
| |
| select * from svals('""=>1'); |
| svals |
| ------- |
| 1 |
| (1 row) |
| |
| select * from svals(''); |
| svals |
| ------- |
| (0 rows) |
| |
| select * from each('aaa=>bq, b=>NULL, ""=>1 '); |
| key | value |
| -----+------- |
| | 1 |
| b | |
| aaa | bq |
| (3 rows) |
| |
| -- @> |
| select 'a=>b, b=>1, c=>NULL'::hstore @> 'a=>b'; |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| select 'a=>b, b=>1, c=>NULL'::hstore @> 'a=>b, c=>NULL'; |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| select 'a=>b, b=>1, c=>NULL'::hstore @> 'a=>b, g=>NULL'; |
| ?column? |
| ---------- |
| f |
| (1 row) |
| |
| select 'a=>b, b=>1, c=>NULL'::hstore @> 'g=>NULL'; |
| ?column? |
| ---------- |
| f |
| (1 row) |
| |
| select 'a=>b, b=>1, c=>NULL'::hstore @> 'a=>c'; |
| ?column? |
| ---------- |
| f |
| (1 row) |
| |
| select 'a=>b, b=>1, c=>NULL'::hstore @> 'a=>b'; |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| select 'a=>b, b=>1, c=>NULL'::hstore @> 'a=>b, c=>q'; |
| ?column? |
| ---------- |
| f |
| (1 row) |
| |
| CREATE TABLE testhstore (h hstore); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'h' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| \copy testhstore from 'data/hstore.data' |
| select count(*) from testhstore where h @> 'wait=>NULL'; |
| count |
| ------- |
| 1 |
| (1 row) |
| |
| select count(*) from testhstore where h @> 'wait=>CC'; |
| count |
| ------- |
| 15 |
| (1 row) |
| |
| select count(*) from testhstore where h @> 'wait=>CC, public=>t'; |
| count |
| ------- |
| 2 |
| (1 row) |
| |
| select count(*) from testhstore where h ? 'public'; |
| count |
| ------- |
| 194 |
| (1 row) |
| |
| select count(*) from testhstore where h ?| ARRAY['public','disabled']; |
| count |
| ------- |
| 337 |
| (1 row) |
| |
| select count(*) from testhstore where h ?& ARRAY['public','disabled']; |
| count |
| ------- |
| 42 |
| (1 row) |
| |
| create index hidx on testhstore using gist(h); |
| set enable_seqscan=off; |
| select count(*) from testhstore where h @> 'wait=>NULL'; |
| count |
| ------- |
| 1 |
| (1 row) |
| |
| select count(*) from testhstore where h @> 'wait=>CC'; |
| count |
| ------- |
| 15 |
| (1 row) |
| |
| select count(*) from testhstore where h @> 'wait=>CC, public=>t'; |
| count |
| ------- |
| 2 |
| (1 row) |
| |
| select count(*) from testhstore where h ? 'public'; |
| count |
| ------- |
| 194 |
| (1 row) |
| |
| select count(*) from testhstore where h ?| ARRAY['public','disabled']; |
| count |
| ------- |
| 337 |
| (1 row) |
| |
| select count(*) from testhstore where h ?& ARRAY['public','disabled']; |
| count |
| ------- |
| 42 |
| (1 row) |
| |
| drop index hidx; |
| create index hidx on testhstore using gist(h gist_hstore_ops(siglen=0)); |
| ERROR: value 0 out of bounds for option "siglen" |
| DETAIL: Valid values are between "1" and "8168". |
| create index hidx on testhstore using gist(h gist_hstore_ops(siglen=8169)); |
| ERROR: value 8169 out of bounds for option "siglen" |
| DETAIL: Valid values are between "1" and "8168". |
| create index hidx on testhstore using gist(h gist_hstore_ops(siglen=8168)); |
| set enable_seqscan=off; |
| select count(*) from testhstore where h @> 'wait=>NULL'; |
| count |
| ------- |
| 1 |
| (1 row) |
| |
| select count(*) from testhstore where h @> 'wait=>CC'; |
| count |
| ------- |
| 15 |
| (1 row) |
| |
| select count(*) from testhstore where h @> 'wait=>CC, public=>t'; |
| count |
| ------- |
| 2 |
| (1 row) |
| |
| select count(*) from testhstore where h ? 'public'; |
| count |
| ------- |
| 194 |
| (1 row) |
| |
| select count(*) from testhstore where h ?| ARRAY['public','disabled']; |
| count |
| ------- |
| 337 |
| (1 row) |
| |
| select count(*) from testhstore where h ?& ARRAY['public','disabled']; |
| count |
| ------- |
| 42 |
| (1 row) |
| |
| drop index hidx; |
| create index hidx on testhstore using gin (h); |
| set enable_seqscan=off; |
| select count(*) from testhstore where h @> 'wait=>NULL'; |
| count |
| ------- |
| 1 |
| (1 row) |
| |
| select count(*) from testhstore where h @> 'wait=>CC'; |
| count |
| ------- |
| 15 |
| (1 row) |
| |
| select count(*) from testhstore where h @> 'wait=>CC, public=>t'; |
| count |
| ------- |
| 2 |
| (1 row) |
| |
| select count(*) from testhstore where h ? 'public'; |
| count |
| ------- |
| 194 |
| (1 row) |
| |
| select count(*) from testhstore where h ?| ARRAY['public','disabled']; |
| count |
| ------- |
| 337 |
| (1 row) |
| |
| select count(*) from testhstore where h ?& ARRAY['public','disabled']; |
| count |
| ------- |
| 42 |
| (1 row) |
| |
| select count(*) from (select (each(h)).key from testhstore) as wow ; |
| count |
| ------- |
| 4781 |
| (1 row) |
| |
| select key, count(*) from (select (each(h)).key from testhstore) as wow group by key order by count desc, key; |
| key | count |
| -----------+------- |
| line | 884 |
| query | 207 |
| pos | 203 |
| node | 202 |
| space | 197 |
| status | 195 |
| public | 194 |
| title | 190 |
| wait | 190 |
| org | 189 |
| user | 189 |
| coauthors | 188 |
| disabled | 185 |
| indexed | 184 |
| cleaned | 180 |
| bad | 179 |
| date | 179 |
| world | 176 |
| state | 172 |
| subtitle | 169 |
| auth | 168 |
| abstract | 161 |
| (22 rows) |
| |
| -- sort/hash |
| select count(distinct h) from testhstore; |
| count |
| ------- |
| 885 |
| (1 row) |
| |
| set enable_hashagg = false; |
| select count(*) from (select h from (select * from testhstore union all select * from testhstore) hs group by h) hs2; |
| count |
| ------- |
| 885 |
| (1 row) |
| |
| set enable_hashagg = true; |
| set enable_sort = false; |
| select count(*) from (select h from (select * from testhstore union all select * from testhstore) hs group by h) hs2; |
| count |
| ------- |
| 885 |
| (1 row) |
| |
| select distinct * from (values (hstore '' || ''),('')) v(h); |
| h |
| --- |
| |
| (1 row) |
| |
| set enable_sort = true; |
| -- btree |
| drop index hidx; |
| create index hidx on testhstore using btree (h); |
| set enable_seqscan=off; |
| select count(*) from testhstore where h #># 'p=>1'; |
| count |
| ------- |
| 125 |
| (1 row) |
| |
| select count(*) from testhstore where h = 'pos=>98, line=>371, node=>CBA, indexed=>t'; |
| count |
| ------- |
| 1 |
| (1 row) |
| |
| -- json and jsonb |
| select hstore_to_json('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4'); |
| hstore_to_json |
| ------------------------------------------------------------------------------------------------- |
| {"b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4", "a key": "1"} |
| (1 row) |
| |
| select cast( hstore '"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4' as json); |
| json |
| ------------------------------------------------------------------------------------------------- |
| {"b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4", "a key": "1"} |
| (1 row) |
| |
| select hstore_to_json_loose('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4, h=> "2016-01-01"'); |
| hstore_to_json_loose |
| ------------------------------------------------------------------------------------------------------------- |
| {"b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4, "h": "2016-01-01", "a key": 1} |
| (1 row) |
| |
| select hstore_to_jsonb('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4'); |
| hstore_to_jsonb |
| ------------------------------------------------------------------------------------------------- |
| {"b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4", "a key": "1"} |
| (1 row) |
| |
| select cast( hstore '"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4' as jsonb); |
| jsonb |
| ------------------------------------------------------------------------------------------------- |
| {"b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4", "a key": "1"} |
| (1 row) |
| |
| select hstore_to_jsonb_loose('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4, h=> "2016-01-01"'); |
| hstore_to_jsonb_loose |
| ---------------------------------------------------------------------------------------------------------- |
| {"b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 23450, "h": "2016-01-01", "a key": 1} |
| (1 row) |
| |
| -- GPDB: use a replicated table, so that the json_agg() below will see the |
| -- rows in the same order the rows are inserted in. With a non-replicated |
| -- table, the rows would be gathered across segments in nondeterministic |
| -- order, but with replicated, the aggregate will run in a single segment, |
| -- and it will always see them in the same order. |
| create table test_json_agg (f1 text, f2 hstore) distributed replicated; |
| insert into test_json_agg values ('rec1','"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4'), |
| ('rec2','"a key" =>2, b => f, c => "null", d=> -12345, e => 012345.6, f=> -1.234, g=> 0.345e-4'); |
| select json_agg(q) from test_json_agg q; |
| json_agg |
| ---------------------------------------------------------------------------------------------------------------------------- |
| [{"f1":"rec1","f2":{"b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4", "a key": "1"}}, + |
| {"f1":"rec2","f2":{"b": "f", "c": "null", "d": "-12345", "e": "012345.6", "f": "-1.234", "g": "0.345e-4", "a key": "2"}}] |
| (1 row) |
| |
| select json_agg(q) from (select f1, hstore_to_json_loose(f2) as f2 from test_json_agg) q; |
| json_agg |
| ---------------------------------------------------------------------------------------------------------------------- |
| [{"f1":"rec1","f2":{"b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4, "a key": 1}}, + |
| {"f1":"rec2","f2":{"b": false, "c": "null", "d": -12345, "e": "012345.6", "f": -1.234, "g": 0.345e-4, "a key": 2}}] |
| (1 row) |
| |
| -- Test subscripting |
| insert into test_json_agg default values; |
| select f2['d'], f2['x'] is null as x_isnull from test_json_agg; |
| f2 | x_isnull |
| --------+---------- |
| 12345 | t |
| -12345 | t |
| | t |
| (3 rows) |
| |
| select f2['d']['e'] from test_json_agg; -- error |
| ERROR: hstore allows only one subscript |
| select f2['d':'e'] from test_json_agg; -- error |
| ERROR: hstore allows only one subscript |
| update test_json_agg set f2['d'] = f2['e'], f2['x'] = 'xyzzy'; |
| select f2 from test_json_agg; |
| f2 |
| --------------------------------------------------------------------------------------------------------------------- |
| "b"=>"t", "c"=>NULL, "d"=>"012345", "e"=>"012345", "f"=>"1.234", "g"=>"2.345e+4", "x"=>"xyzzy", "a key"=>"1" |
| "b"=>"f", "c"=>"null", "d"=>"012345.6", "e"=>"012345.6", "f"=>"-1.234", "g"=>"0.345e-4", "x"=>"xyzzy", "a key"=>"2" |
| "d"=>NULL, "x"=>"xyzzy" |
| (3 rows) |
| |
| -- Test subscripting in plpgsql |
| do $$ declare h hstore; |
| begin h['a'] := 'b'; raise notice 'h = %, h[a] = %', h, h['a']; end $$; |
| NOTICE: h = "a"=>"b", h[a] = b |
| -- Check the hstore_hash() and hstore_hash_extended() function explicitly. |
| SELECT v as value, hstore_hash(v)::bit(32) as standard, |
| hstore_hash_extended(v, 0)::bit(32) as extended0, |
| hstore_hash_extended(v, 1)::bit(32) as extended1 |
| FROM (VALUES (NULL::hstore), (''), ('"a key" =>1'), ('c => null'), |
| ('e => 012345'), ('g => 2.345e+4')) x(v) |
| WHERE hstore_hash(v)::bit(32) != hstore_hash_extended(v, 0)::bit(32) |
| OR hstore_hash(v)::bit(32) = hstore_hash_extended(v, 1)::bit(32); |
| value | standard | extended0 | extended1 |
| -------+----------+-----------+----------- |
| (0 rows) |
| |
