1CREATE EXTENSION hstore; 2-- Check whether any of our opclasses fail amvalidate 3SELECT amname, opcname 4FROM pg_opclass opc LEFT JOIN pg_am am ON am.oid = opcmethod 5WHERE opc.oid >= 16384 AND NOT amvalidate(opc.oid); 6 amname | opcname 7--------+--------- 8(0 rows) 9 10set escape_string_warning=off; 11--hstore; 12select ''::hstore; 13 hstore 14-------- 15 16(1 row) 17 18select 'a=>b'::hstore; 19 hstore 20---------- 21 "a"=>"b" 22(1 row) 23 24select ' a=>b'::hstore; 25 hstore 26---------- 27 "a"=>"b" 28(1 row) 29 30select 'a =>b'::hstore; 31 hstore 32---------- 33 "a"=>"b" 34(1 row) 35 36select 'a=>b '::hstore; 37 hstore 38---------- 39 "a"=>"b" 40(1 row) 41 42select 'a=> b'::hstore; 43 hstore 44---------- 45 "a"=>"b" 46(1 row) 47 48select '"a"=>"b"'::hstore; 49 hstore 50---------- 51 "a"=>"b" 52(1 row) 53 54select ' "a"=>"b"'::hstore; 55 hstore 56---------- 57 "a"=>"b" 58(1 row) 59 60select '"a" =>"b"'::hstore; 61 hstore 62---------- 63 "a"=>"b" 64(1 row) 65 66select '"a"=>"b" '::hstore; 67 hstore 68---------- 69 "a"=>"b" 70(1 row) 71 72select '"a"=> "b"'::hstore; 73 hstore 74---------- 75 "a"=>"b" 76(1 row) 77 78select 'aa=>bb'::hstore; 79 hstore 80------------ 81 "aa"=>"bb" 82(1 row) 83 84select ' aa=>bb'::hstore; 85 hstore 86------------ 87 "aa"=>"bb" 88(1 row) 89 90select 'aa =>bb'::hstore; 91 hstore 92------------ 93 "aa"=>"bb" 94(1 row) 95 96select 'aa=>bb '::hstore; 97 hstore 98------------ 99 "aa"=>"bb" 100(1 row) 101 102select 'aa=> bb'::hstore; 103 hstore 104------------ 105 "aa"=>"bb" 106(1 row) 107 108select '"aa"=>"bb"'::hstore; 109 hstore 110------------ 111 "aa"=>"bb" 112(1 row) 113 114select ' "aa"=>"bb"'::hstore; 115 hstore 116------------ 117 "aa"=>"bb" 118(1 row) 119 120select '"aa" =>"bb"'::hstore; 121 hstore 122------------ 123 "aa"=>"bb" 124(1 row) 125 126select '"aa"=>"bb" '::hstore; 127 hstore 128------------ 129 "aa"=>"bb" 130(1 row) 131 132select '"aa"=> "bb"'::hstore; 133 hstore 134------------ 135 "aa"=>"bb" 136(1 row) 137 138select 'aa=>bb, cc=>dd'::hstore; 139 hstore 140------------------------ 141 "aa"=>"bb", "cc"=>"dd" 142(1 row) 143 144select 'aa=>bb , cc=>dd'::hstore; 145 hstore 146------------------------ 147 "aa"=>"bb", "cc"=>"dd" 148(1 row) 149 150select 'aa=>bb ,cc=>dd'::hstore; 151 hstore 152------------------------ 153 "aa"=>"bb", "cc"=>"dd" 154(1 row) 155 156select 'aa=>bb, "cc"=>dd'::hstore; 157 hstore 158------------------------ 159 "aa"=>"bb", "cc"=>"dd" 160(1 row) 161 162select 'aa=>bb , "cc"=>dd'::hstore; 163 hstore 164------------------------ 165 "aa"=>"bb", "cc"=>"dd" 166(1 row) 167 168select 'aa=>bb ,"cc"=>dd'::hstore; 169 hstore 170------------------------ 171 "aa"=>"bb", "cc"=>"dd" 172(1 row) 173 174select 'aa=>"bb", cc=>dd'::hstore; 175 hstore 176------------------------ 177 "aa"=>"bb", "cc"=>"dd" 178(1 row) 179 180select 'aa=>"bb" , cc=>dd'::hstore; 181 hstore 182------------------------ 183 "aa"=>"bb", "cc"=>"dd" 184(1 row) 185 186select 'aa=>"bb" ,cc=>dd'::hstore; 187 hstore 188------------------------ 189 "aa"=>"bb", "cc"=>"dd" 190(1 row) 191 192select 'aa=>null'::hstore; 193 hstore 194------------ 195 "aa"=>NULL 196(1 row) 197 198select 'aa=>NuLl'::hstore; 199 hstore 200------------ 201 "aa"=>NULL 202(1 row) 203 204select 'aa=>"NuLl"'::hstore; 205 hstore 206-------------- 207 "aa"=>"NuLl" 208(1 row) 209 210select e'\\=a=>q=w'::hstore; 211 hstore 212------------- 213 "=a"=>"q=w" 214(1 row) 215 216select e'"=a"=>q\\=w'::hstore; 217 hstore 218------------- 219 "=a"=>"q=w" 220(1 row) 221 222select e'"\\"a"=>q>w'::hstore; 223 hstore 224-------------- 225 "\"a"=>"q>w" 226(1 row) 227 228select e'\\"a=>q"w'::hstore; 229 hstore 230--------------- 231 "\"a"=>"q\"w" 232(1 row) 233 234select ''::hstore; 235 hstore 236-------- 237 238(1 row) 239 240select ' '::hstore; 241 hstore 242-------- 243 244(1 row) 245 246-- -> operator 247select 'aa=>b, c=>d , b=>16'::hstore->'c'; 248 ?column? 249---------- 250 d 251(1 row) 252 253select 'aa=>b, c=>d , b=>16'::hstore->'b'; 254 ?column? 255---------- 256 16 257(1 row) 258 259select 'aa=>b, c=>d , b=>16'::hstore->'aa'; 260 ?column? 261---------- 262 b 263(1 row) 264 265select ('aa=>b, c=>d , b=>16'::hstore->'gg') is null; 266 ?column? 267---------- 268 t 269(1 row) 270 271select ('aa=>NULL, c=>d , b=>16'::hstore->'aa') is null; 272 ?column? 273---------- 274 t 275(1 row) 276 277select ('aa=>"NULL", c=>d , b=>16'::hstore->'aa') is null; 278 ?column? 279---------- 280 f 281(1 row) 282 283-- -> array operator 284select 'aa=>"NULL", c=>d , b=>16'::hstore -> ARRAY['aa','c']; 285 ?column? 286------------ 287 {"NULL",d} 288(1 row) 289 290select 'aa=>"NULL", c=>d , b=>16'::hstore -> ARRAY['c','aa']; 291 ?column? 292------------ 293 {d,"NULL"} 294(1 row) 295 296select 'aa=>NULL, c=>d , b=>16'::hstore -> ARRAY['aa','c',null]; 297 ?column? 298--------------- 299 {NULL,d,NULL} 300(1 row) 301 302select 'aa=>1, c=>3, b=>2, d=>4'::hstore -> ARRAY[['b','d'],['aa','c']]; 303 ?column? 304--------------- 305 {{2,4},{1,3}} 306(1 row) 307 308-- exists/defined 309select exist('a=>NULL, b=>qq', 'a'); 310 exist 311------- 312 t 313(1 row) 314 315select exist('a=>NULL, b=>qq', 'b'); 316 exist 317------- 318 t 319(1 row) 320 321select exist('a=>NULL, b=>qq', 'c'); 322 exist 323------- 324 f 325(1 row) 326 327select exist('a=>"NULL", b=>qq', 'a'); 328 exist 329------- 330 t 331(1 row) 332 333select defined('a=>NULL, b=>qq', 'a'); 334 defined 335--------- 336 f 337(1 row) 338 339select defined('a=>NULL, b=>qq', 'b'); 340 defined 341--------- 342 t 343(1 row) 344 345select defined('a=>NULL, b=>qq', 'c'); 346 defined 347--------- 348 f 349(1 row) 350 351select defined('a=>"NULL", b=>qq', 'a'); 352 defined 353--------- 354 t 355(1 row) 356 357select hstore 'a=>NULL, b=>qq' ? 'a'; 358 ?column? 359---------- 360 t 361(1 row) 362 363select hstore 'a=>NULL, b=>qq' ? 'b'; 364 ?column? 365---------- 366 t 367(1 row) 368 369select hstore 'a=>NULL, b=>qq' ? 'c'; 370 ?column? 371---------- 372 f 373(1 row) 374 375select hstore 'a=>"NULL", b=>qq' ? 'a'; 376 ?column? 377---------- 378 t 379(1 row) 380 381select hstore 'a=>NULL, b=>qq' ?| ARRAY['a','b']; 382 ?column? 383---------- 384 t 385(1 row) 386 387select hstore 'a=>NULL, b=>qq' ?| ARRAY['b','a']; 388 ?column? 389---------- 390 t 391(1 row) 392 393select hstore 'a=>NULL, b=>qq' ?| ARRAY['c','a']; 394 ?column? 395---------- 396 t 397(1 row) 398 399select hstore 'a=>NULL, b=>qq' ?| ARRAY['c','d']; 400 ?column? 401---------- 402 f 403(1 row) 404 405select hstore 'a=>NULL, b=>qq' ?| '{}'::text[]; 406 ?column? 407---------- 408 f 409(1 row) 410 411select hstore 'a=>NULL, b=>qq' ?& ARRAY['a','b']; 412 ?column? 413---------- 414 t 415(1 row) 416 417select hstore 'a=>NULL, b=>qq' ?& ARRAY['b','a']; 418 ?column? 419---------- 420 t 421(1 row) 422 423select hstore 'a=>NULL, b=>qq' ?& ARRAY['c','a']; 424 ?column? 425---------- 426 f 427(1 row) 428 429select hstore 'a=>NULL, b=>qq' ?& ARRAY['c','d']; 430 ?column? 431---------- 432 f 433(1 row) 434 435select hstore 'a=>NULL, b=>qq' ?& '{}'::text[]; 436 ?column? 437---------- 438 t 439(1 row) 440 441-- delete 442select delete('a=>1 , b=>2, c=>3'::hstore, 'a'); 443 delete 444-------------------- 445 "b"=>"2", "c"=>"3" 446(1 row) 447 448select delete('a=>null , b=>2, c=>3'::hstore, 'a'); 449 delete 450-------------------- 451 "b"=>"2", "c"=>"3" 452(1 row) 453 454select delete('a=>1 , b=>2, c=>3'::hstore, 'b'); 455 delete 456-------------------- 457 "a"=>"1", "c"=>"3" 458(1 row) 459 460select delete('a=>1 , b=>2, c=>3'::hstore, 'c'); 461 delete 462-------------------- 463 "a"=>"1", "b"=>"2" 464(1 row) 465 466select delete('a=>1 , b=>2, c=>3'::hstore, 'd'); 467 delete 468------------------------------ 469 "a"=>"1", "b"=>"2", "c"=>"3" 470(1 row) 471 472select 'a=>1 , b=>2, c=>3'::hstore - 'a'::text; 473 ?column? 474-------------------- 475 "b"=>"2", "c"=>"3" 476(1 row) 477 478select 'a=>null , b=>2, c=>3'::hstore - 'a'::text; 479 ?column? 480-------------------- 481 "b"=>"2", "c"=>"3" 482(1 row) 483 484select 'a=>1 , b=>2, c=>3'::hstore - 'b'::text; 485 ?column? 486-------------------- 487 "a"=>"1", "c"=>"3" 488(1 row) 489 490select 'a=>1 , b=>2, c=>3'::hstore - 'c'::text; 491 ?column? 492-------------------- 493 "a"=>"1", "b"=>"2" 494(1 row) 495 496select 'a=>1 , b=>2, c=>3'::hstore - 'd'::text; 497 ?column? 498------------------------------ 499 "a"=>"1", "b"=>"2", "c"=>"3" 500(1 row) 501 502select pg_column_size('a=>1 , b=>2, c=>3'::hstore - 'b'::text) 503 = pg_column_size('a=>1, b=>2'::hstore); 504 ?column? 505---------- 506 t 507(1 row) 508 509-- delete (array) 510select delete('a=>1 , b=>2, c=>3'::hstore, ARRAY['d','e']); 511 delete 512------------------------------ 513 "a"=>"1", "b"=>"2", "c"=>"3" 514(1 row) 515 516select delete('a=>1 , b=>2, c=>3'::hstore, ARRAY['d','b']); 517 delete 518-------------------- 519 "a"=>"1", "c"=>"3" 520(1 row) 521 522select delete('a=>1 , b=>2, c=>3'::hstore, ARRAY['a','c']); 523 delete 524---------- 525 "b"=>"2" 526(1 row) 527 528select delete('a=>1 , b=>2, c=>3'::hstore, ARRAY[['b'],['c'],['a']]); 529 delete 530-------- 531 532(1 row) 533 534select delete('a=>1 , b=>2, c=>3'::hstore, '{}'::text[]); 535 delete 536------------------------------ 537 "a"=>"1", "b"=>"2", "c"=>"3" 538(1 row) 539 540select 'a=>1 , b=>2, c=>3'::hstore - ARRAY['d','e']; 541 ?column? 542------------------------------ 543 "a"=>"1", "b"=>"2", "c"=>"3" 544(1 row) 545 546select 'a=>1 , b=>2, c=>3'::hstore - ARRAY['d','b']; 547 ?column? 548-------------------- 549 "a"=>"1", "c"=>"3" 550(1 row) 551 552select 'a=>1 , b=>2, c=>3'::hstore - ARRAY['a','c']; 553 ?column? 554---------- 555 "b"=>"2" 556(1 row) 557 558select 'a=>1 , b=>2, c=>3'::hstore - ARRAY[['b'],['c'],['a']]; 559 ?column? 560---------- 561 562(1 row) 563 564select 'a=>1 , b=>2, c=>3'::hstore - '{}'::text[]; 565 ?column? 566------------------------------ 567 "a"=>"1", "b"=>"2", "c"=>"3" 568(1 row) 569 570select pg_column_size('a=>1 , b=>2, c=>3'::hstore - ARRAY['a','c']) 571 = pg_column_size('b=>2'::hstore); 572 ?column? 573---------- 574 t 575(1 row) 576 577select pg_column_size('a=>1 , b=>2, c=>3'::hstore - '{}'::text[]) 578 = pg_column_size('a=>1, b=>2, c=>3'::hstore); 579 ?column? 580---------- 581 t 582(1 row) 583 584-- delete (hstore) 585select delete('aa=>1 , b=>2, c=>3'::hstore, 'aa=>4, b=>2'::hstore); 586 delete 587--------------------- 588 "c"=>"3", "aa"=>"1" 589(1 row) 590 591select delete('aa=>1 , b=>2, c=>3'::hstore, 'aa=>NULL, c=>3'::hstore); 592 delete 593--------------------- 594 "b"=>"2", "aa"=>"1" 595(1 row) 596 597select delete('aa=>1 , b=>2, c=>3'::hstore, 'aa=>1, b=>2, c=>3'::hstore); 598 delete 599-------- 600 601(1 row) 602 603select delete('aa=>1 , b=>2, c=>3'::hstore, 'b=>2'::hstore); 604 delete 605--------------------- 606 "c"=>"3", "aa"=>"1" 607(1 row) 608 609select delete('aa=>1 , b=>2, c=>3'::hstore, ''::hstore); 610 delete 611------------------------------- 612 "b"=>"2", "c"=>"3", "aa"=>"1" 613(1 row) 614 615select 'aa=>1 , b=>2, c=>3'::hstore - 'aa=>4, b=>2'::hstore; 616 ?column? 617--------------------- 618 "c"=>"3", "aa"=>"1" 619(1 row) 620 621select 'aa=>1 , b=>2, c=>3'::hstore - 'aa=>NULL, c=>3'::hstore; 622 ?column? 623--------------------- 624 "b"=>"2", "aa"=>"1" 625(1 row) 626 627select 'aa=>1 , b=>2, c=>3'::hstore - 'aa=>1, b=>2, c=>3'::hstore; 628 ?column? 629---------- 630 631(1 row) 632 633select 'aa=>1 , b=>2, c=>3'::hstore - 'b=>2'::hstore; 634 ?column? 635--------------------- 636 "c"=>"3", "aa"=>"1" 637(1 row) 638 639select 'aa=>1 , b=>2, c=>3'::hstore - ''::hstore; 640 ?column? 641------------------------------- 642 "b"=>"2", "c"=>"3", "aa"=>"1" 643(1 row) 644 645select pg_column_size('a=>1 , b=>2, c=>3'::hstore - 'b=>2'::hstore) 646 = pg_column_size('a=>1, c=>3'::hstore); 647 ?column? 648---------- 649 t 650(1 row) 651 652select pg_column_size('a=>1 , b=>2, c=>3'::hstore - ''::hstore) 653 = pg_column_size('a=>1, b=>2, c=>3'::hstore); 654 ?column? 655---------- 656 t 657(1 row) 658 659-- || 660select 'aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>f'; 661 ?column? 662------------------------------------------- 663 "b"=>"g", "aa"=>"1", "cq"=>"l", "fg"=>"f" 664(1 row) 665 666select 'aa=>1 , b=>2, cq=>3'::hstore || 'aq=>l'; 667 ?column? 668------------------------------------------- 669 "b"=>"2", "aa"=>"1", "aq"=>"l", "cq"=>"3" 670(1 row) 671 672select 'aa=>1 , b=>2, cq=>3'::hstore || 'aa=>l'; 673 ?column? 674-------------------------------- 675 "b"=>"2", "aa"=>"l", "cq"=>"3" 676(1 row) 677 678select 'aa=>1 , b=>2, cq=>3'::hstore || ''; 679 ?column? 680-------------------------------- 681 "b"=>"2", "aa"=>"1", "cq"=>"3" 682(1 row) 683 684select ''::hstore || 'cq=>l, b=>g, fg=>f'; 685 ?column? 686-------------------------------- 687 "b"=>"g", "cq"=>"l", "fg"=>"f" 688(1 row) 689 690select pg_column_size(''::hstore || ''::hstore) = pg_column_size(''::hstore); 691 ?column? 692---------- 693 t 694(1 row) 695 696select pg_column_size('aa=>1'::hstore || 'b=>2'::hstore) 697 = pg_column_size('aa=>1, b=>2'::hstore); 698 ?column? 699---------- 700 t 701(1 row) 702 703select pg_column_size('aa=>1, b=>2'::hstore || ''::hstore) 704 = pg_column_size('aa=>1, b=>2'::hstore); 705 ?column? 706---------- 707 t 708(1 row) 709 710select pg_column_size(''::hstore || 'aa=>1, b=>2'::hstore) 711 = pg_column_size('aa=>1, b=>2'::hstore); 712 ?column? 713---------- 714 t 715(1 row) 716 717-- hstore(text,text) 718select 'a=>g, b=>c'::hstore || hstore('asd', 'gf'); 719 ?column? 720--------------------------------- 721 "a"=>"g", "b"=>"c", "asd"=>"gf" 722(1 row) 723 724select 'a=>g, b=>c'::hstore || hstore('b', 'gf'); 725 ?column? 726--------------------- 727 "a"=>"g", "b"=>"gf" 728(1 row) 729 730select 'a=>g, b=>c'::hstore || hstore('b', 'NULL'); 731 ?column? 732----------------------- 733 "a"=>"g", "b"=>"NULL" 734(1 row) 735 736select 'a=>g, b=>c'::hstore || hstore('b', NULL); 737 ?column? 738--------------------- 739 "a"=>"g", "b"=>NULL 740(1 row) 741 742select ('a=>g, b=>c'::hstore || hstore(NULL, 'b')) is null; 743 ?column? 744---------- 745 t 746(1 row) 747 748select pg_column_size(hstore('b', 'gf')) 749 = pg_column_size('b=>gf'::hstore); 750 ?column? 751---------- 752 t 753(1 row) 754 755select pg_column_size('a=>g, b=>c'::hstore || hstore('b', 'gf')) 756 = pg_column_size('a=>g, b=>gf'::hstore); 757 ?column? 758---------- 759 t 760(1 row) 761 762-- slice() 763select slice(hstore 'aa=>1, b=>2, c=>3', ARRAY['g','h','i']); 764 slice 765------- 766 767(1 row) 768 769select slice(hstore 'aa=>1, b=>2, c=>3', ARRAY['c','b']); 770 slice 771-------------------- 772 "b"=>"2", "c"=>"3" 773(1 row) 774 775select slice(hstore 'aa=>1, b=>2, c=>3', ARRAY['aa','b']); 776 slice 777--------------------- 778 "b"=>"2", "aa"=>"1" 779(1 row) 780 781select slice(hstore 'aa=>1, b=>2, c=>3', ARRAY['c','b','aa']); 782 slice 783------------------------------- 784 "b"=>"2", "c"=>"3", "aa"=>"1" 785(1 row) 786 787select pg_column_size(slice(hstore 'aa=>1, b=>2, c=>3', ARRAY['c','b'])) 788 = pg_column_size('b=>2, c=>3'::hstore); 789 ?column? 790---------- 791 t 792(1 row) 793 794select pg_column_size(slice(hstore 'aa=>1, b=>2, c=>3', ARRAY['c','b','aa'])) 795 = pg_column_size('aa=>1, b=>2, c=>3'::hstore); 796 ?column? 797---------- 798 t 799(1 row) 800 801-- array input 802select '{}'::text[]::hstore; 803 hstore 804-------- 805 806(1 row) 807 808select ARRAY['a','g','b','h','asd']::hstore; 809ERROR: array must have even number of elements 810select ARRAY['a','g','b','h','asd','i']::hstore; 811 array 812-------------------------------- 813 "a"=>"g", "b"=>"h", "asd"=>"i" 814(1 row) 815 816select ARRAY[['a','g'],['b','h'],['asd','i']]::hstore; 817 array 818-------------------------------- 819 "a"=>"g", "b"=>"h", "asd"=>"i" 820(1 row) 821 822select ARRAY[['a','g','b'],['h','asd','i']]::hstore; 823ERROR: array must have two columns 824select ARRAY[[['a','g'],['b','h'],['asd','i']]]::hstore; 825ERROR: wrong number of array subscripts 826select hstore('{}'::text[]); 827 hstore 828-------- 829 830(1 row) 831 832select hstore(ARRAY['a','g','b','h','asd']); 833ERROR: array must have even number of elements 834select hstore(ARRAY['a','g','b','h','asd','i']); 835 hstore 836-------------------------------- 837 "a"=>"g", "b"=>"h", "asd"=>"i" 838(1 row) 839 840select hstore(ARRAY[['a','g'],['b','h'],['asd','i']]); 841 hstore 842-------------------------------- 843 "a"=>"g", "b"=>"h", "asd"=>"i" 844(1 row) 845 846select hstore(ARRAY[['a','g','b'],['h','asd','i']]); 847ERROR: array must have two columns 848select hstore(ARRAY[[['a','g'],['b','h'],['asd','i']]]); 849ERROR: wrong number of array subscripts 850select hstore('[0:5]={a,g,b,h,asd,i}'::text[]); 851 hstore 852-------------------------------- 853 "a"=>"g", "b"=>"h", "asd"=>"i" 854(1 row) 855 856select hstore('[0:2][1:2]={{a,g},{b,h},{asd,i}}'::text[]); 857 hstore 858-------------------------------- 859 "a"=>"g", "b"=>"h", "asd"=>"i" 860(1 row) 861 862-- pairs of arrays 863select hstore(ARRAY['a','b','asd'], ARRAY['g','h','i']); 864 hstore 865-------------------------------- 866 "a"=>"g", "b"=>"h", "asd"=>"i" 867(1 row) 868 869select hstore(ARRAY['a','b','asd'], ARRAY['g','h',NULL]); 870 hstore 871--------------------------------- 872 "a"=>"g", "b"=>"h", "asd"=>NULL 873(1 row) 874 875select hstore(ARRAY['z','y','x'], ARRAY['1','2','3']); 876 hstore 877------------------------------ 878 "x"=>"3", "y"=>"2", "z"=>"1" 879(1 row) 880 881select hstore(ARRAY['aaa','bb','c','d'], ARRAY[null::text,null,null,null]); 882 hstore 883----------------------------------------------- 884 "c"=>NULL, "d"=>NULL, "bb"=>NULL, "aaa"=>NULL 885(1 row) 886 887select hstore(ARRAY['aaa','bb','c','d'], null); 888 hstore 889----------------------------------------------- 890 "c"=>NULL, "d"=>NULL, "bb"=>NULL, "aaa"=>NULL 891(1 row) 892 893select quote_literal(hstore('{}'::text[], '{}'::text[])); 894 quote_literal 895--------------- 896 '' 897(1 row) 898 899select quote_literal(hstore('{}'::text[], null)); 900 quote_literal 901--------------- 902 '' 903(1 row) 904 905select hstore(ARRAY['a'], '{}'::text[]); -- error 906ERROR: arrays must have same bounds 907select hstore('{}'::text[], ARRAY['a']); -- error 908ERROR: arrays must have same bounds 909select pg_column_size(hstore(ARRAY['a','b','asd'], ARRAY['g','h','i'])) 910 = pg_column_size('a=>g, b=>h, asd=>i'::hstore); 911 ?column? 912---------- 913 t 914(1 row) 915 916-- records 917select hstore(v) from (values (1, 'foo', 1.2, 3::float8)) v(a,b,c,d); 918 hstore 919-------------------------------------------- 920 "a"=>"1", "b"=>"foo", "c"=>"1.2", "d"=>"3" 921(1 row) 922 923create domain hstestdom1 as integer not null default 0; 924create table testhstore0 (a integer, b text, c numeric, d float8); 925create table testhstore1 (a integer, b text, c numeric, d float8, e hstestdom1); 926insert into testhstore0 values (1, 'foo', 1.2, 3::float8); 927insert into testhstore1 values (1, 'foo', 1.2, 3::float8); 928select hstore(v) from testhstore1 v; 929 hstore 930------------------------------------------------------ 931 "a"=>"1", "b"=>"foo", "c"=>"1.2", "d"=>"3", "e"=>"0" 932(1 row) 933 934select hstore(null::testhstore0); 935 hstore 936-------------------------------------------- 937 "a"=>NULL, "b"=>NULL, "c"=>NULL, "d"=>NULL 938(1 row) 939 940select hstore(null::testhstore1); 941 hstore 942------------------------------------------------------- 943 "a"=>NULL, "b"=>NULL, "c"=>NULL, "d"=>NULL, "e"=>NULL 944(1 row) 945 946select pg_column_size(hstore(v)) 947 = pg_column_size('a=>1, b=>"foo", c=>"1.2", d=>"3", e=>"0"'::hstore) 948 from testhstore1 v; 949 ?column? 950---------- 951 t 952(1 row) 953 954select populate_record(v, hstore('c', '3.45')) from testhstore1 v; 955 populate_record 956------------------ 957 (1,foo,3.45,3,0) 958(1 row) 959 960select populate_record(v, hstore('d', '3.45')) from testhstore1 v; 961 populate_record 962-------------------- 963 (1,foo,1.2,3.45,0) 964(1 row) 965 966select populate_record(v, hstore('e', '123')) from testhstore1 v; 967 populate_record 968------------------- 969 (1,foo,1.2,3,123) 970(1 row) 971 972select populate_record(v, hstore('e', null)) from testhstore1 v; 973ERROR: domain hstestdom1 does not allow null values 974select populate_record(v, hstore('c', null)) from testhstore1 v; 975 populate_record 976----------------- 977 (1,foo,,3,0) 978(1 row) 979 980select populate_record(v, hstore('b', 'foo') || hstore('a', '123')) from testhstore1 v; 981 populate_record 982------------------- 983 (123,foo,1.2,3,0) 984(1 row) 985 986select populate_record(v, hstore('b', 'foo') || hstore('e', null)) from testhstore0 v; 987 populate_record 988----------------- 989 (1,foo,1.2,3) 990(1 row) 991 992select populate_record(v, hstore('b', 'foo') || hstore('e', null)) from testhstore1 v; 993ERROR: domain hstestdom1 does not allow null values 994select populate_record(v, '') from testhstore0 v; 995 populate_record 996----------------- 997 (1,foo,1.2,3) 998(1 row) 999 1000select populate_record(v, '') from testhstore1 v; 1001 populate_record 1002----------------- 1003 (1,foo,1.2,3,0) 1004(1 row) 1005 1006select populate_record(null::testhstore1, hstore('c', '3.45') || hstore('a', '123')); 1007ERROR: domain hstestdom1 does not allow null values 1008select populate_record(null::testhstore1, hstore('c', '3.45') || hstore('e', '123')); 1009 populate_record 1010----------------- 1011 (,,3.45,,123) 1012(1 row) 1013 1014select populate_record(null::testhstore0, ''); 1015 populate_record 1016----------------- 1017 (,,,) 1018(1 row) 1019 1020select populate_record(null::testhstore1, ''); 1021ERROR: domain hstestdom1 does not allow null values 1022select v #= hstore('c', '3.45') from testhstore1 v; 1023 ?column? 1024------------------ 1025 (1,foo,3.45,3,0) 1026(1 row) 1027 1028select v #= hstore('d', '3.45') from testhstore1 v; 1029 ?column? 1030-------------------- 1031 (1,foo,1.2,3.45,0) 1032(1 row) 1033 1034select v #= hstore('e', '123') from testhstore1 v; 1035 ?column? 1036------------------- 1037 (1,foo,1.2,3,123) 1038(1 row) 1039 1040select v #= hstore('c', null) from testhstore1 v; 1041 ?column? 1042-------------- 1043 (1,foo,,3,0) 1044(1 row) 1045 1046select v #= hstore('e', null) from testhstore0 v; 1047 ?column? 1048--------------- 1049 (1,foo,1.2,3) 1050(1 row) 1051 1052select v #= hstore('e', null) from testhstore1 v; 1053ERROR: domain hstestdom1 does not allow null values 1054select v #= (hstore('b', 'foo') || hstore('a', '123')) from testhstore1 v; 1055 ?column? 1056------------------- 1057 (123,foo,1.2,3,0) 1058(1 row) 1059 1060select v #= (hstore('b', 'foo') || hstore('e', '123')) from testhstore1 v; 1061 ?column? 1062------------------- 1063 (1,foo,1.2,3,123) 1064(1 row) 1065 1066select v #= hstore '' from testhstore0 v; 1067 ?column? 1068--------------- 1069 (1,foo,1.2,3) 1070(1 row) 1071 1072select v #= hstore '' from testhstore1 v; 1073 ?column? 1074----------------- 1075 (1,foo,1.2,3,0) 1076(1 row) 1077 1078select null::testhstore1 #= (hstore('c', '3.45') || hstore('a', '123')); 1079ERROR: domain hstestdom1 does not allow null values 1080select null::testhstore1 #= (hstore('c', '3.45') || hstore('e', '123')); 1081 ?column? 1082--------------- 1083 (,,3.45,,123) 1084(1 row) 1085 1086select null::testhstore0 #= hstore ''; 1087 ?column? 1088---------- 1089 (,,,) 1090(1 row) 1091 1092select null::testhstore1 #= hstore ''; 1093ERROR: domain hstestdom1 does not allow null values 1094select 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; 1095 ?column? 1096------------------- 1097 (123,foo,1.2,3,0) 1098 (1,foo,3.21,3,0) 1099 (,foo,1.2,3,0) 1100 (1,foo,1.2,3,123) 1101 (1,foo,1.2,3,0) 1102(5 rows) 1103 1104-- keys/values 1105select akeys('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>f'); 1106 akeys 1107-------------- 1108 {b,aa,cq,fg} 1109(1 row) 1110 1111select akeys('""=>1'); 1112 akeys 1113------- 1114 {""} 1115(1 row) 1116 1117select akeys(''); 1118 akeys 1119------- 1120 {} 1121(1 row) 1122 1123select avals('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>f'); 1124 avals 1125----------- 1126 {g,1,l,f} 1127(1 row) 1128 1129select avals('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>NULL'); 1130 avals 1131-------------- 1132 {g,1,l,NULL} 1133(1 row) 1134 1135select avals('""=>1'); 1136 avals 1137------- 1138 {1} 1139(1 row) 1140 1141select avals(''); 1142 avals 1143------- 1144 {} 1145(1 row) 1146 1147select hstore_to_array('aa=>1, cq=>l, b=>g, fg=>NULL'::hstore); 1148 hstore_to_array 1149------------------------- 1150 {b,g,aa,1,cq,l,fg,NULL} 1151(1 row) 1152 1153select %% 'aa=>1, cq=>l, b=>g, fg=>NULL'; 1154 ?column? 1155------------------------- 1156 {b,g,aa,1,cq,l,fg,NULL} 1157(1 row) 1158 1159select hstore_to_matrix('aa=>1, cq=>l, b=>g, fg=>NULL'::hstore); 1160 hstore_to_matrix 1161--------------------------------- 1162 {{b,g},{aa,1},{cq,l},{fg,NULL}} 1163(1 row) 1164 1165select %# 'aa=>1, cq=>l, b=>g, fg=>NULL'; 1166 ?column? 1167--------------------------------- 1168 {{b,g},{aa,1},{cq,l},{fg,NULL}} 1169(1 row) 1170 1171select * from skeys('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>f'); 1172 skeys 1173------- 1174 b 1175 aa 1176 cq 1177 fg 1178(4 rows) 1179 1180select * from skeys('""=>1'); 1181 skeys 1182------- 1183 1184(1 row) 1185 1186select * from skeys(''); 1187 skeys 1188------- 1189(0 rows) 1190 1191select * from svals('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>f'); 1192 svals 1193------- 1194 g 1195 1 1196 l 1197 f 1198(4 rows) 1199 1200select *, svals is null from svals('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>NULL'); 1201 svals | ?column? 1202-------+---------- 1203 g | f 1204 1 | f 1205 l | f 1206 | t 1207(4 rows) 1208 1209select * from svals('""=>1'); 1210 svals 1211------- 1212 1 1213(1 row) 1214 1215select * from svals(''); 1216 svals 1217------- 1218(0 rows) 1219 1220select * from each('aaa=>bq, b=>NULL, ""=>1 '); 1221 key | value 1222-----+------- 1223 | 1 1224 b | 1225 aaa | bq 1226(3 rows) 1227 1228-- @> 1229select 'a=>b, b=>1, c=>NULL'::hstore @> 'a=>b'; 1230 ?column? 1231---------- 1232 t 1233(1 row) 1234 1235select 'a=>b, b=>1, c=>NULL'::hstore @> 'a=>b, c=>NULL'; 1236 ?column? 1237---------- 1238 t 1239(1 row) 1240 1241select 'a=>b, b=>1, c=>NULL'::hstore @> 'a=>b, g=>NULL'; 1242 ?column? 1243---------- 1244 f 1245(1 row) 1246 1247select 'a=>b, b=>1, c=>NULL'::hstore @> 'g=>NULL'; 1248 ?column? 1249---------- 1250 f 1251(1 row) 1252 1253select 'a=>b, b=>1, c=>NULL'::hstore @> 'a=>c'; 1254 ?column? 1255---------- 1256 f 1257(1 row) 1258 1259select 'a=>b, b=>1, c=>NULL'::hstore @> 'a=>b'; 1260 ?column? 1261---------- 1262 t 1263(1 row) 1264 1265select 'a=>b, b=>1, c=>NULL'::hstore @> 'a=>b, c=>q'; 1266 ?column? 1267---------- 1268 f 1269(1 row) 1270 1271CREATE TABLE testhstore (h hstore); 1272\copy testhstore from 'data/hstore.data' 1273select count(*) from testhstore where h @> 'wait=>NULL'; 1274 count 1275------- 1276 1 1277(1 row) 1278 1279select count(*) from testhstore where h @> 'wait=>CC'; 1280 count 1281------- 1282 15 1283(1 row) 1284 1285select count(*) from testhstore where h @> 'wait=>CC, public=>t'; 1286 count 1287------- 1288 2 1289(1 row) 1290 1291select count(*) from testhstore where h ? 'public'; 1292 count 1293------- 1294 194 1295(1 row) 1296 1297select count(*) from testhstore where h ?| ARRAY['public','disabled']; 1298 count 1299------- 1300 337 1301(1 row) 1302 1303select count(*) from testhstore where h ?& ARRAY['public','disabled']; 1304 count 1305------- 1306 42 1307(1 row) 1308 1309create index hidx on testhstore using gist(h); 1310set enable_seqscan=off; 1311select count(*) from testhstore where h @> 'wait=>NULL'; 1312 count 1313------- 1314 1 1315(1 row) 1316 1317select count(*) from testhstore where h @> 'wait=>CC'; 1318 count 1319------- 1320 15 1321(1 row) 1322 1323select count(*) from testhstore where h @> 'wait=>CC, public=>t'; 1324 count 1325------- 1326 2 1327(1 row) 1328 1329select count(*) from testhstore where h ? 'public'; 1330 count 1331------- 1332 194 1333(1 row) 1334 1335select count(*) from testhstore where h ?| ARRAY['public','disabled']; 1336 count 1337------- 1338 337 1339(1 row) 1340 1341select count(*) from testhstore where h ?& ARRAY['public','disabled']; 1342 count 1343------- 1344 42 1345(1 row) 1346 1347drop index hidx; 1348create index hidx on testhstore using gin (h); 1349set enable_seqscan=off; 1350select count(*) from testhstore where h @> 'wait=>NULL'; 1351 count 1352------- 1353 1 1354(1 row) 1355 1356select count(*) from testhstore where h @> 'wait=>CC'; 1357 count 1358------- 1359 15 1360(1 row) 1361 1362select count(*) from testhstore where h @> 'wait=>CC, public=>t'; 1363 count 1364------- 1365 2 1366(1 row) 1367 1368select count(*) from testhstore where h ? 'public'; 1369 count 1370------- 1371 194 1372(1 row) 1373 1374select count(*) from testhstore where h ?| ARRAY['public','disabled']; 1375 count 1376------- 1377 337 1378(1 row) 1379 1380select count(*) from testhstore where h ?& ARRAY['public','disabled']; 1381 count 1382------- 1383 42 1384(1 row) 1385 1386select count(*) from (select (each(h)).key from testhstore) as wow ; 1387 count 1388------- 1389 4781 1390(1 row) 1391 1392select key, count(*) from (select (each(h)).key from testhstore) as wow group by key order by count desc, key; 1393 key | count 1394-----------+------- 1395 line | 884 1396 query | 207 1397 pos | 203 1398 node | 202 1399 space | 197 1400 status | 195 1401 public | 194 1402 title | 190 1403 wait | 190 1404 org | 189 1405 user | 189 1406 coauthors | 188 1407 disabled | 185 1408 indexed | 184 1409 cleaned | 180 1410 bad | 179 1411 date | 179 1412 world | 176 1413 state | 172 1414 subtitle | 169 1415 auth | 168 1416 abstract | 161 1417(22 rows) 1418 1419-- sort/hash 1420select count(distinct h) from testhstore; 1421 count 1422------- 1423 885 1424(1 row) 1425 1426set enable_hashagg = false; 1427select count(*) from (select h from (select * from testhstore union all select * from testhstore) hs group by h) hs2; 1428 count 1429------- 1430 885 1431(1 row) 1432 1433set enable_hashagg = true; 1434set enable_sort = false; 1435select count(*) from (select h from (select * from testhstore union all select * from testhstore) hs group by h) hs2; 1436 count 1437------- 1438 885 1439(1 row) 1440 1441select distinct * from (values (hstore '' || ''),('')) v(h); 1442 h 1443--- 1444 1445(1 row) 1446 1447set enable_sort = true; 1448-- btree 1449drop index hidx; 1450create index hidx on testhstore using btree (h); 1451set enable_seqscan=off; 1452select count(*) from testhstore where h #># 'p=>1'; 1453 count 1454------- 1455 125 1456(1 row) 1457 1458select count(*) from testhstore where h = 'pos=>98, line=>371, node=>CBA, indexed=>t'; 1459 count 1460------- 1461 1 1462(1 row) 1463 1464-- json and jsonb 1465select hstore_to_json('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4'); 1466 hstore_to_json 1467------------------------------------------------------------------------------------------------- 1468 {"b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4", "a key": "1"} 1469(1 row) 1470 1471select cast( hstore '"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4' as json); 1472 json 1473------------------------------------------------------------------------------------------------- 1474 {"b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4", "a key": "1"} 1475(1 row) 1476 1477select 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"'); 1478 hstore_to_json_loose 1479------------------------------------------------------------------------------------------------------------- 1480 {"b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4, "h": "2016-01-01", "a key": 1} 1481(1 row) 1482 1483select hstore_to_jsonb('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4'); 1484 hstore_to_jsonb 1485------------------------------------------------------------------------------------------------- 1486 {"b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4", "a key": "1"} 1487(1 row) 1488 1489select cast( hstore '"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4' as jsonb); 1490 jsonb 1491------------------------------------------------------------------------------------------------- 1492 {"b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4", "a key": "1"} 1493(1 row) 1494 1495select 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"'); 1496 hstore_to_jsonb_loose 1497---------------------------------------------------------------------------------------------------------- 1498 {"b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 23450, "h": "2016-01-01", "a key": 1} 1499(1 row) 1500 1501create table test_json_agg (f1 text, f2 hstore); 1502insert into test_json_agg values ('rec1','"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4'), 1503 ('rec2','"a key" =>2, b => f, c => "null", d=> -12345, e => 012345.6, f=> -1.234, g=> 0.345e-4'); 1504select json_agg(q) from test_json_agg q; 1505 json_agg 1506---------------------------------------------------------------------------------------------------------------------------- 1507 [{"f1":"rec1","f2":{"b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4", "a key": "1"}}, + 1508 {"f1":"rec2","f2":{"b": "f", "c": "null", "d": "-12345", "e": "012345.6", "f": "-1.234", "g": "0.345e-4", "a key": "2"}}] 1509(1 row) 1510 1511select json_agg(q) from (select f1, hstore_to_json_loose(f2) as f2 from test_json_agg) q; 1512 json_agg 1513---------------------------------------------------------------------------------------------------------------------- 1514 [{"f1":"rec1","f2":{"b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4, "a key": 1}}, + 1515 {"f1":"rec2","f2":{"b": false, "c": "null", "d": -12345, "e": "012345.6", "f": -1.234, "g": 0.345e-4, "a key": 2}}] 1516(1 row) 1517 1518