hstore.diffs
text/plain
Filename: hstore.diffs
Type: text/plain
Part: 4
*** /home/vpopov/Projects/pwdtest.new/postgresql/contrib/hstore/expected/hstore.out 2016-03-15 14:32:12.921977887 +0300
--- /home/vpopov/Projects/pwdtest.new/postgresql/contrib/hstore/results/hstore.out 2016-03-15 16:19:53.174671801 +0300
***************
*** 1161,1509 ****
(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 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)
!
--- 1161,1167 ----
(1 row)
select * from skeys('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>f');
! server closed the connection unexpectedly
! This probably means the server terminated abnormally
! before or while processing the request.
! connection to server was lost
======================================================================