diff options
Diffstat (limited to 'contrib/hstore/expected')
| -rw-r--r-- | contrib/hstore/expected/hstore.out | 1601 |
1 files changed, 1601 insertions, 0 deletions
diff --git a/contrib/hstore/expected/hstore.out b/contrib/hstore/expected/hstore.out new file mode 100644 index 0000000..64a3272 --- /dev/null +++ b/contrib/hstore/expected/hstore.out @@ -0,0 +1,1601 @@ +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); +create table testhstore1 (a integer, b text, c numeric, d float8, e hstestdom1); +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); +\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 "2024". +create index hidx on testhstore using gist(h gist_hstore_ops(siglen=2025)); +ERROR: value 2025 out of bounds for option "siglen" +DETAIL: Valid values are between "1" and "2024". +create index hidx on testhstore using gist(h gist_hstore_ops(siglen=2024)); +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) + +create table test_json_agg (f1 text, f2 hstore); +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) + |