1 CREATE EXTENSION hstore;
2 set escape_string_warning=off;
10 select 'a=>b'::hstore;
16 select ' a=>b'::hstore;
22 select 'a =>b'::hstore;
28 select 'a=>b '::hstore;
34 select 'a=> b'::hstore;
40 select '"a"=>"b"'::hstore;
46 select ' "a"=>"b"'::hstore;
52 select '"a" =>"b"'::hstore;
58 select '"a"=>"b" '::hstore;
64 select '"a"=> "b"'::hstore;
70 select 'aa=>bb'::hstore;
76 select ' aa=>bb'::hstore;
82 select 'aa =>bb'::hstore;
88 select 'aa=>bb '::hstore;
94 select 'aa=> bb'::hstore;
100 select '"aa"=>"bb"'::hstore;
106 select ' "aa"=>"bb"'::hstore;
112 select '"aa" =>"bb"'::hstore;
118 select '"aa"=>"bb" '::hstore;
124 select '"aa"=> "bb"'::hstore;
130 select 'aa=>bb, cc=>dd'::hstore;
132 ------------------------
133 "aa"=>"bb", "cc"=>"dd"
136 select 'aa=>bb , cc=>dd'::hstore;
138 ------------------------
139 "aa"=>"bb", "cc"=>"dd"
142 select 'aa=>bb ,cc=>dd'::hstore;
144 ------------------------
145 "aa"=>"bb", "cc"=>"dd"
148 select 'aa=>bb, "cc"=>dd'::hstore;
150 ------------------------
151 "aa"=>"bb", "cc"=>"dd"
154 select 'aa=>bb , "cc"=>dd'::hstore;
156 ------------------------
157 "aa"=>"bb", "cc"=>"dd"
160 select 'aa=>bb ,"cc"=>dd'::hstore;
162 ------------------------
163 "aa"=>"bb", "cc"=>"dd"
166 select 'aa=>"bb", cc=>dd'::hstore;
168 ------------------------
169 "aa"=>"bb", "cc"=>"dd"
172 select 'aa=>"bb" , cc=>dd'::hstore;
174 ------------------------
175 "aa"=>"bb", "cc"=>"dd"
178 select 'aa=>"bb" ,cc=>dd'::hstore;
180 ------------------------
181 "aa"=>"bb", "cc"=>"dd"
184 select 'aa=>null'::hstore;
190 select 'aa=>NuLl'::hstore;
196 select 'aa=>"NuLl"'::hstore;
202 select 'aa=>nul'::hstore;
208 select 'aa=>NuL'::hstore;
214 select 'aa=>"NuL"'::hstore;
220 select e'\\=a=>q=w'::hstore;
226 select e'"=a"=>q\\=w'::hstore;
232 select e'"\\"a"=>q>w'::hstore;
238 select e'\\"a=>q"w'::hstore;
257 select 'aa=>b, c=>d , b=>16'::hstore->'c';
263 select 'aa=>b, c=>d , b=>16'::hstore->'b';
269 select 'aa=>b, c=>d , b=>16'::hstore->'aa';
275 select ('aa=>b, c=>d , b=>16'::hstore->'gg') is null;
281 select ('aa=>NULL, c=>d , b=>16'::hstore->'aa') is null;
287 select ('aa=>"NULL", c=>d , b=>16'::hstore->'aa') is null;
294 select 'aa=>"NULL", c=>d , b=>16'::hstore -> ARRAY['aa','c'];
300 select 'aa=>"NULL", c=>d , b=>16'::hstore -> ARRAY['c','aa'];
306 select 'aa=>NULL, c=>d , b=>16'::hstore -> ARRAY['aa','c',null];
312 select 'aa=>1, c=>3, b=>2, d=>4'::hstore -> ARRAY[['b','d'],['aa','c']];
319 select exist('a=>NULL, b=>qq', 'a');
325 select exist('a=>NULL, b=>qq', 'b');
331 select exist('a=>NULL, b=>qq', 'c');
337 select exist('a=>"NULL", b=>qq', 'a');
343 select defined('a=>NULL, b=>qq', 'a');
349 select defined('a=>NULL, b=>qq', 'b');
355 select defined('a=>NULL, b=>qq', 'c');
361 select defined('a=>"NULL", b=>qq', 'a');
367 select hstore 'a=>NULL, b=>qq' ? 'a';
373 select hstore 'a=>NULL, b=>qq' ? 'b';
379 select hstore 'a=>NULL, b=>qq' ? 'c';
385 select hstore 'a=>"NULL", b=>qq' ? 'a';
391 select hstore 'a=>NULL, b=>qq' ?| ARRAY['a','b'];
397 select hstore 'a=>NULL, b=>qq' ?| ARRAY['b','a'];
403 select hstore 'a=>NULL, b=>qq' ?| ARRAY['c','a'];
409 select hstore 'a=>NULL, b=>qq' ?| ARRAY['c','d'];
415 select hstore 'a=>NULL, b=>qq' ?| '{}'::text[];
421 select hstore 'a=>NULL, b=>qq' ?& ARRAY['a','b'];
427 select hstore 'a=>NULL, b=>qq' ?& ARRAY['b','a'];
433 select hstore 'a=>NULL, b=>qq' ?& ARRAY['c','a'];
439 select hstore 'a=>NULL, b=>qq' ?& ARRAY['c','d'];
445 select hstore 'a=>NULL, b=>qq' ?& '{}'::text[];
452 select delete('a=>1 , b=>2, c=>3'::hstore, 'a');
458 select delete('a=>null , b=>2, c=>3'::hstore, 'a');
464 select delete('a=>1 , b=>2, c=>3'::hstore, 'b');
470 select delete('a=>1 , b=>2, c=>3'::hstore, 'c');
476 select delete('a=>1 , b=>2, c=>3'::hstore, 'd');
478 ------------------------
479 "a"=>1, "b"=>2, "c"=>3
482 select 'a=>1 , b=>2, c=>3'::hstore - 'a'::text;
488 select 'a=>null , b=>2, c=>3'::hstore - 'a'::text;
494 select 'a=>1 , b=>2, c=>3'::hstore - 'b'::text;
500 select 'a=>1 , b=>2, c=>3'::hstore - 'c'::text;
506 select 'a=>1 , b=>2, c=>3'::hstore - 'd'::text;
508 ------------------------
509 "a"=>1, "b"=>2, "c"=>3
512 select pg_column_size('a=>1 , b=>2, c=>3'::hstore - 'b'::text)
513 = pg_column_size('a=>1, b=>2'::hstore);
520 select delete('a=>1 , b=>2, c=>3'::hstore, ARRAY['d','e']);
522 ------------------------
523 "a"=>1, "b"=>2, "c"=>3
526 select delete('a=>1 , b=>2, c=>3'::hstore, ARRAY['d','b']);
532 select delete('a=>1 , b=>2, c=>3'::hstore, ARRAY['a','c']);
538 select delete('a=>1 , b=>2, c=>3'::hstore, ARRAY[['b'],['c'],['a']]);
544 select delete('a=>1 , b=>2, c=>3'::hstore, '{}'::text[]);
546 ------------------------
547 "a"=>1, "b"=>2, "c"=>3
550 select 'a=>1 , b=>2, c=>3'::hstore - ARRAY['d','e'];
552 ------------------------
553 "a"=>1, "b"=>2, "c"=>3
556 select 'a=>1 , b=>2, c=>3'::hstore - ARRAY['d','b'];
562 select 'a=>1 , b=>2, c=>3'::hstore - ARRAY['a','c'];
568 select 'a=>1 , b=>2, c=>3'::hstore - ARRAY[['b'],['c'],['a']];
574 select 'a=>1 , b=>2, c=>3'::hstore - '{}'::text[];
576 ------------------------
577 "a"=>1, "b"=>2, "c"=>3
580 select pg_column_size('a=>1 , b=>2, c=>3'::hstore - ARRAY['a','c'])
581 = pg_column_size('b=>2'::hstore);
587 select pg_column_size('a=>1 , b=>2, c=>3'::hstore - '{}'::text[])
588 = pg_column_size('a=>1, b=>2, c=>3'::hstore);
595 select delete('aa=>1 , b=>2, c=>3'::hstore, 'aa=>4, b=>2'::hstore);
601 select delete('aa=>1 , b=>2, c=>3'::hstore, 'aa=>NULL, c=>3'::hstore);
607 select delete('aa=>1 , b=>2, c=>3'::hstore, 'aa=>1, b=>2, c=>3'::hstore);
613 select delete('aa=>1 , b=>2, c=>3'::hstore, 'b=>2'::hstore);
619 select delete('aa=>1 , b=>2, c=>3'::hstore, ''::hstore);
621 -------------------------
622 "b"=>2, "c"=>3, "aa"=>1
625 select 'aa=>1 , b=>2, c=>3'::hstore - 'aa=>4, b=>2'::hstore;
631 select 'aa=>1 , b=>2, c=>3'::hstore - 'aa=>NULL, c=>3'::hstore;
637 select 'aa=>1 , b=>2, c=>3'::hstore - 'aa=>1, b=>2, c=>3'::hstore;
643 select 'aa=>1 , b=>2, c=>3'::hstore - 'b=>2'::hstore;
649 select 'aa=>1 , b=>2, c=>3'::hstore - ''::hstore;
651 -------------------------
652 "b"=>2, "c"=>3, "aa"=>1
655 select pg_column_size('a=>1 , b=>2, c=>3'::hstore - 'b=>2'::hstore)
656 = pg_column_size('a=>1, c=>3'::hstore);
662 select pg_column_size('a=>1 , b=>2, c=>3'::hstore - ''::hstore)
663 = pg_column_size('a=>1, b=>2, c=>3'::hstore);
670 select 'aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>f';
672 ---------------------------------------
673 "b"=>"g", "aa"=>1, "cq"=>"l", "fg"=>f
676 select 'aa=>1 , b=>2, cq=>3'::hstore || 'aq=>l';
678 -------------------------------------
679 "b"=>2, "aa"=>1, "aq"=>"l", "cq"=>3
682 select 'aa=>1 , b=>2, cq=>3'::hstore || 'aa=>l';
684 ----------------------------
685 "b"=>2, "aa"=>"l", "cq"=>3
688 select 'aa=>1 , b=>2, cq=>3'::hstore || '';
690 --------------------------
691 "b"=>2, "aa"=>1, "cq"=>3
694 select ''::hstore || 'cq=>l, b=>g, fg=>f';
696 ------------------------------
697 "b"=>"g", "cq"=>"l", "fg"=>f
700 select pg_column_size(''::hstore || ''::hstore) = pg_column_size(''::hstore);
706 select pg_column_size('aa=>1'::hstore || 'b=>2'::hstore)
707 = pg_column_size('aa=>1, b=>2'::hstore);
713 select pg_column_size('aa=>1, b=>2'::hstore || ''::hstore)
714 = pg_column_size('aa=>1, b=>2'::hstore);
720 select pg_column_size(''::hstore || 'aa=>1, b=>2'::hstore)
721 = pg_column_size('aa=>1, b=>2'::hstore);
728 select 'a=>g, b=>c'::hstore || hstore('asd', 'gf');
730 ---------------------------------
731 "a"=>"g", "b"=>"c", "asd"=>"gf"
734 select 'a=>g, b=>c'::hstore || hstore('b', 'gf');
736 ---------------------
740 select 'a=>g, b=>c'::hstore || hstore('b', 'NULL');
742 -----------------------
743 "a"=>"g", "b"=>"NULL"
746 select 'a=>g, b=>c'::hstore || hstore('b', NULL);
748 ---------------------
752 select ('a=>g, b=>c'::hstore || hstore(NULL, 'b')) is null;
758 select pg_column_size(hstore('b', 'gf'))
759 = pg_column_size('b=>gf'::hstore);
765 select pg_column_size('a=>g, b=>c'::hstore || hstore('b', 'gf'))
766 = pg_column_size('a=>g, b=>gf'::hstore);
773 select slice(hstore 'aa=>1, b=>2, c=>3', ARRAY['g','h','i']);
779 select slice(hstore 'aa=>1, b=>2, c=>3', ARRAY['c','b']);
785 select slice(hstore 'aa=>1, b=>2, c=>3', ARRAY['aa','b']);
791 select slice(hstore 'aa=>1, b=>2, c=>3', ARRAY['c','b','aa']);
793 -------------------------
794 "b"=>2, "c"=>3, "aa"=>1
797 select pg_column_size(slice(hstore 'aa=>1, b=>2, c=>3', ARRAY['c','b']))
798 = pg_column_size('b=>2, c=>3'::hstore);
804 select pg_column_size(slice(hstore 'aa=>1, b=>2, c=>3', ARRAY['c','b','aa']))
805 = pg_column_size('aa=>1, b=>2, c=>3'::hstore);
812 select '{}'::text[]::hstore;
818 select ARRAY['a','g','b','h','asd']::hstore;
819 ERROR: array must have even number of elements
820 select ARRAY['a','g','b','h','asd','i']::hstore;
822 --------------------------------
823 "a"=>"g", "b"=>"h", "asd"=>"i"
826 select ARRAY[['a','g'],['b','h'],['asd','i']]::hstore;
828 --------------------------------
829 "a"=>"g", "b"=>"h", "asd"=>"i"
832 select ARRAY[['a','g','b'],['h','asd','i']]::hstore;
833 ERROR: array must have two columns
834 select ARRAY[[['a','g'],['b','h'],['asd','i']]]::hstore;
835 ERROR: wrong number of array subscripts
836 select hstore('{}'::text[]);
842 select hstore(ARRAY['a','g','b','h','asd']);
843 ERROR: array must have even number of elements
844 select hstore(ARRAY['a','g','b','h','asd','i']);
846 --------------------------------
847 "a"=>"g", "b"=>"h", "asd"=>"i"
850 select hstore(ARRAY[['a','g'],['b','h'],['asd','i']]);
852 --------------------------------
853 "a"=>"g", "b"=>"h", "asd"=>"i"
856 select hstore(ARRAY[['a','g','b'],['h','asd','i']]);
857 ERROR: array must have two columns
858 select hstore(ARRAY[[['a','g'],['b','h'],['asd','i']]]);
859 ERROR: wrong number of array subscripts
860 select hstore('[0:5]={a,g,b,h,asd,i}'::text[]);
862 --------------------------------
863 "a"=>"g", "b"=>"h", "asd"=>"i"
866 select hstore('[0:2][1:2]={{a,g},{b,h},{asd,i}}'::text[]);
868 --------------------------------
869 "a"=>"g", "b"=>"h", "asd"=>"i"
873 select hstore(ARRAY['a','b','asd'], ARRAY['g','h','i']);
875 --------------------------------
876 "a"=>"g", "b"=>"h", "asd"=>"i"
879 select hstore(ARRAY['a','b','asd'], ARRAY['g','h',NULL]);
881 ---------------------------------
882 "a"=>"g", "b"=>"h", "asd"=>NULL
885 select hstore(ARRAY['z','y','x'], ARRAY['1','2','3']);
887 ------------------------------
888 "x"=>"3", "y"=>"2", "z"=>"1"
891 select hstore(ARRAY['aaa','bb','c','d'], ARRAY[null::text,null,null,null]);
893 -----------------------------------------------
894 "c"=>NULL, "d"=>NULL, "bb"=>NULL, "aaa"=>NULL
897 select hstore(ARRAY['aaa','bb','c','d'], null);
899 -----------------------------------------------
900 "c"=>NULL, "d"=>NULL, "bb"=>NULL, "aaa"=>NULL
903 select quote_literal(hstore('{}'::text[], '{}'::text[]));
909 select quote_literal(hstore('{}'::text[], null));
915 select hstore(ARRAY['a'], '{}'::text[]); -- error
916 ERROR: arrays must have same bounds
917 select hstore('{}'::text[], ARRAY['a']); -- error
918 ERROR: arrays must have same bounds
919 select pg_column_size(hstore(ARRAY['a','b','asd'], ARRAY['g','h','i']))
920 = pg_column_size('a=>g, b=>h, asd=>i'::hstore);
927 select hstore(v) from (values (1, 'foo', 1.2, 3::float8)) v(a,b,c,d);
929 --------------------------------------
930 "a"=>1, "b"=>"foo", "c"=>1.2, "d"=>3
933 create domain hstestdom1 as integer not null default 0;
934 create table testhstore0 (a integer, b text, c numeric, d float8);
935 create table testhstore1 (a integer, b text, c numeric, d float8, e hstestdom1);
936 insert into testhstore0 values (1, 'foo', 1.2, 3::float8);
937 insert into testhstore1 values (1, 'foo', 1.2, 3::float8);
938 select hstore(v) from testhstore1 v;
940 ----------------------------------------------
941 "a"=>1, "b"=>"foo", "c"=>1.2, "d"=>3, "e"=>0
944 select hstore(null::testhstore0);
946 --------------------------------------------
947 "a"=>NULL, "b"=>NULL, "c"=>NULL, "d"=>NULL
950 select hstore(null::testhstore1);
952 -------------------------------------------------------
953 "a"=>NULL, "b"=>NULL, "c"=>NULL, "d"=>NULL, "e"=>NULL
956 select pg_column_size(hstore(v))
957 = pg_column_size('a=>1, b=>"foo", c=>1.2, d=>3, e=>0'::hstore)
964 select populate_record(v, hstore('c', '3.45')) from testhstore1 v;
970 select populate_record(v, hstore('d', '3.45')) from testhstore1 v;
976 select populate_record(v, hstore('e', '123')) from testhstore1 v;
982 select populate_record(v, hstore('e', null)) from testhstore1 v;
983 ERROR: domain hstestdom1 does not allow null values
984 select populate_record(v, hstore('c', null)) from testhstore1 v;
990 select populate_record(v, hstore('b', 'foo') || hstore('a', '123')) from testhstore1 v;
996 select populate_record(v, hstore('b', 'foo') || hstore('e', null)) from testhstore0 v;
1002 select populate_record(v, hstore('b', 'foo') || hstore('e', null)) from testhstore1 v;
1003 ERROR: domain hstestdom1 does not allow null values
1004 select populate_record(v, '') from testhstore0 v;
1010 select populate_record(v, '') from testhstore1 v;
1016 select populate_record(null::testhstore1, hstore('c', '3.45') || hstore('a', '123'));
1017 ERROR: domain hstestdom1 does not allow null values
1018 select populate_record(null::testhstore1, hstore('c', '3.45') || hstore('e', '123'));
1024 select populate_record(null::testhstore0, '');
1030 select populate_record(null::testhstore1, '');
1031 ERROR: domain hstestdom1 does not allow null values
1032 select v #= hstore('c', '3.45') from testhstore1 v;
1038 select v #= hstore('d', '3.45') from testhstore1 v;
1040 --------------------
1044 select v #= hstore('e', '123') from testhstore1 v;
1050 select v #= hstore('c', null) from testhstore1 v;
1056 select v #= hstore('e', null) from testhstore0 v;
1062 select v #= hstore('e', null) from testhstore1 v;
1063 ERROR: domain hstestdom1 does not allow null values
1064 select v #= (hstore('b', 'foo') || hstore('a', '123')) from testhstore1 v;
1070 select v #= (hstore('b', 'foo') || hstore('e', '123')) from testhstore1 v;
1076 select v #= hstore '' from testhstore0 v;
1082 select v #= hstore '' from testhstore1 v;
1088 select null::testhstore1 #= (hstore('c', '3.45') || hstore('a', '123'));
1089 ERROR: domain hstestdom1 does not allow null values
1090 select null::testhstore1 #= (hstore('c', '3.45') || hstore('e', '123'));
1096 select null::testhstore0 #= hstore '';
1102 select null::testhstore1 #= hstore '';
1103 ERROR: domain hstestdom1 does not allow null values
1104 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;
1115 select akeys('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>f');
1121 select akeys('""=>1');
1133 select avals('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>f');
1139 select avals('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>NULL');
1145 select avals('""=>1');
1157 select hstore_to_array('aa=>1, cq=>l, b=>g, fg=>NULL'::hstore);
1159 -------------------------
1160 {b,g,aa,1,cq,l,fg,NULL}
1163 select %% 'aa=>1, cq=>l, b=>g, fg=>NULL';
1165 -------------------------
1166 {b,g,aa,1,cq,l,fg,NULL}
1169 select hstore_to_matrix('aa=>1, cq=>l, b=>g, fg=>NULL'::hstore);
1171 ---------------------------------
1172 {{b,g},{aa,1},{cq,l},{fg,NULL}}
1175 select %# 'aa=>1, cq=>l, b=>g, fg=>NULL';
1177 ---------------------------------
1178 {{b,g},{aa,1},{cq,l},{fg,NULL}}
1181 select * from skeys('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>f');
1190 select * from skeys('""=>1');
1196 select * from skeys('');
1201 select * from svals('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>f');
1210 select *, svals is null from svals('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>NULL');
1219 select * from svals('""=>1');
1225 select * from svals('');
1230 select * from each('aaa=>bq, b=>NULL, ""=>1 ');
1239 select 'a=>b, b=>1, c=>NULL'::hstore @> 'a=>b';
1245 select 'a=>b, b=>1, c=>NULL'::hstore @> 'a=>b, c=>NULL';
1251 select 'a=>b, b=>1, c=>NULL'::hstore @> 'a=>b, g=>NULL';
1257 select 'a=>b, b=>1, c=>NULL'::hstore @> 'g=>NULL';
1263 select 'a=>b, b=>1, c=>NULL'::hstore @> 'a=>c';
1269 select 'a=>b, b=>1, c=>NULL'::hstore @> 'a=>b';
1275 select 'a=>b, b=>1, c=>NULL'::hstore @> 'a=>b, c=>q';
1281 CREATE TABLE testhstore (h hstore);
1282 \copy testhstore from 'data/hstore.data'
1283 select count(*) from testhstore where h @> 'wait=>NULL';
1289 select count(*) from testhstore where h @> 'wait=>CC';
1295 select count(*) from testhstore where h @> 'wait=>CC, public=>t';
1301 select count(*) from testhstore where h @> 'age=>25';
1307 select count(*) from testhstore where h @> 'age=>25.0';
1313 select count(*) from testhstore where h @> 'age=>+25.00';
1319 select count(*) from testhstore where h ? 'public';
1325 select count(*) from testhstore where h ?| ARRAY['public','disabled'];
1331 select count(*) from testhstore where h ?& ARRAY['public','disabled'];
1337 create index hidx on testhstore using gist(h);
1338 set enable_seqscan=off;
1339 select count(*) from testhstore where h @> 'wait=>NULL';
1345 select count(*) from testhstore where h @> 'wait=>CC';
1351 select count(*) from testhstore where h @> 'wait=>CC, public=>t';
1357 select count(*) from testhstore where h @> 'age=>25';
1363 select count(*) from testhstore where h @> 'age=>25.0';
1369 select count(*) from testhstore where h @> 'age=>+25.00';
1375 select count(*) from testhstore where h ? 'public';
1381 select count(*) from testhstore where h ?| ARRAY['public','disabled'];
1387 select count(*) from testhstore where h ?& ARRAY['public','disabled'];
1393 RESET enable_seqscan;
1395 create index hidx on testhstore using gin (h);
1396 set enable_seqscan=off;
1397 select count(*) from testhstore where h @> 'wait=>NULL';
1403 select count(*) from testhstore where h @> 'wait=>CC';
1409 select count(*) from testhstore where h @> 'wait=>CC, public=>t';
1415 select count(*) from testhstore where h @> 'age=>25';
1421 select count(*) from testhstore where h @> 'age=>25.0';
1427 select count(*) from testhstore where h @> 'age=>+25.00';
1433 select count(*) from testhstore where h ? 'public';
1439 select count(*) from testhstore where h ?| ARRAY['public','disabled'];
1445 select count(*) from testhstore where h ?& ARRAY['public','disabled'];
1451 RESET enable_seqscan;
1452 select count(*) from (select (each(h)).key from testhstore) as wow ;
1458 select key, count(*) from (select (each(h)).key from testhstore) as wow group by key order by count desc, key;
1487 select count(distinct h) from testhstore;
1493 set enable_hashagg = false;
1494 select count(*) from (select h from (select * from testhstore union all select * from testhstore) hs group by h) hs2;
1500 set enable_hashagg = true;
1501 set enable_sort = false;
1502 select count(*) from (select h from (select * from testhstore union all select * from testhstore) hs group by h) hs2;
1508 select distinct * from (values (hstore '' || ''),('')) v(h);
1514 set enable_sort = true;
1515 RESET enable_hashagg;
1519 create index hidx on testhstore using btree (h);
1520 set enable_seqscan=off;
1521 select count(*) from testhstore where h #># 'p=>1';
1527 select count(*) from testhstore where h = 'pos=>98, line=>371, node=>CBA, indexed=>t';
1535 create index hidx on testhstore using gin (h gin_hstore_hash_ops);
1536 set enable_seqscan=off;
1537 select count(*) from testhstore where h @> 'wait=>NULL';
1543 select count(*) from testhstore where h @> 'wait=>CC';
1549 select count(*) from testhstore where h @> 'wait=>CC, public=>t';
1555 select count(*) from testhstore where h @> 'age=>25';
1561 select count(*) from testhstore where h @> 'age=>25.0';
1567 select count(*) from testhstore where h @> 'age=>+25.00';
1573 RESET enable_seqscan;
1576 select hstore_to_json('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4');
1578 ------------------------------------------------------------------------------------
1579 {"b": true, "c": null, "d": 12345, "e": 12345, "f": 1.234, "g": 23450, "a key": 1}
1582 select cast( hstore '"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4' as json);
1584 ------------------------------------------------------------------------------------
1585 {"b": true, "c": null, "d": 12345, "e": 12345, "f": 1.234, "g": 23450, "a key": 1}
1588 select hstore_to_json_loose('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4');
1589 hstore_to_json_loose
1590 ------------------------------------------------------------------------------------
1591 {"b": true, "c": null, "d": 12345, "e": 12345, "f": 1.234, "g": 23450, "a key": 1}
1594 create table test_json_agg (f1 text, f2 hstore);
1595 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'),
1596 ('rec2','"a key" =>2, b => f, c => "null", d=> -12345, e => 012345.6, f=> -1.234, g=> 0.345e-4');
1597 select json_agg(q) from test_json_agg q;
1599 --------------------------------------------------------------------------------------------------------------------
1600 [{"f1":"rec1","f2":{"b": true, "c": null, "d": 12345, "e": 12345, "f": 1.234, "g": 23450, "a key": 1}}, +
1601 {"f1":"rec2","f2":{"b": false, "c": "null", "d": -12345, "e": 12345.6, "f": -1.234, "g": 0.0000345, "a key": 2}}]
1604 select json_agg(q) from (select f1, hstore_to_json_loose(f2) as f2 from test_json_agg) q;
1606 --------------------------------------------------------------------------------------------------------------------
1607 [{"f1":"rec1","f2":{"b": true, "c": null, "d": 12345, "e": 12345, "f": 1.234, "g": 23450, "a key": 1}}, +
1608 {"f1":"rec2","f2":{"b": false, "c": "null", "d": -12345, "e": 12345.6, "f": -1.234, "g": 0.0000345, "a key": 2}}]