1-- 2-- STRINGS 3-- Test various data entry syntaxes. 4-- 5-- SQL string continuation syntax 6-- E021-03 character string literals 7SELECT 'first line' 8' - next line' 9 ' - third line' 10 AS "Three lines to one"; 11 Three lines to one 12------------------------------------- 13 first line - next line - third line 14(1 row) 15 16-- illegal string continuation syntax 17SELECT 'first line' 18' - next line' /* this comment is not allowed here */ 19' - third line' 20 AS "Illegal comment within continuation"; 21ERROR: syntax error at or near "' - third line'" 22LINE 3: ' - third line' 23 ^ 24-- Unicode escapes 25SET standard_conforming_strings TO on; 26SELECT U&'d\0061t\+000061' AS U&"d\0061t\+000061"; 27 data 28------ 29 data 30(1 row) 31 32SELECT U&'d!0061t\+000061' UESCAPE '!' AS U&"d*0061t\+000061" UESCAPE '*'; 33 dat\+000061 34------------- 35 dat\+000061 36(1 row) 37 38SELECT U&'a\\b' AS "a\b"; 39 a\b 40----- 41 a\b 42(1 row) 43 44SELECT U&' \' UESCAPE '!' AS "tricky"; 45 tricky 46-------- 47 \ 48(1 row) 49 50SELECT 'tricky' AS U&"\" UESCAPE '!'; 51 \ 52-------- 53 tricky 54(1 row) 55 56SELECT U&'wrong: \061'; 57ERROR: invalid Unicode escape 58LINE 1: SELECT U&'wrong: \061'; 59 ^ 60HINT: Unicode escapes must be \XXXX or \+XXXXXX. 61SELECT U&'wrong: \+0061'; 62ERROR: invalid Unicode escape 63LINE 1: SELECT U&'wrong: \+0061'; 64 ^ 65HINT: Unicode escapes must be \XXXX or \+XXXXXX. 66SELECT U&'wrong: +0061' UESCAPE +; 67ERROR: UESCAPE must be followed by a simple string literal at or near "+" 68LINE 1: SELECT U&'wrong: +0061' UESCAPE +; 69 ^ 70SELECT U&'wrong: +0061' UESCAPE '+'; 71ERROR: invalid Unicode escape character at or near "'+'" 72LINE 1: SELECT U&'wrong: +0061' UESCAPE '+'; 73 ^ 74SELECT U&'wrong: \db99'; 75ERROR: invalid Unicode surrogate pair 76LINE 1: SELECT U&'wrong: \db99'; 77 ^ 78SELECT U&'wrong: \db99xy'; 79ERROR: invalid Unicode surrogate pair 80LINE 1: SELECT U&'wrong: \db99xy'; 81 ^ 82SELECT U&'wrong: \db99\\'; 83ERROR: invalid Unicode surrogate pair 84LINE 1: SELECT U&'wrong: \db99\\'; 85 ^ 86SELECT U&'wrong: \db99\0061'; 87ERROR: invalid Unicode surrogate pair 88LINE 1: SELECT U&'wrong: \db99\0061'; 89 ^ 90SELECT U&'wrong: \+00db99\+000061'; 91ERROR: invalid Unicode surrogate pair 92LINE 1: SELECT U&'wrong: \+00db99\+000061'; 93 ^ 94SELECT U&'wrong: \+2FFFFF'; 95ERROR: invalid Unicode escape value 96LINE 1: SELECT U&'wrong: \+2FFFFF'; 97 ^ 98-- while we're here, check the same cases in E-style literals 99SELECT E'd\u0061t\U00000061' AS "data"; 100 data 101------ 102 data 103(1 row) 104 105SELECT E'a\\b' AS "a\b"; 106 a\b 107----- 108 a\b 109(1 row) 110 111SELECT E'wrong: \u061'; 112ERROR: invalid Unicode escape 113LINE 1: SELECT E'wrong: \u061'; 114 ^ 115HINT: Unicode escapes must be \uXXXX or \UXXXXXXXX. 116SELECT E'wrong: \U0061'; 117ERROR: invalid Unicode escape 118LINE 1: SELECT E'wrong: \U0061'; 119 ^ 120HINT: Unicode escapes must be \uXXXX or \UXXXXXXXX. 121SELECT E'wrong: \udb99'; 122ERROR: invalid Unicode surrogate pair at or near "'" 123LINE 1: SELECT E'wrong: \udb99'; 124 ^ 125SELECT E'wrong: \udb99xy'; 126ERROR: invalid Unicode surrogate pair at or near "x" 127LINE 1: SELECT E'wrong: \udb99xy'; 128 ^ 129SELECT E'wrong: \udb99\\'; 130ERROR: invalid Unicode surrogate pair at or near "\" 131LINE 1: SELECT E'wrong: \udb99\\'; 132 ^ 133SELECT E'wrong: \udb99\u0061'; 134ERROR: invalid Unicode surrogate pair at or near "\u0061" 135LINE 1: SELECT E'wrong: \udb99\u0061'; 136 ^ 137SELECT E'wrong: \U0000db99\U00000061'; 138ERROR: invalid Unicode surrogate pair at or near "\U00000061" 139LINE 1: SELECT E'wrong: \U0000db99\U00000061'; 140 ^ 141SELECT E'wrong: \U002FFFFF'; 142ERROR: invalid Unicode escape value at or near "\U002FFFFF" 143LINE 1: SELECT E'wrong: \U002FFFFF'; 144 ^ 145SET standard_conforming_strings TO off; 146SELECT U&'d\0061t\+000061' AS U&"d\0061t\+000061"; 147ERROR: unsafe use of string constant with Unicode escapes 148LINE 1: SELECT U&'d\0061t\+000061' AS U&"d\0061t\+000061"; 149 ^ 150DETAIL: String constants with Unicode escapes cannot be used when standard_conforming_strings is off. 151SELECT U&'d!0061t\+000061' UESCAPE '!' AS U&"d*0061t\+000061" UESCAPE '*'; 152ERROR: unsafe use of string constant with Unicode escapes 153LINE 1: SELECT U&'d!0061t\+000061' UESCAPE '!' AS U&"d*0061t\+000061... 154 ^ 155DETAIL: String constants with Unicode escapes cannot be used when standard_conforming_strings is off. 156SELECT U&' \' UESCAPE '!' AS "tricky"; 157ERROR: unsafe use of string constant with Unicode escapes 158LINE 1: SELECT U&' \' UESCAPE '!' AS "tricky"; 159 ^ 160DETAIL: String constants with Unicode escapes cannot be used when standard_conforming_strings is off. 161SELECT 'tricky' AS U&"\" UESCAPE '!'; 162 \ 163-------- 164 tricky 165(1 row) 166 167SELECT U&'wrong: \061'; 168ERROR: unsafe use of string constant with Unicode escapes 169LINE 1: SELECT U&'wrong: \061'; 170 ^ 171DETAIL: String constants with Unicode escapes cannot be used when standard_conforming_strings is off. 172SELECT U&'wrong: \+0061'; 173ERROR: unsafe use of string constant with Unicode escapes 174LINE 1: SELECT U&'wrong: \+0061'; 175 ^ 176DETAIL: String constants with Unicode escapes cannot be used when standard_conforming_strings is off. 177SELECT U&'wrong: +0061' UESCAPE '+'; 178ERROR: unsafe use of string constant with Unicode escapes 179LINE 1: SELECT U&'wrong: +0061' UESCAPE '+'; 180 ^ 181DETAIL: String constants with Unicode escapes cannot be used when standard_conforming_strings is off. 182RESET standard_conforming_strings; 183-- bytea 184SET bytea_output TO hex; 185SELECT E'\\xDeAdBeEf'::bytea; 186 bytea 187------------ 188 \xdeadbeef 189(1 row) 190 191SELECT E'\\x De Ad Be Ef '::bytea; 192 bytea 193------------ 194 \xdeadbeef 195(1 row) 196 197SELECT E'\\xDeAdBeE'::bytea; 198ERROR: invalid hexadecimal data: odd number of digits 199LINE 1: SELECT E'\\xDeAdBeE'::bytea; 200 ^ 201SELECT E'\\xDeAdBeEx'::bytea; 202ERROR: invalid hexadecimal digit: "x" 203LINE 1: SELECT E'\\xDeAdBeEx'::bytea; 204 ^ 205SELECT E'\\xDe00BeEf'::bytea; 206 bytea 207------------ 208 \xde00beef 209(1 row) 210 211SELECT E'DeAdBeEf'::bytea; 212 bytea 213-------------------- 214 \x4465416442654566 215(1 row) 216 217SELECT E'De\\000dBeEf'::bytea; 218 bytea 219-------------------- 220 \x4465006442654566 221(1 row) 222 223SELECT E'De\123dBeEf'::bytea; 224 bytea 225-------------------- 226 \x4465536442654566 227(1 row) 228 229SELECT E'De\\123dBeEf'::bytea; 230 bytea 231-------------------- 232 \x4465536442654566 233(1 row) 234 235SELECT E'De\\678dBeEf'::bytea; 236ERROR: invalid input syntax for type bytea 237LINE 1: SELECT E'De\\678dBeEf'::bytea; 238 ^ 239SET bytea_output TO escape; 240SELECT E'\\xDeAdBeEf'::bytea; 241 bytea 242------------------ 243 \336\255\276\357 244(1 row) 245 246SELECT E'\\x De Ad Be Ef '::bytea; 247 bytea 248------------------ 249 \336\255\276\357 250(1 row) 251 252SELECT E'\\xDe00BeEf'::bytea; 253 bytea 254------------------ 255 \336\000\276\357 256(1 row) 257 258SELECT E'DeAdBeEf'::bytea; 259 bytea 260---------- 261 DeAdBeEf 262(1 row) 263 264SELECT E'De\\000dBeEf'::bytea; 265 bytea 266------------- 267 De\000dBeEf 268(1 row) 269 270SELECT E'De\\123dBeEf'::bytea; 271 bytea 272---------- 273 DeSdBeEf 274(1 row) 275 276-- 277-- test conversions between various string types 278-- E021-10 implicit casting among the character data types 279-- 280SELECT CAST(f1 AS text) AS "text(char)" FROM CHAR_TBL; 281 text(char) 282------------ 283 a 284 ab 285 abcd 286 abcd 287(4 rows) 288 289SELECT CAST(f1 AS text) AS "text(varchar)" FROM VARCHAR_TBL; 290 text(varchar) 291--------------- 292 a 293 ab 294 abcd 295 abcd 296(4 rows) 297 298SELECT CAST(name 'namefield' AS text) AS "text(name)"; 299 text(name) 300------------ 301 namefield 302(1 row) 303 304-- since this is an explicit cast, it should truncate w/o error: 305SELECT CAST(f1 AS char(10)) AS "char(text)" FROM TEXT_TBL; 306 char(text) 307------------ 308 doh! 309 hi de ho n 310(2 rows) 311 312-- note: implicit-cast case is tested in char.sql 313SELECT CAST(f1 AS char(20)) AS "char(text)" FROM TEXT_TBL; 314 char(text) 315---------------------- 316 doh! 317 hi de ho neighbor 318(2 rows) 319 320SELECT CAST(f1 AS char(10)) AS "char(varchar)" FROM VARCHAR_TBL; 321 char(varchar) 322--------------- 323 a 324 ab 325 abcd 326 abcd 327(4 rows) 328 329SELECT CAST(name 'namefield' AS char(10)) AS "char(name)"; 330 char(name) 331------------ 332 namefield 333(1 row) 334 335SELECT CAST(f1 AS varchar) AS "varchar(text)" FROM TEXT_TBL; 336 varchar(text) 337------------------- 338 doh! 339 hi de ho neighbor 340(2 rows) 341 342SELECT CAST(f1 AS varchar) AS "varchar(char)" FROM CHAR_TBL; 343 varchar(char) 344--------------- 345 a 346 ab 347 abcd 348 abcd 349(4 rows) 350 351SELECT CAST(name 'namefield' AS varchar) AS "varchar(name)"; 352 varchar(name) 353--------------- 354 namefield 355(1 row) 356 357-- 358-- test SQL string functions 359-- E### and T### are feature reference numbers from SQL99 360-- 361-- E021-09 trim function 362SELECT TRIM(BOTH FROM ' bunch o blanks ') = 'bunch o blanks' AS "bunch o blanks"; 363 bunch o blanks 364---------------- 365 t 366(1 row) 367 368SELECT TRIM(LEADING FROM ' bunch o blanks ') = 'bunch o blanks ' AS "bunch o blanks "; 369 bunch o blanks 370------------------ 371 t 372(1 row) 373 374SELECT TRIM(TRAILING FROM ' bunch o blanks ') = ' bunch o blanks' AS " bunch o blanks"; 375 bunch o blanks 376------------------ 377 t 378(1 row) 379 380SELECT TRIM(BOTH 'x' FROM 'xxxxxsome Xsxxxxx') = 'some Xs' AS "some Xs"; 381 some Xs 382--------- 383 t 384(1 row) 385 386-- E021-06 substring expression 387SELECT SUBSTRING('1234567890' FROM 3) = '34567890' AS "34567890"; 388 34567890 389---------- 390 t 391(1 row) 392 393SELECT SUBSTRING('1234567890' FROM 4 FOR 3) = '456' AS "456"; 394 456 395----- 396 t 397(1 row) 398 399-- test overflow cases 400SELECT SUBSTRING('string' FROM 2 FOR 2147483646) AS "tring"; 401 tring 402------- 403 tring 404(1 row) 405 406SELECT SUBSTRING('string' FROM -10 FOR 2147483646) AS "string"; 407 string 408-------- 409 string 410(1 row) 411 412SELECT SUBSTRING('string' FROM -10 FOR -2147483646) AS "error"; 413ERROR: negative substring length not allowed 414-- T581 regular expression substring (with SQL's bizarre regexp syntax) 415SELECT SUBSTRING('abcdefg' FROM 'a#"(b_d)#"%' FOR '#') AS "bcd"; 416 bcd 417----- 418 bcd 419(1 row) 420 421-- No match should return NULL 422SELECT SUBSTRING('abcdefg' FROM '#"(b_d)#"%' FOR '#') IS NULL AS "True"; 423 True 424------ 425 t 426(1 row) 427 428-- Null inputs should return NULL 429SELECT SUBSTRING('abcdefg' FROM '%' FOR NULL) IS NULL AS "True"; 430 True 431------ 432 t 433(1 row) 434 435SELECT SUBSTRING(NULL FROM '%' FOR '#') IS NULL AS "True"; 436 True 437------ 438 t 439(1 row) 440 441SELECT SUBSTRING('abcdefg' FROM NULL FOR '#') IS NULL AS "True"; 442 True 443------ 444 t 445(1 row) 446 447-- The first and last parts should act non-greedy 448SELECT SUBSTRING('abcdefg' FROM 'a#"%#"g' FOR '#') AS "bcdef"; 449 bcdef 450------- 451 bcdef 452(1 row) 453 454SELECT SUBSTRING('abcdefg' FROM 'a*#"%#"g*' FOR '#') AS "abcdefg"; 455 abcdefg 456--------- 457 abcdefg 458(1 row) 459 460-- Vertical bar in any part affects only that part 461SELECT SUBSTRING('abcdefg' FROM 'a|b#"%#"g' FOR '#') AS "bcdef"; 462 bcdef 463------- 464 bcdef 465(1 row) 466 467SELECT SUBSTRING('abcdefg' FROM 'a#"%#"x|g' FOR '#') AS "bcdef"; 468 bcdef 469------- 470 bcdef 471(1 row) 472 473SELECT SUBSTRING('abcdefg' FROM 'a#"%|ab#"g' FOR '#') AS "bcdef"; 474 bcdef 475------- 476 bcdef 477(1 row) 478 479-- Can't have more than two part separators 480SELECT SUBSTRING('abcdefg' FROM 'a*#"%#"g*#"x' FOR '#') AS "error"; 481ERROR: SQL regular expression may not contain more than two escape-double-quote separators 482CONTEXT: SQL function "substring" statement 1 483-- Postgres extension: with 0 or 1 separator, assume parts 1 and 3 are empty 484SELECT SUBSTRING('abcdefg' FROM 'a#"%g' FOR '#') AS "bcdefg"; 485 bcdefg 486-------- 487 bcdefg 488(1 row) 489 490SELECT SUBSTRING('abcdefg' FROM 'a%g' FOR '#') AS "abcdefg"; 491 abcdefg 492--------- 493 abcdefg 494(1 row) 495 496-- substring() with just two arguments is not allowed by SQL spec; 497-- we accept it, but we interpret the pattern as a POSIX regexp not SQL 498SELECT SUBSTRING('abcdefg' FROM 'c.e') AS "cde"; 499 cde 500----- 501 cde 502(1 row) 503 504-- With a parenthesized subexpression, return only what matches the subexpr 505SELECT SUBSTRING('abcdefg' FROM 'b(.*)f') AS "cde"; 506 cde 507----- 508 cde 509(1 row) 510 511-- Check behavior of SIMILAR TO, which uses largely the same regexp variant 512SELECT 'abcdefg' SIMILAR TO '_bcd%' AS true; 513 true 514------ 515 t 516(1 row) 517 518SELECT 'abcdefg' SIMILAR TO 'bcd%' AS false; 519 false 520------- 521 f 522(1 row) 523 524SELECT 'abcdefg' SIMILAR TO '_bcd#%' ESCAPE '#' AS false; 525 false 526------- 527 f 528(1 row) 529 530SELECT 'abcd%' SIMILAR TO '_bcd#%' ESCAPE '#' AS true; 531 true 532------ 533 t 534(1 row) 535 536-- Postgres uses '\' as the default escape character, which is not per spec 537SELECT 'abcdefg' SIMILAR TO '_bcd\%' AS false; 538 false 539------- 540 f 541(1 row) 542 543-- and an empty string to mean "no escape", which is also not per spec 544SELECT 'abcd\efg' SIMILAR TO '_bcd\%' ESCAPE '' AS true; 545 true 546------ 547 t 548(1 row) 549 550-- these behaviors are per spec, though: 551SELECT 'abcdefg' SIMILAR TO '_bcd%' ESCAPE NULL AS null; 552 null 553------ 554 555(1 row) 556 557SELECT 'abcdefg' SIMILAR TO '_bcd#%' ESCAPE '##' AS error; 558ERROR: invalid escape string 559HINT: Escape string must be empty or one character. 560-- Test back reference in regexp_replace 561SELECT regexp_replace('1112223333', E'(\\d{3})(\\d{3})(\\d{4})', E'(\\1) \\2-\\3'); 562 regexp_replace 563---------------- 564 (111) 222-3333 565(1 row) 566 567SELECT regexp_replace('AAA BBB CCC ', E'\\s+', ' ', 'g'); 568 regexp_replace 569---------------- 570 AAA BBB CCC 571(1 row) 572 573SELECT regexp_replace('AAA', '^|$', 'Z', 'g'); 574 regexp_replace 575---------------- 576 ZAAAZ 577(1 row) 578 579SELECT regexp_replace('AAA aaa', 'A+', 'Z', 'gi'); 580 regexp_replace 581---------------- 582 Z Z 583(1 row) 584 585-- invalid regexp option 586SELECT regexp_replace('AAA aaa', 'A+', 'Z', 'z'); 587ERROR: invalid regular expression option: "z" 588-- set so we can tell NULL from empty string 589\pset null '\\N' 590-- return all matches from regexp 591SELECT regexp_matches('foobarbequebaz', $re$(bar)(beque)$re$); 592 regexp_matches 593---------------- 594 {bar,beque} 595(1 row) 596 597-- test case insensitive 598SELECT regexp_matches('foObARbEqUEbAz', $re$(bar)(beque)$re$, 'i'); 599 regexp_matches 600---------------- 601 {bAR,bEqUE} 602(1 row) 603 604-- global option - more than one match 605SELECT regexp_matches('foobarbequebazilbarfbonk', $re$(b[^b]+)(b[^b]+)$re$, 'g'); 606 regexp_matches 607---------------- 608 {bar,beque} 609 {bazil,barf} 610(2 rows) 611 612-- empty capture group (matched empty string) 613SELECT regexp_matches('foobarbequebaz', $re$(bar)(.*)(beque)$re$); 614 regexp_matches 615---------------- 616 {bar,"",beque} 617(1 row) 618 619-- no match 620SELECT regexp_matches('foobarbequebaz', $re$(bar)(.+)(beque)$re$); 621 regexp_matches 622---------------- 623(0 rows) 624 625-- optional capture group did not match, null entry in array 626SELECT regexp_matches('foobarbequebaz', $re$(bar)(.+)?(beque)$re$); 627 regexp_matches 628------------------ 629 {bar,NULL,beque} 630(1 row) 631 632-- no capture groups 633SELECT regexp_matches('foobarbequebaz', $re$barbeque$re$); 634 regexp_matches 635---------------- 636 {barbeque} 637(1 row) 638 639-- start/end-of-line matches are of zero length 640SELECT regexp_matches('foo' || chr(10) || 'bar' || chr(10) || 'bequq' || chr(10) || 'baz', '^', 'mg'); 641 regexp_matches 642---------------- 643 {""} 644 {""} 645 {""} 646 {""} 647(4 rows) 648 649SELECT regexp_matches('foo' || chr(10) || 'bar' || chr(10) || 'bequq' || chr(10) || 'baz', '$', 'mg'); 650 regexp_matches 651---------------- 652 {""} 653 {""} 654 {""} 655 {""} 656(4 rows) 657 658SELECT regexp_matches('1' || chr(10) || '2' || chr(10) || '3' || chr(10) || '4' || chr(10), '^.?', 'mg'); 659 regexp_matches 660---------------- 661 {1} 662 {2} 663 {3} 664 {4} 665 {""} 666(5 rows) 667 668SELECT regexp_matches(chr(10) || '1' || chr(10) || '2' || chr(10) || '3' || chr(10) || '4' || chr(10), '.?$', 'mg'); 669 regexp_matches 670---------------- 671 {""} 672 {1} 673 {""} 674 {2} 675 {""} 676 {3} 677 {""} 678 {4} 679 {""} 680 {""} 681(10 rows) 682 683SELECT regexp_matches(chr(10) || '1' || chr(10) || '2' || chr(10) || '3' || chr(10) || '4', '.?$', 'mg'); 684 regexp_matches 685---------------- 686 {""} 687 {1} 688 {""} 689 {2} 690 {""} 691 {3} 692 {""} 693 {4} 694 {""} 695(9 rows) 696 697-- give me errors 698SELECT regexp_matches('foobarbequebaz', $re$(bar)(beque)$re$, 'gz'); 699ERROR: invalid regular expression option: "z" 700SELECT regexp_matches('foobarbequebaz', $re$(barbeque$re$); 701ERROR: invalid regular expression: parentheses () not balanced 702SELECT regexp_matches('foobarbequebaz', $re$(bar)(beque){2,1}$re$); 703ERROR: invalid regular expression: invalid repetition count(s) 704-- split string on regexp 705SELECT foo, length(foo) FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', $re$\s+$re$) AS foo; 706 foo | length 707-------+-------- 708 the | 3 709 quick | 5 710 brown | 5 711 fox | 3 712 jumps | 5 713 over | 4 714 the | 3 715 lazy | 4 716 dog | 3 717(9 rows) 718 719SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', $re$\s+$re$); 720 regexp_split_to_array 721----------------------------------------------- 722 {the,quick,brown,fox,jumps,over,the,lazy,dog} 723(1 row) 724 725SELECT foo, length(foo) FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', $re$\s*$re$) AS foo; 726 foo | length 727-----+-------- 728 t | 1 729 h | 1 730 e | 1 731 q | 1 732 u | 1 733 i | 1 734 c | 1 735 k | 1 736 b | 1 737 r | 1 738 o | 1 739 w | 1 740 n | 1 741 f | 1 742 o | 1 743 x | 1 744 j | 1 745 u | 1 746 m | 1 747 p | 1 748 s | 1 749 o | 1 750 v | 1 751 e | 1 752 r | 1 753 t | 1 754 h | 1 755 e | 1 756 l | 1 757 a | 1 758 z | 1 759 y | 1 760 d | 1 761 o | 1 762 g | 1 763(35 rows) 764 765SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', $re$\s*$re$); 766 regexp_split_to_array 767------------------------------------------------------------------------- 768 {t,h,e,q,u,i,c,k,b,r,o,w,n,f,o,x,j,u,m,p,s,o,v,e,r,t,h,e,l,a,z,y,d,o,g} 769(1 row) 770 771SELECT foo, length(foo) FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', '') AS foo; 772 foo | length 773-----+-------- 774 t | 1 775 h | 1 776 e | 1 777 | 1 778 q | 1 779 u | 1 780 i | 1 781 c | 1 782 k | 1 783 | 1 784 b | 1 785 r | 1 786 o | 1 787 w | 1 788 n | 1 789 | 1 790 f | 1 791 o | 1 792 x | 1 793 | 1 794 j | 1 795 u | 1 796 m | 1 797 p | 1 798 s | 1 799 | 1 800 o | 1 801 v | 1 802 e | 1 803 r | 1 804 | 1 805 t | 1 806 h | 1 807 e | 1 808 | 1 809 l | 1 810 a | 1 811 z | 1 812 y | 1 813 | 1 814 d | 1 815 o | 1 816 g | 1 817(43 rows) 818 819SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', ''); 820 regexp_split_to_array 821--------------------------------------------------------------------------------------------------------- 822 {t,h,e," ",q,u,i,c,k," ",b,r,o,w,n," ",f,o,x," ",j,u,m,p,s," ",o,v,e,r," ",t,h,e," ",l,a,z,y," ",d,o,g} 823(1 row) 824 825-- case insensitive 826SELECT foo, length(foo) FROM regexp_split_to_table('thE QUick bROWn FOx jUMPs ovEr The lazy dOG', 'e', 'i') AS foo; 827 foo | length 828---------------------------+-------- 829 th | 2 830 QUick bROWn FOx jUMPs ov | 25 831 r Th | 4 832 lazy dOG | 9 833(4 rows) 834 835SELECT regexp_split_to_array('thE QUick bROWn FOx jUMPs ovEr The lazy dOG', 'e', 'i'); 836 regexp_split_to_array 837----------------------------------------------------- 838 {th," QUick bROWn FOx jUMPs ov","r Th"," lazy dOG"} 839(1 row) 840 841-- no match of pattern 842SELECT foo, length(foo) FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', 'nomatch') AS foo; 843 foo | length 844---------------------------------------------+-------- 845 the quick brown fox jumps over the lazy dog | 43 846(1 row) 847 848SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', 'nomatch'); 849 regexp_split_to_array 850------------------------------------------------- 851 {"the quick brown fox jumps over the lazy dog"} 852(1 row) 853 854-- some corner cases 855SELECT regexp_split_to_array('123456','1'); 856 regexp_split_to_array 857----------------------- 858 {"",23456} 859(1 row) 860 861SELECT regexp_split_to_array('123456','6'); 862 regexp_split_to_array 863----------------------- 864 {12345,""} 865(1 row) 866 867SELECT regexp_split_to_array('123456','.'); 868 regexp_split_to_array 869------------------------ 870 {"","","","","","",""} 871(1 row) 872 873SELECT regexp_split_to_array('123456',''); 874 regexp_split_to_array 875----------------------- 876 {1,2,3,4,5,6} 877(1 row) 878 879SELECT regexp_split_to_array('123456','(?:)'); 880 regexp_split_to_array 881----------------------- 882 {1,2,3,4,5,6} 883(1 row) 884 885SELECT regexp_split_to_array('1',''); 886 regexp_split_to_array 887----------------------- 888 {1} 889(1 row) 890 891-- errors 892SELECT foo, length(foo) FROM regexp_split_to_table('thE QUick bROWn FOx jUMPs ovEr The lazy dOG', 'e', 'zippy') AS foo; 893ERROR: invalid regular expression option: "z" 894SELECT regexp_split_to_array('thE QUick bROWn FOx jUMPs ovEr The lazy dOG', 'e', 'iz'); 895ERROR: invalid regular expression option: "z" 896-- global option meaningless for regexp_split 897SELECT foo, length(foo) FROM regexp_split_to_table('thE QUick bROWn FOx jUMPs ovEr The lazy dOG', 'e', 'g') AS foo; 898ERROR: regexp_split_to_table() does not support the "global" option 899SELECT regexp_split_to_array('thE QUick bROWn FOx jUMPs ovEr The lazy dOG', 'e', 'g'); 900ERROR: regexp_split_to_array() does not support the "global" option 901-- change NULL-display back 902\pset null '' 903-- E021-11 position expression 904SELECT POSITION('4' IN '1234567890') = '4' AS "4"; 905 4 906--- 907 t 908(1 row) 909 910SELECT POSITION('5' IN '1234567890') = '5' AS "5"; 911 5 912--- 913 t 914(1 row) 915 916-- T312 character overlay function 917SELECT OVERLAY('abcdef' PLACING '45' FROM 4) AS "abc45f"; 918 abc45f 919-------- 920 abc45f 921(1 row) 922 923SELECT OVERLAY('yabadoo' PLACING 'daba' FROM 5) AS "yabadaba"; 924 yabadaba 925---------- 926 yabadaba 927(1 row) 928 929SELECT OVERLAY('yabadoo' PLACING 'daba' FROM 5 FOR 0) AS "yabadabadoo"; 930 yabadabadoo 931------------- 932 yabadabadoo 933(1 row) 934 935SELECT OVERLAY('babosa' PLACING 'ubb' FROM 2 FOR 4) AS "bubba"; 936 bubba 937------- 938 bubba 939(1 row) 940 941-- 942-- test LIKE 943-- Be sure to form every test as a LIKE/NOT LIKE pair. 944-- 945-- simplest examples 946-- E061-04 like predicate 947SELECT 'hawkeye' LIKE 'h%' AS "true"; 948 true 949------ 950 t 951(1 row) 952 953SELECT 'hawkeye' NOT LIKE 'h%' AS "false"; 954 false 955------- 956 f 957(1 row) 958 959SELECT 'hawkeye' LIKE 'H%' AS "false"; 960 false 961------- 962 f 963(1 row) 964 965SELECT 'hawkeye' NOT LIKE 'H%' AS "true"; 966 true 967------ 968 t 969(1 row) 970 971SELECT 'hawkeye' LIKE 'indio%' AS "false"; 972 false 973------- 974 f 975(1 row) 976 977SELECT 'hawkeye' NOT LIKE 'indio%' AS "true"; 978 true 979------ 980 t 981(1 row) 982 983SELECT 'hawkeye' LIKE 'h%eye' AS "true"; 984 true 985------ 986 t 987(1 row) 988 989SELECT 'hawkeye' NOT LIKE 'h%eye' AS "false"; 990 false 991------- 992 f 993(1 row) 994 995SELECT 'indio' LIKE '_ndio' AS "true"; 996 true 997------ 998 t 999(1 row) 1000 1001SELECT 'indio' NOT LIKE '_ndio' AS "false"; 1002 false 1003------- 1004 f 1005(1 row) 1006 1007SELECT 'indio' LIKE 'in__o' AS "true"; 1008 true 1009------ 1010 t 1011(1 row) 1012 1013SELECT 'indio' NOT LIKE 'in__o' AS "false"; 1014 false 1015------- 1016 f 1017(1 row) 1018 1019SELECT 'indio' LIKE 'in_o' AS "false"; 1020 false 1021------- 1022 f 1023(1 row) 1024 1025SELECT 'indio' NOT LIKE 'in_o' AS "true"; 1026 true 1027------ 1028 t 1029(1 row) 1030 1031-- unused escape character 1032SELECT 'hawkeye' LIKE 'h%' ESCAPE '#' AS "true"; 1033 true 1034------ 1035 t 1036(1 row) 1037 1038SELECT 'hawkeye' NOT LIKE 'h%' ESCAPE '#' AS "false"; 1039 false 1040------- 1041 f 1042(1 row) 1043 1044SELECT 'indio' LIKE 'ind_o' ESCAPE '$' AS "true"; 1045 true 1046------ 1047 t 1048(1 row) 1049 1050SELECT 'indio' NOT LIKE 'ind_o' ESCAPE '$' AS "false"; 1051 false 1052------- 1053 f 1054(1 row) 1055 1056-- escape character 1057-- E061-05 like predicate with escape clause 1058SELECT 'h%' LIKE 'h#%' ESCAPE '#' AS "true"; 1059 true 1060------ 1061 t 1062(1 row) 1063 1064SELECT 'h%' NOT LIKE 'h#%' ESCAPE '#' AS "false"; 1065 false 1066------- 1067 f 1068(1 row) 1069 1070SELECT 'h%wkeye' LIKE 'h#%' ESCAPE '#' AS "false"; 1071 false 1072------- 1073 f 1074(1 row) 1075 1076SELECT 'h%wkeye' NOT LIKE 'h#%' ESCAPE '#' AS "true"; 1077 true 1078------ 1079 t 1080(1 row) 1081 1082SELECT 'h%wkeye' LIKE 'h#%%' ESCAPE '#' AS "true"; 1083 true 1084------ 1085 t 1086(1 row) 1087 1088SELECT 'h%wkeye' NOT LIKE 'h#%%' ESCAPE '#' AS "false"; 1089 false 1090------- 1091 f 1092(1 row) 1093 1094SELECT 'h%awkeye' LIKE 'h#%a%k%e' ESCAPE '#' AS "true"; 1095 true 1096------ 1097 t 1098(1 row) 1099 1100SELECT 'h%awkeye' NOT LIKE 'h#%a%k%e' ESCAPE '#' AS "false"; 1101 false 1102------- 1103 f 1104(1 row) 1105 1106SELECT 'indio' LIKE '_ndio' ESCAPE '$' AS "true"; 1107 true 1108------ 1109 t 1110(1 row) 1111 1112SELECT 'indio' NOT LIKE '_ndio' ESCAPE '$' AS "false"; 1113 false 1114------- 1115 f 1116(1 row) 1117 1118SELECT 'i_dio' LIKE 'i$_d_o' ESCAPE '$' AS "true"; 1119 true 1120------ 1121 t 1122(1 row) 1123 1124SELECT 'i_dio' NOT LIKE 'i$_d_o' ESCAPE '$' AS "false"; 1125 false 1126------- 1127 f 1128(1 row) 1129 1130SELECT 'i_dio' LIKE 'i$_nd_o' ESCAPE '$' AS "false"; 1131 false 1132------- 1133 f 1134(1 row) 1135 1136SELECT 'i_dio' NOT LIKE 'i$_nd_o' ESCAPE '$' AS "true"; 1137 true 1138------ 1139 t 1140(1 row) 1141 1142SELECT 'i_dio' LIKE 'i$_d%o' ESCAPE '$' AS "true"; 1143 true 1144------ 1145 t 1146(1 row) 1147 1148SELECT 'i_dio' NOT LIKE 'i$_d%o' ESCAPE '$' AS "false"; 1149 false 1150------- 1151 f 1152(1 row) 1153 1154-- escape character same as pattern character 1155SELECT 'maca' LIKE 'm%aca' ESCAPE '%' AS "true"; 1156 true 1157------ 1158 t 1159(1 row) 1160 1161SELECT 'maca' NOT LIKE 'm%aca' ESCAPE '%' AS "false"; 1162 false 1163------- 1164 f 1165(1 row) 1166 1167SELECT 'ma%a' LIKE 'm%a%%a' ESCAPE '%' AS "true"; 1168 true 1169------ 1170 t 1171(1 row) 1172 1173SELECT 'ma%a' NOT LIKE 'm%a%%a' ESCAPE '%' AS "false"; 1174 false 1175------- 1176 f 1177(1 row) 1178 1179SELECT 'bear' LIKE 'b_ear' ESCAPE '_' AS "true"; 1180 true 1181------ 1182 t 1183(1 row) 1184 1185SELECT 'bear' NOT LIKE 'b_ear' ESCAPE '_' AS "false"; 1186 false 1187------- 1188 f 1189(1 row) 1190 1191SELECT 'be_r' LIKE 'b_e__r' ESCAPE '_' AS "true"; 1192 true 1193------ 1194 t 1195(1 row) 1196 1197SELECT 'be_r' NOT LIKE 'b_e__r' ESCAPE '_' AS "false"; 1198 false 1199------- 1200 f 1201(1 row) 1202 1203SELECT 'be_r' LIKE '__e__r' ESCAPE '_' AS "false"; 1204 false 1205------- 1206 f 1207(1 row) 1208 1209SELECT 'be_r' NOT LIKE '__e__r' ESCAPE '_' AS "true"; 1210 true 1211------ 1212 t 1213(1 row) 1214 1215-- 1216-- test ILIKE (case-insensitive LIKE) 1217-- Be sure to form every test as an ILIKE/NOT ILIKE pair. 1218-- 1219SELECT 'hawkeye' ILIKE 'h%' AS "true"; 1220 true 1221------ 1222 t 1223(1 row) 1224 1225SELECT 'hawkeye' NOT ILIKE 'h%' AS "false"; 1226 false 1227------- 1228 f 1229(1 row) 1230 1231SELECT 'hawkeye' ILIKE 'H%' AS "true"; 1232 true 1233------ 1234 t 1235(1 row) 1236 1237SELECT 'hawkeye' NOT ILIKE 'H%' AS "false"; 1238 false 1239------- 1240 f 1241(1 row) 1242 1243SELECT 'hawkeye' ILIKE 'H%Eye' AS "true"; 1244 true 1245------ 1246 t 1247(1 row) 1248 1249SELECT 'hawkeye' NOT ILIKE 'H%Eye' AS "false"; 1250 false 1251------- 1252 f 1253(1 row) 1254 1255SELECT 'Hawkeye' ILIKE 'h%' AS "true"; 1256 true 1257------ 1258 t 1259(1 row) 1260 1261SELECT 'Hawkeye' NOT ILIKE 'h%' AS "false"; 1262 false 1263------- 1264 f 1265(1 row) 1266 1267-- 1268-- test %/_ combination cases, cf bugs #4821 and #5478 1269-- 1270SELECT 'foo' LIKE '_%' as t, 'f' LIKE '_%' as t, '' LIKE '_%' as f; 1271 t | t | f 1272---+---+--- 1273 t | t | f 1274(1 row) 1275 1276SELECT 'foo' LIKE '%_' as t, 'f' LIKE '%_' as t, '' LIKE '%_' as f; 1277 t | t | f 1278---+---+--- 1279 t | t | f 1280(1 row) 1281 1282SELECT 'foo' LIKE '__%' as t, 'foo' LIKE '___%' as t, 'foo' LIKE '____%' as f; 1283 t | t | f 1284---+---+--- 1285 t | t | f 1286(1 row) 1287 1288SELECT 'foo' LIKE '%__' as t, 'foo' LIKE '%___' as t, 'foo' LIKE '%____' as f; 1289 t | t | f 1290---+---+--- 1291 t | t | f 1292(1 row) 1293 1294SELECT 'jack' LIKE '%____%' AS t; 1295 t 1296--- 1297 t 1298(1 row) 1299 1300-- 1301-- basic tests of LIKE with indexes 1302-- 1303CREATE TABLE texttest (a text PRIMARY KEY, b int); 1304SELECT * FROM texttest WHERE a LIKE '%1%'; 1305 a | b 1306---+--- 1307(0 rows) 1308 1309CREATE TABLE byteatest (a bytea PRIMARY KEY, b int); 1310SELECT * FROM byteatest WHERE a LIKE '%1%'; 1311 a | b 1312---+--- 1313(0 rows) 1314 1315DROP TABLE texttest, byteatest; 1316-- 1317-- test implicit type conversion 1318-- 1319-- E021-07 character concatenation 1320SELECT 'unknown' || ' and unknown' AS "Concat unknown types"; 1321 Concat unknown types 1322---------------------- 1323 unknown and unknown 1324(1 row) 1325 1326SELECT text 'text' || ' and unknown' AS "Concat text to unknown type"; 1327 Concat text to unknown type 1328----------------------------- 1329 text and unknown 1330(1 row) 1331 1332SELECT char(20) 'characters' || ' and text' AS "Concat char to unknown type"; 1333 Concat char to unknown type 1334----------------------------- 1335 characters and text 1336(1 row) 1337 1338SELECT text 'text' || char(20) ' and characters' AS "Concat text to char"; 1339 Concat text to char 1340--------------------- 1341 text and characters 1342(1 row) 1343 1344SELECT text 'text' || varchar ' and varchar' AS "Concat text to varchar"; 1345 Concat text to varchar 1346------------------------ 1347 text and varchar 1348(1 row) 1349 1350-- 1351-- test substr with toasted text values 1352-- 1353CREATE TABLE toasttest(f1 text); 1354insert into toasttest values(repeat('1234567890',10000)); 1355insert into toasttest values(repeat('1234567890',10000)); 1356-- 1357-- Ensure that some values are uncompressed, to test the faster substring 1358-- operation used in that case 1359-- 1360alter table toasttest alter column f1 set storage external; 1361insert into toasttest values(repeat('1234567890',10000)); 1362insert into toasttest values(repeat('1234567890',10000)); 1363-- If the starting position is zero or less, then return from the start of the string 1364-- adjusting the length to be consistent with the "negative start" per SQL. 1365SELECT substr(f1, -1, 5) from toasttest; 1366 substr 1367-------- 1368 123 1369 123 1370 123 1371 123 1372(4 rows) 1373 1374-- If the length is less than zero, an ERROR is thrown. 1375SELECT substr(f1, 5, -1) from toasttest; 1376ERROR: negative substring length not allowed 1377-- If no third argument (length) is provided, the length to the end of the 1378-- string is assumed. 1379SELECT substr(f1, 99995) from toasttest; 1380 substr 1381-------- 1382 567890 1383 567890 1384 567890 1385 567890 1386(4 rows) 1387 1388-- If start plus length is > string length, the result is truncated to 1389-- string length 1390SELECT substr(f1, 99995, 10) from toasttest; 1391 substr 1392-------- 1393 567890 1394 567890 1395 567890 1396 567890 1397(4 rows) 1398 1399TRUNCATE TABLE toasttest; 1400INSERT INTO toasttest values (repeat('1234567890',300)); 1401INSERT INTO toasttest values (repeat('1234567890',300)); 1402INSERT INTO toasttest values (repeat('1234567890',300)); 1403INSERT INTO toasttest values (repeat('1234567890',300)); 1404-- expect >0 blocks 1405SELECT pg_relation_size(reltoastrelid) = 0 AS is_empty 1406 FROM pg_class where relname = 'toasttest'; 1407 is_empty 1408---------- 1409 f 1410(1 row) 1411 1412TRUNCATE TABLE toasttest; 1413ALTER TABLE toasttest set (toast_tuple_target = 4080); 1414INSERT INTO toasttest values (repeat('1234567890',300)); 1415INSERT INTO toasttest values (repeat('1234567890',300)); 1416INSERT INTO toasttest values (repeat('1234567890',300)); 1417INSERT INTO toasttest values (repeat('1234567890',300)); 1418-- expect 0 blocks 1419SELECT pg_relation_size(reltoastrelid) = 0 AS is_empty 1420 FROM pg_class where relname = 'toasttest'; 1421 is_empty 1422---------- 1423 t 1424(1 row) 1425 1426DROP TABLE toasttest; 1427-- 1428-- test substr with toasted bytea values 1429-- 1430CREATE TABLE toasttest(f1 bytea); 1431insert into toasttest values(decode(repeat('1234567890',10000),'escape')); 1432insert into toasttest values(decode(repeat('1234567890',10000),'escape')); 1433-- 1434-- Ensure that some values are uncompressed, to test the faster substring 1435-- operation used in that case 1436-- 1437alter table toasttest alter column f1 set storage external; 1438insert into toasttest values(decode(repeat('1234567890',10000),'escape')); 1439insert into toasttest values(decode(repeat('1234567890',10000),'escape')); 1440-- If the starting position is zero or less, then return from the start of the string 1441-- adjusting the length to be consistent with the "negative start" per SQL. 1442SELECT substr(f1, -1, 5) from toasttest; 1443 substr 1444-------- 1445 123 1446 123 1447 123 1448 123 1449(4 rows) 1450 1451-- If the length is less than zero, an ERROR is thrown. 1452SELECT substr(f1, 5, -1) from toasttest; 1453ERROR: negative substring length not allowed 1454-- If no third argument (length) is provided, the length to the end of the 1455-- string is assumed. 1456SELECT substr(f1, 99995) from toasttest; 1457 substr 1458-------- 1459 567890 1460 567890 1461 567890 1462 567890 1463(4 rows) 1464 1465-- If start plus length is > string length, the result is truncated to 1466-- string length 1467SELECT substr(f1, 99995, 10) from toasttest; 1468 substr 1469-------- 1470 567890 1471 567890 1472 567890 1473 567890 1474(4 rows) 1475 1476DROP TABLE toasttest; 1477-- test internally compressing datums 1478-- this tests compressing a datum to a very small size which exercises a 1479-- corner case in packed-varlena handling: even though small, the compressed 1480-- datum must be given a 4-byte header because there are no bits to indicate 1481-- compression in a 1-byte header 1482CREATE TABLE toasttest (c char(4096)); 1483INSERT INTO toasttest VALUES('x'); 1484SELECT length(c), c::text FROM toasttest; 1485 length | c 1486--------+--- 1487 1 | x 1488(1 row) 1489 1490SELECT c FROM toasttest; 1491 c 1492------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 1493 x 1494(1 row) 1495 1496DROP TABLE toasttest; 1497-- 1498-- test length 1499-- 1500SELECT length('abcdef') AS "length_6"; 1501 length_6 1502---------- 1503 6 1504(1 row) 1505 1506-- 1507-- test strpos 1508-- 1509SELECT strpos('abcdef', 'cd') AS "pos_3"; 1510 pos_3 1511------- 1512 3 1513(1 row) 1514 1515SELECT strpos('abcdef', 'xy') AS "pos_0"; 1516 pos_0 1517------- 1518 0 1519(1 row) 1520 1521SELECT strpos('abcdef', '') AS "pos_1"; 1522 pos_1 1523------- 1524 1 1525(1 row) 1526 1527SELECT strpos('', 'xy') AS "pos_0"; 1528 pos_0 1529------- 1530 0 1531(1 row) 1532 1533SELECT strpos('', '') AS "pos_1"; 1534 pos_1 1535------- 1536 1 1537(1 row) 1538 1539-- 1540-- test replace 1541-- 1542SELECT replace('abcdef', 'de', '45') AS "abc45f"; 1543 abc45f 1544-------- 1545 abc45f 1546(1 row) 1547 1548SELECT replace('yabadabadoo', 'ba', '123') AS "ya123da123doo"; 1549 ya123da123doo 1550--------------- 1551 ya123da123doo 1552(1 row) 1553 1554SELECT replace('yabadoo', 'bad', '') AS "yaoo"; 1555 yaoo 1556------ 1557 yaoo 1558(1 row) 1559 1560-- 1561-- test split_part 1562-- 1563select split_part('joeuser@mydatabase','@',0) AS "an error"; 1564ERROR: field position must be greater than zero 1565select split_part('joeuser@mydatabase','@',1) AS "joeuser"; 1566 joeuser 1567--------- 1568 joeuser 1569(1 row) 1570 1571select split_part('joeuser@mydatabase','@',2) AS "mydatabase"; 1572 mydatabase 1573------------ 1574 mydatabase 1575(1 row) 1576 1577select split_part('joeuser@mydatabase','@',3) AS "empty string"; 1578 empty string 1579-------------- 1580 1581(1 row) 1582 1583select split_part('@joeuser@mydatabase@','@',2) AS "joeuser"; 1584 joeuser 1585--------- 1586 joeuser 1587(1 row) 1588 1589-- 1590-- test to_hex 1591-- 1592select to_hex(256*256*256 - 1) AS "ffffff"; 1593 ffffff 1594-------- 1595 ffffff 1596(1 row) 1597 1598select to_hex(256::bigint*256::bigint*256::bigint*256::bigint - 1) AS "ffffffff"; 1599 ffffffff 1600---------- 1601 ffffffff 1602(1 row) 1603 1604-- 1605-- MD5 test suite - from IETF RFC 1321 1606-- (see: ftp://ftp.rfc-editor.org/in-notes/rfc1321.txt) 1607-- 1608select md5('') = 'd41d8cd98f00b204e9800998ecf8427e' AS "TRUE"; 1609 TRUE 1610------ 1611 t 1612(1 row) 1613 1614select md5('a') = '0cc175b9c0f1b6a831c399e269772661' AS "TRUE"; 1615 TRUE 1616------ 1617 t 1618(1 row) 1619 1620select md5('abc') = '900150983cd24fb0d6963f7d28e17f72' AS "TRUE"; 1621 TRUE 1622------ 1623 t 1624(1 row) 1625 1626select md5('message digest') = 'f96b697d7cb7938d525a2f31aaf161d0' AS "TRUE"; 1627 TRUE 1628------ 1629 t 1630(1 row) 1631 1632select md5('abcdefghijklmnopqrstuvwxyz') = 'c3fcd3d76192e4007dfb496cca67e13b' AS "TRUE"; 1633 TRUE 1634------ 1635 t 1636(1 row) 1637 1638select md5('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789') = 'd174ab98d277d9f5a5611c2c9f419d9f' AS "TRUE"; 1639 TRUE 1640------ 1641 t 1642(1 row) 1643 1644select md5('12345678901234567890123456789012345678901234567890123456789012345678901234567890') = '57edf4a22be3c955ac49da2e2107b67a' AS "TRUE"; 1645 TRUE 1646------ 1647 t 1648(1 row) 1649 1650select md5(''::bytea) = 'd41d8cd98f00b204e9800998ecf8427e' AS "TRUE"; 1651 TRUE 1652------ 1653 t 1654(1 row) 1655 1656select md5('a'::bytea) = '0cc175b9c0f1b6a831c399e269772661' AS "TRUE"; 1657 TRUE 1658------ 1659 t 1660(1 row) 1661 1662select md5('abc'::bytea) = '900150983cd24fb0d6963f7d28e17f72' AS "TRUE"; 1663 TRUE 1664------ 1665 t 1666(1 row) 1667 1668select md5('message digest'::bytea) = 'f96b697d7cb7938d525a2f31aaf161d0' AS "TRUE"; 1669 TRUE 1670------ 1671 t 1672(1 row) 1673 1674select md5('abcdefghijklmnopqrstuvwxyz'::bytea) = 'c3fcd3d76192e4007dfb496cca67e13b' AS "TRUE"; 1675 TRUE 1676------ 1677 t 1678(1 row) 1679 1680select md5('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'::bytea) = 'd174ab98d277d9f5a5611c2c9f419d9f' AS "TRUE"; 1681 TRUE 1682------ 1683 t 1684(1 row) 1685 1686select md5('12345678901234567890123456789012345678901234567890123456789012345678901234567890'::bytea) = '57edf4a22be3c955ac49da2e2107b67a' AS "TRUE"; 1687 TRUE 1688------ 1689 t 1690(1 row) 1691 1692-- 1693-- SHA-2 1694-- 1695SET bytea_output TO hex; 1696SELECT sha224(''); 1697 sha224 1698------------------------------------------------------------ 1699 \xd14a028c2a3a2bc9476102bb288234c415a2b01f828ea62ac5b3e42f 1700(1 row) 1701 1702SELECT sha224('The quick brown fox jumps over the lazy dog.'); 1703 sha224 1704------------------------------------------------------------ 1705 \x619cba8e8e05826e9b8c519c0a5c68f4fb653e8a3d8aa04bb2c8cd4c 1706(1 row) 1707 1708SELECT sha256(''); 1709 sha256 1710-------------------------------------------------------------------- 1711 \xe3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855 1712(1 row) 1713 1714SELECT sha256('The quick brown fox jumps over the lazy dog.'); 1715 sha256 1716-------------------------------------------------------------------- 1717 \xef537f25c895bfa782526529a9b63d97aa631564d5d789c2b765448c8635fb6c 1718(1 row) 1719 1720SELECT sha384(''); 1721 sha384 1722---------------------------------------------------------------------------------------------------- 1723 \x38b060a751ac96384cd9327eb1b1e36a21fdb71114be07434c0cc7bf63f6e1da274edebfe76f65fbd51ad2f14898b95b 1724(1 row) 1725 1726SELECT sha384('The quick brown fox jumps over the lazy dog.'); 1727 sha384 1728---------------------------------------------------------------------------------------------------- 1729 \xed892481d8272ca6df370bf706e4d7bc1b5739fa2177aae6c50e946678718fc67a7af2819a021c2fc34e91bdb63409d7 1730(1 row) 1731 1732SELECT sha512(''); 1733 sha512 1734------------------------------------------------------------------------------------------------------------------------------------ 1735 \xcf83e1357eefb8bdf1542850d66d8007d620e4050b5715dc83f4a921d36ce9ce47d0d13c5d85f2b0ff8318d2877eec2f63b931bd47417a81a538327af927da3e 1736(1 row) 1737 1738SELECT sha512('The quick brown fox jumps over the lazy dog.'); 1739 sha512 1740------------------------------------------------------------------------------------------------------------------------------------ 1741 \x91ea1245f20d46ae9a037a989f54f1f790f0a47607eeb8a14d12890cea77a1bbc6c7ed9cf205e67b7f2b8fd4c7dfd3a7a8617e45f3c463d481c7e586c39ac1ed 1742(1 row) 1743 1744-- 1745-- encode/decode 1746-- 1747SELECT encode('\x1234567890abcdef00', 'hex'); 1748 encode 1749-------------------- 1750 1234567890abcdef00 1751(1 row) 1752 1753SELECT decode('1234567890abcdef00', 'hex'); 1754 decode 1755---------------------- 1756 \x1234567890abcdef00 1757(1 row) 1758 1759SELECT encode(('\x' || repeat('1234567890abcdef0001', 7))::bytea, 'base64'); 1760 encode 1761------------------------------------------------------------------------------ 1762 EjRWeJCrze8AARI0VniQq83vAAESNFZ4kKvN7wABEjRWeJCrze8AARI0VniQq83vAAESNFZ4kKvN+ 1763 7wABEjRWeJCrze8AAQ== 1764(1 row) 1765 1766SELECT decode(encode(('\x' || repeat('1234567890abcdef0001', 7))::bytea, 1767 'base64'), 'base64'); 1768 decode 1769------------------------------------------------------------------------------------------------------------------------------------------------ 1770 \x1234567890abcdef00011234567890abcdef00011234567890abcdef00011234567890abcdef00011234567890abcdef00011234567890abcdef00011234567890abcdef0001 1771(1 row) 1772 1773SELECT encode('\x1234567890abcdef00', 'escape'); 1774 encode 1775----------------------------- 1776 \x124Vx\220\253\315\357\000 1777(1 row) 1778 1779SELECT decode(encode('\x1234567890abcdef00', 'escape'), 'escape'); 1780 decode 1781---------------------- 1782 \x1234567890abcdef00 1783(1 row) 1784 1785-- 1786-- get_bit/set_bit etc 1787-- 1788SELECT get_bit('\x1234567890abcdef00'::bytea, 43); 1789 get_bit 1790--------- 1791 1 1792(1 row) 1793 1794SELECT get_bit('\x1234567890abcdef00'::bytea, 99); -- error 1795ERROR: index 99 out of valid range, 0..71 1796SELECT set_bit('\x1234567890abcdef00'::bytea, 43, 0); 1797 set_bit 1798---------------------- 1799 \x1234567890a3cdef00 1800(1 row) 1801 1802SELECT set_bit('\x1234567890abcdef00'::bytea, 99, 0); -- error 1803ERROR: index 99 out of valid range, 0..71 1804SELECT get_byte('\x1234567890abcdef00'::bytea, 3); 1805 get_byte 1806---------- 1807 120 1808(1 row) 1809 1810SELECT get_byte('\x1234567890abcdef00'::bytea, 99); -- error 1811ERROR: index 99 out of valid range, 0..8 1812SELECT set_byte('\x1234567890abcdef00'::bytea, 7, 11); 1813 set_byte 1814---------------------- 1815 \x1234567890abcd0b00 1816(1 row) 1817 1818SELECT set_byte('\x1234567890abcdef00'::bytea, 99, 11); -- error 1819ERROR: index 99 out of valid range, 0..8 1820-- 1821-- test behavior of escape_string_warning and standard_conforming_strings options 1822-- 1823set escape_string_warning = off; 1824set standard_conforming_strings = off; 1825show escape_string_warning; 1826 escape_string_warning 1827----------------------- 1828 off 1829(1 row) 1830 1831show standard_conforming_strings; 1832 standard_conforming_strings 1833----------------------------- 1834 off 1835(1 row) 1836 1837set escape_string_warning = on; 1838set standard_conforming_strings = on; 1839show escape_string_warning; 1840 escape_string_warning 1841----------------------- 1842 on 1843(1 row) 1844 1845show standard_conforming_strings; 1846 standard_conforming_strings 1847----------------------------- 1848 on 1849(1 row) 1850 1851select 'a\bcd' as f1, 'a\b''cd' as f2, 'a\b''''cd' as f3, 'abcd\' as f4, 'ab\''cd' as f5, '\\' as f6; 1852 f1 | f2 | f3 | f4 | f5 | f6 1853-------+--------+---------+-------+--------+---- 1854 a\bcd | a\b'cd | a\b''cd | abcd\ | ab\'cd | \\ 1855(1 row) 1856 1857set standard_conforming_strings = off; 1858select 'a\\bcd' as f1, 'a\\b\'cd' as f2, 'a\\b\'''cd' as f3, 'abcd\\' as f4, 'ab\\\'cd' as f5, '\\\\' as f6; 1859WARNING: nonstandard use of \\ in a string literal 1860LINE 1: select 'a\\bcd' as f1, 'a\\b\'cd' as f2, 'a\\b\'''cd' as f3,... 1861 ^ 1862HINT: Use the escape string syntax for backslashes, e.g., E'\\'. 1863WARNING: nonstandard use of \\ in a string literal 1864LINE 1: select 'a\\bcd' as f1, 'a\\b\'cd' as f2, 'a\\b\'''cd' as f3,... 1865 ^ 1866HINT: Use the escape string syntax for backslashes, e.g., E'\\'. 1867WARNING: nonstandard use of \\ in a string literal 1868LINE 1: select 'a\\bcd' as f1, 'a\\b\'cd' as f2, 'a\\b\'''cd' as f3,... 1869 ^ 1870HINT: Use the escape string syntax for backslashes, e.g., E'\\'. 1871WARNING: nonstandard use of \\ in a string literal 1872LINE 1: ...bcd' as f1, 'a\\b\'cd' as f2, 'a\\b\'''cd' as f3, 'abcd\\' ... 1873 ^ 1874HINT: Use the escape string syntax for backslashes, e.g., E'\\'. 1875WARNING: nonstandard use of \\ in a string literal 1876LINE 1: ...'cd' as f2, 'a\\b\'''cd' as f3, 'abcd\\' as f4, 'ab\\\'cd'... 1877 ^ 1878HINT: Use the escape string syntax for backslashes, e.g., E'\\'. 1879WARNING: nonstandard use of \\ in a string literal 1880LINE 1: ...'''cd' as f3, 'abcd\\' as f4, 'ab\\\'cd' as f5, '\\\\' as ... 1881 ^ 1882HINT: Use the escape string syntax for backslashes, e.g., E'\\'. 1883 f1 | f2 | f3 | f4 | f5 | f6 1884-------+--------+---------+-------+--------+---- 1885 a\bcd | a\b'cd | a\b''cd | abcd\ | ab\'cd | \\ 1886(1 row) 1887 1888set escape_string_warning = off; 1889set standard_conforming_strings = on; 1890select 'a\bcd' as f1, 'a\b''cd' as f2, 'a\b''''cd' as f3, 'abcd\' as f4, 'ab\''cd' as f5, '\\' as f6; 1891 f1 | f2 | f3 | f4 | f5 | f6 1892-------+--------+---------+-------+--------+---- 1893 a\bcd | a\b'cd | a\b''cd | abcd\ | ab\'cd | \\ 1894(1 row) 1895 1896set standard_conforming_strings = off; 1897select 'a\\bcd' as f1, 'a\\b\'cd' as f2, 'a\\b\'''cd' as f3, 'abcd\\' as f4, 'ab\\\'cd' as f5, '\\\\' as f6; 1898 f1 | f2 | f3 | f4 | f5 | f6 1899-------+--------+---------+-------+--------+---- 1900 a\bcd | a\b'cd | a\b''cd | abcd\ | ab\'cd | \\ 1901(1 row) 1902 1903-- 1904-- Additional string functions 1905-- 1906SET bytea_output TO escape; 1907SELECT initcap('hi THOMAS'); 1908 initcap 1909----------- 1910 Hi Thomas 1911(1 row) 1912 1913SELECT lpad('hi', 5, 'xy'); 1914 lpad 1915------- 1916 xyxhi 1917(1 row) 1918 1919SELECT lpad('hi', 5); 1920 lpad 1921------- 1922 hi 1923(1 row) 1924 1925SELECT lpad('hi', -5, 'xy'); 1926 lpad 1927------ 1928 1929(1 row) 1930 1931SELECT lpad('hello', 2); 1932 lpad 1933------ 1934 he 1935(1 row) 1936 1937SELECT lpad('hi', 5, ''); 1938 lpad 1939------ 1940 hi 1941(1 row) 1942 1943SELECT rpad('hi', 5, 'xy'); 1944 rpad 1945------- 1946 hixyx 1947(1 row) 1948 1949SELECT rpad('hi', 5); 1950 rpad 1951------- 1952 hi 1953(1 row) 1954 1955SELECT rpad('hi', -5, 'xy'); 1956 rpad 1957------ 1958 1959(1 row) 1960 1961SELECT rpad('hello', 2); 1962 rpad 1963------ 1964 he 1965(1 row) 1966 1967SELECT rpad('hi', 5, ''); 1968 rpad 1969------ 1970 hi 1971(1 row) 1972 1973SELECT ltrim('zzzytrim', 'xyz'); 1974 ltrim 1975------- 1976 trim 1977(1 row) 1978 1979SELECT translate('', '14', 'ax'); 1980 translate 1981----------- 1982 1983(1 row) 1984 1985SELECT translate('12345', '14', 'ax'); 1986 translate 1987----------- 1988 a23x5 1989(1 row) 1990 1991SELECT ascii('x'); 1992 ascii 1993------- 1994 120 1995(1 row) 1996 1997SELECT ascii(''); 1998 ascii 1999------- 2000 0 2001(1 row) 2002 2003SELECT chr(65); 2004 chr 2005----- 2006 A 2007(1 row) 2008 2009SELECT chr(0); 2010ERROR: null character not permitted 2011SELECT repeat('Pg', 4); 2012 repeat 2013---------- 2014 PgPgPgPg 2015(1 row) 2016 2017SELECT repeat('Pg', -4); 2018 repeat 2019-------- 2020 2021(1 row) 2022 2023SELECT SUBSTRING('1234567890'::bytea FROM 3) "34567890"; 2024 34567890 2025---------- 2026 34567890 2027(1 row) 2028 2029SELECT SUBSTRING('1234567890'::bytea FROM 4 FOR 3) AS "456"; 2030 456 2031----- 2032 456 2033(1 row) 2034 2035SELECT SUBSTRING('string'::bytea FROM 2 FOR 2147483646) AS "tring"; 2036 tring 2037------- 2038 tring 2039(1 row) 2040 2041SELECT SUBSTRING('string'::bytea FROM -10 FOR 2147483646) AS "string"; 2042 string 2043-------- 2044 string 2045(1 row) 2046 2047SELECT SUBSTRING('string'::bytea FROM -10 FOR -2147483646) AS "error"; 2048ERROR: negative substring length not allowed 2049SELECT trim(E'\\000'::bytea from E'\\000Tom\\000'::bytea); 2050 btrim 2051------- 2052 Tom 2053(1 row) 2054 2055SELECT btrim(E'\\000trim\\000'::bytea, E'\\000'::bytea); 2056 btrim 2057------- 2058 trim 2059(1 row) 2060 2061SELECT btrim(''::bytea, E'\\000'::bytea); 2062 btrim 2063------- 2064 2065(1 row) 2066 2067SELECT btrim(E'\\000trim\\000'::bytea, ''::bytea); 2068 btrim 2069-------------- 2070 \000trim\000 2071(1 row) 2072 2073SELECT encode(overlay(E'Th\\000omas'::bytea placing E'Th\\001omas'::bytea from 2),'escape'); 2074 encode 2075------------- 2076 TTh\x01omas 2077(1 row) 2078 2079SELECT encode(overlay(E'Th\\000omas'::bytea placing E'\\002\\003'::bytea from 8),'escape'); 2080 encode 2081-------------------- 2082 Th\000omas\x02\x03 2083(1 row) 2084 2085SELECT encode(overlay(E'Th\\000omas'::bytea placing E'\\002\\003'::bytea from 5 for 3),'escape'); 2086 encode 2087----------------- 2088 Th\000o\x02\x03 2089(1 row) 2090 2091