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' SIMILAR 'a#"(b_d)#"%' ESCAPE '#') AS "bcd"; 416 bcd 417----- 418 bcd 419(1 row) 420 421-- obsolete SQL99 syntax 422SELECT SUBSTRING('abcdefg' FROM 'a#"(b_d)#"%' FOR '#') AS "bcd"; 423 bcd 424----- 425 bcd 426(1 row) 427 428-- No match should return NULL 429SELECT SUBSTRING('abcdefg' SIMILAR '#"(b_d)#"%' ESCAPE '#') IS NULL AS "True"; 430 True 431------ 432 t 433(1 row) 434 435-- Null inputs should return NULL 436SELECT SUBSTRING('abcdefg' SIMILAR '%' ESCAPE NULL) IS NULL AS "True"; 437 True 438------ 439 t 440(1 row) 441 442SELECT SUBSTRING(NULL SIMILAR '%' ESCAPE '#') IS NULL AS "True"; 443 True 444------ 445 t 446(1 row) 447 448SELECT SUBSTRING('abcdefg' SIMILAR NULL ESCAPE '#') IS NULL AS "True"; 449 True 450------ 451 t 452(1 row) 453 454-- The first and last parts should act non-greedy 455SELECT SUBSTRING('abcdefg' SIMILAR 'a#"%#"g' ESCAPE '#') AS "bcdef"; 456 bcdef 457------- 458 bcdef 459(1 row) 460 461SELECT SUBSTRING('abcdefg' SIMILAR 'a*#"%#"g*' ESCAPE '#') AS "abcdefg"; 462 abcdefg 463--------- 464 abcdefg 465(1 row) 466 467-- Vertical bar in any part affects only that part 468SELECT SUBSTRING('abcdefg' SIMILAR 'a|b#"%#"g' ESCAPE '#') AS "bcdef"; 469 bcdef 470------- 471 bcdef 472(1 row) 473 474SELECT SUBSTRING('abcdefg' SIMILAR 'a#"%#"x|g' ESCAPE '#') AS "bcdef"; 475 bcdef 476------- 477 bcdef 478(1 row) 479 480SELECT SUBSTRING('abcdefg' SIMILAR 'a#"%|ab#"g' ESCAPE '#') AS "bcdef"; 481 bcdef 482------- 483 bcdef 484(1 row) 485 486-- Can't have more than two part separators 487SELECT SUBSTRING('abcdefg' SIMILAR 'a*#"%#"g*#"x' ESCAPE '#') AS "error"; 488ERROR: SQL regular expression may not contain more than two escape-double-quote separators 489CONTEXT: SQL function "substring" statement 1 490-- Postgres extension: with 0 or 1 separator, assume parts 1 and 3 are empty 491SELECT SUBSTRING('abcdefg' SIMILAR 'a#"%g' ESCAPE '#') AS "bcdefg"; 492 bcdefg 493-------- 494 bcdefg 495(1 row) 496 497SELECT SUBSTRING('abcdefg' SIMILAR 'a%g' ESCAPE '#') AS "abcdefg"; 498 abcdefg 499--------- 500 abcdefg 501(1 row) 502 503-- substring() with just two arguments is not allowed by SQL spec; 504-- we accept it, but we interpret the pattern as a POSIX regexp not SQL 505SELECT SUBSTRING('abcdefg' FROM 'c.e') AS "cde"; 506 cde 507----- 508 cde 509(1 row) 510 511-- With a parenthesized subexpression, return only what matches the subexpr 512SELECT SUBSTRING('abcdefg' FROM 'b(.*)f') AS "cde"; 513 cde 514----- 515 cde 516(1 row) 517 518-- Check behavior of SIMILAR TO, which uses largely the same regexp variant 519SELECT 'abcdefg' SIMILAR TO '_bcd%' AS true; 520 true 521------ 522 t 523(1 row) 524 525SELECT 'abcdefg' SIMILAR TO 'bcd%' AS false; 526 false 527------- 528 f 529(1 row) 530 531SELECT 'abcdefg' SIMILAR TO '_bcd#%' ESCAPE '#' AS false; 532 false 533------- 534 f 535(1 row) 536 537SELECT 'abcd%' SIMILAR TO '_bcd#%' ESCAPE '#' AS true; 538 true 539------ 540 t 541(1 row) 542 543-- Postgres uses '\' as the default escape character, which is not per spec 544SELECT 'abcdefg' SIMILAR TO '_bcd\%' AS false; 545 false 546------- 547 f 548(1 row) 549 550-- and an empty string to mean "no escape", which is also not per spec 551SELECT 'abcd\efg' SIMILAR TO '_bcd\%' ESCAPE '' AS true; 552 true 553------ 554 t 555(1 row) 556 557-- these behaviors are per spec, though: 558SELECT 'abcdefg' SIMILAR TO '_bcd%' ESCAPE NULL AS null; 559 null 560------ 561 562(1 row) 563 564SELECT 'abcdefg' SIMILAR TO '_bcd#%' ESCAPE '##' AS error; 565ERROR: invalid escape string 566HINT: Escape string must be empty or one character. 567-- Test back reference in regexp_replace 568SELECT regexp_replace('1112223333', E'(\\d{3})(\\d{3})(\\d{4})', E'(\\1) \\2-\\3'); 569 regexp_replace 570---------------- 571 (111) 222-3333 572(1 row) 573 574SELECT regexp_replace('AAA BBB CCC ', E'\\s+', ' ', 'g'); 575 regexp_replace 576---------------- 577 AAA BBB CCC 578(1 row) 579 580SELECT regexp_replace('AAA', '^|$', 'Z', 'g'); 581 regexp_replace 582---------------- 583 ZAAAZ 584(1 row) 585 586SELECT regexp_replace('AAA aaa', 'A+', 'Z', 'gi'); 587 regexp_replace 588---------------- 589 Z Z 590(1 row) 591 592-- invalid regexp option 593SELECT regexp_replace('AAA aaa', 'A+', 'Z', 'z'); 594ERROR: invalid regular expression option: "z" 595-- set so we can tell NULL from empty string 596\pset null '\\N' 597-- return all matches from regexp 598SELECT regexp_matches('foobarbequebaz', $re$(bar)(beque)$re$); 599 regexp_matches 600---------------- 601 {bar,beque} 602(1 row) 603 604-- test case insensitive 605SELECT regexp_matches('foObARbEqUEbAz', $re$(bar)(beque)$re$, 'i'); 606 regexp_matches 607---------------- 608 {bAR,bEqUE} 609(1 row) 610 611-- global option - more than one match 612SELECT regexp_matches('foobarbequebazilbarfbonk', $re$(b[^b]+)(b[^b]+)$re$, 'g'); 613 regexp_matches 614---------------- 615 {bar,beque} 616 {bazil,barf} 617(2 rows) 618 619-- empty capture group (matched empty string) 620SELECT regexp_matches('foobarbequebaz', $re$(bar)(.*)(beque)$re$); 621 regexp_matches 622---------------- 623 {bar,"",beque} 624(1 row) 625 626-- no match 627SELECT regexp_matches('foobarbequebaz', $re$(bar)(.+)(beque)$re$); 628 regexp_matches 629---------------- 630(0 rows) 631 632-- optional capture group did not match, null entry in array 633SELECT regexp_matches('foobarbequebaz', $re$(bar)(.+)?(beque)$re$); 634 regexp_matches 635------------------ 636 {bar,NULL,beque} 637(1 row) 638 639-- no capture groups 640SELECT regexp_matches('foobarbequebaz', $re$barbeque$re$); 641 regexp_matches 642---------------- 643 {barbeque} 644(1 row) 645 646-- start/end-of-line matches are of zero length 647SELECT regexp_matches('foo' || chr(10) || 'bar' || chr(10) || 'bequq' || chr(10) || 'baz', '^', 'mg'); 648 regexp_matches 649---------------- 650 {""} 651 {""} 652 {""} 653 {""} 654(4 rows) 655 656SELECT regexp_matches('foo' || chr(10) || 'bar' || chr(10) || 'bequq' || chr(10) || 'baz', '$', 'mg'); 657 regexp_matches 658---------------- 659 {""} 660 {""} 661 {""} 662 {""} 663(4 rows) 664 665SELECT regexp_matches('1' || chr(10) || '2' || chr(10) || '3' || chr(10) || '4' || chr(10), '^.?', 'mg'); 666 regexp_matches 667---------------- 668 {1} 669 {2} 670 {3} 671 {4} 672 {""} 673(5 rows) 674 675SELECT regexp_matches(chr(10) || '1' || chr(10) || '2' || chr(10) || '3' || chr(10) || '4' || chr(10), '.?$', 'mg'); 676 regexp_matches 677---------------- 678 {""} 679 {1} 680 {""} 681 {2} 682 {""} 683 {3} 684 {""} 685 {4} 686 {""} 687 {""} 688(10 rows) 689 690SELECT regexp_matches(chr(10) || '1' || chr(10) || '2' || chr(10) || '3' || chr(10) || '4', '.?$', 'mg'); 691 regexp_matches 692---------------- 693 {""} 694 {1} 695 {""} 696 {2} 697 {""} 698 {3} 699 {""} 700 {4} 701 {""} 702(9 rows) 703 704-- give me errors 705SELECT regexp_matches('foobarbequebaz', $re$(bar)(beque)$re$, 'gz'); 706ERROR: invalid regular expression option: "z" 707SELECT regexp_matches('foobarbequebaz', $re$(barbeque$re$); 708ERROR: invalid regular expression: parentheses () not balanced 709SELECT regexp_matches('foobarbequebaz', $re$(bar)(beque){2,1}$re$); 710ERROR: invalid regular expression: invalid repetition count(s) 711-- split string on regexp 712SELECT foo, length(foo) FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', $re$\s+$re$) AS foo; 713 foo | length 714-------+-------- 715 the | 3 716 quick | 5 717 brown | 5 718 fox | 3 719 jumps | 5 720 over | 4 721 the | 3 722 lazy | 4 723 dog | 3 724(9 rows) 725 726SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', $re$\s+$re$); 727 regexp_split_to_array 728----------------------------------------------- 729 {the,quick,brown,fox,jumps,over,the,lazy,dog} 730(1 row) 731 732SELECT foo, length(foo) FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', $re$\s*$re$) AS foo; 733 foo | length 734-----+-------- 735 t | 1 736 h | 1 737 e | 1 738 q | 1 739 u | 1 740 i | 1 741 c | 1 742 k | 1 743 b | 1 744 r | 1 745 o | 1 746 w | 1 747 n | 1 748 f | 1 749 o | 1 750 x | 1 751 j | 1 752 u | 1 753 m | 1 754 p | 1 755 s | 1 756 o | 1 757 v | 1 758 e | 1 759 r | 1 760 t | 1 761 h | 1 762 e | 1 763 l | 1 764 a | 1 765 z | 1 766 y | 1 767 d | 1 768 o | 1 769 g | 1 770(35 rows) 771 772SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', $re$\s*$re$); 773 regexp_split_to_array 774------------------------------------------------------------------------- 775 {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} 776(1 row) 777 778SELECT foo, length(foo) FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', '') AS foo; 779 foo | length 780-----+-------- 781 t | 1 782 h | 1 783 e | 1 784 | 1 785 q | 1 786 u | 1 787 i | 1 788 c | 1 789 k | 1 790 | 1 791 b | 1 792 r | 1 793 o | 1 794 w | 1 795 n | 1 796 | 1 797 f | 1 798 o | 1 799 x | 1 800 | 1 801 j | 1 802 u | 1 803 m | 1 804 p | 1 805 s | 1 806 | 1 807 o | 1 808 v | 1 809 e | 1 810 r | 1 811 | 1 812 t | 1 813 h | 1 814 e | 1 815 | 1 816 l | 1 817 a | 1 818 z | 1 819 y | 1 820 | 1 821 d | 1 822 o | 1 823 g | 1 824(43 rows) 825 826SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', ''); 827 regexp_split_to_array 828--------------------------------------------------------------------------------------------------------- 829 {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} 830(1 row) 831 832-- case insensitive 833SELECT foo, length(foo) FROM regexp_split_to_table('thE QUick bROWn FOx jUMPs ovEr The lazy dOG', 'e', 'i') AS foo; 834 foo | length 835---------------------------+-------- 836 th | 2 837 QUick bROWn FOx jUMPs ov | 25 838 r Th | 4 839 lazy dOG | 9 840(4 rows) 841 842SELECT regexp_split_to_array('thE QUick bROWn FOx jUMPs ovEr The lazy dOG', 'e', 'i'); 843 regexp_split_to_array 844----------------------------------------------------- 845 {th," QUick bROWn FOx jUMPs ov","r Th"," lazy dOG"} 846(1 row) 847 848-- no match of pattern 849SELECT foo, length(foo) FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', 'nomatch') AS foo; 850 foo | length 851---------------------------------------------+-------- 852 the quick brown fox jumps over the lazy dog | 43 853(1 row) 854 855SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', 'nomatch'); 856 regexp_split_to_array 857------------------------------------------------- 858 {"the quick brown fox jumps over the lazy dog"} 859(1 row) 860 861-- some corner cases 862SELECT regexp_split_to_array('123456','1'); 863 regexp_split_to_array 864----------------------- 865 {"",23456} 866(1 row) 867 868SELECT regexp_split_to_array('123456','6'); 869 regexp_split_to_array 870----------------------- 871 {12345,""} 872(1 row) 873 874SELECT regexp_split_to_array('123456','.'); 875 regexp_split_to_array 876------------------------ 877 {"","","","","","",""} 878(1 row) 879 880SELECT regexp_split_to_array('123456',''); 881 regexp_split_to_array 882----------------------- 883 {1,2,3,4,5,6} 884(1 row) 885 886SELECT regexp_split_to_array('123456','(?:)'); 887 regexp_split_to_array 888----------------------- 889 {1,2,3,4,5,6} 890(1 row) 891 892SELECT regexp_split_to_array('1',''); 893 regexp_split_to_array 894----------------------- 895 {1} 896(1 row) 897 898-- errors 899SELECT foo, length(foo) FROM regexp_split_to_table('thE QUick bROWn FOx jUMPs ovEr The lazy dOG', 'e', 'zippy') AS foo; 900ERROR: invalid regular expression option: "z" 901SELECT regexp_split_to_array('thE QUick bROWn FOx jUMPs ovEr The lazy dOG', 'e', 'iz'); 902ERROR: invalid regular expression option: "z" 903-- global option meaningless for regexp_split 904SELECT foo, length(foo) FROM regexp_split_to_table('thE QUick bROWn FOx jUMPs ovEr The lazy dOG', 'e', 'g') AS foo; 905ERROR: regexp_split_to_table() does not support the "global" option 906SELECT regexp_split_to_array('thE QUick bROWn FOx jUMPs ovEr The lazy dOG', 'e', 'g'); 907ERROR: regexp_split_to_array() does not support the "global" option 908-- change NULL-display back 909\pset null '' 910-- E021-11 position expression 911SELECT POSITION('4' IN '1234567890') = '4' AS "4"; 912 4 913--- 914 t 915(1 row) 916 917SELECT POSITION('5' IN '1234567890') = '5' AS "5"; 918 5 919--- 920 t 921(1 row) 922 923-- T312 character overlay function 924SELECT OVERLAY('abcdef' PLACING '45' FROM 4) AS "abc45f"; 925 abc45f 926-------- 927 abc45f 928(1 row) 929 930SELECT OVERLAY('yabadoo' PLACING 'daba' FROM 5) AS "yabadaba"; 931 yabadaba 932---------- 933 yabadaba 934(1 row) 935 936SELECT OVERLAY('yabadoo' PLACING 'daba' FROM 5 FOR 0) AS "yabadabadoo"; 937 yabadabadoo 938------------- 939 yabadabadoo 940(1 row) 941 942SELECT OVERLAY('babosa' PLACING 'ubb' FROM 2 FOR 4) AS "bubba"; 943 bubba 944------- 945 bubba 946(1 row) 947 948-- 949-- test LIKE 950-- Be sure to form every test as a LIKE/NOT LIKE pair. 951-- 952-- simplest examples 953-- E061-04 like predicate 954SELECT 'hawkeye' LIKE 'h%' AS "true"; 955 true 956------ 957 t 958(1 row) 959 960SELECT 'hawkeye' NOT LIKE 'h%' AS "false"; 961 false 962------- 963 f 964(1 row) 965 966SELECT 'hawkeye' LIKE 'H%' AS "false"; 967 false 968------- 969 f 970(1 row) 971 972SELECT 'hawkeye' NOT LIKE 'H%' AS "true"; 973 true 974------ 975 t 976(1 row) 977 978SELECT 'hawkeye' LIKE 'indio%' AS "false"; 979 false 980------- 981 f 982(1 row) 983 984SELECT 'hawkeye' NOT LIKE 'indio%' AS "true"; 985 true 986------ 987 t 988(1 row) 989 990SELECT 'hawkeye' LIKE 'h%eye' AS "true"; 991 true 992------ 993 t 994(1 row) 995 996SELECT 'hawkeye' NOT LIKE 'h%eye' AS "false"; 997 false 998------- 999 f 1000(1 row) 1001 1002SELECT 'indio' LIKE '_ndio' AS "true"; 1003 true 1004------ 1005 t 1006(1 row) 1007 1008SELECT 'indio' NOT LIKE '_ndio' AS "false"; 1009 false 1010------- 1011 f 1012(1 row) 1013 1014SELECT 'indio' LIKE 'in__o' AS "true"; 1015 true 1016------ 1017 t 1018(1 row) 1019 1020SELECT 'indio' NOT LIKE 'in__o' AS "false"; 1021 false 1022------- 1023 f 1024(1 row) 1025 1026SELECT 'indio' LIKE 'in_o' AS "false"; 1027 false 1028------- 1029 f 1030(1 row) 1031 1032SELECT 'indio' NOT LIKE 'in_o' AS "true"; 1033 true 1034------ 1035 t 1036(1 row) 1037 1038SELECT 'abc'::name LIKE '_b_' AS "true"; 1039 true 1040------ 1041 t 1042(1 row) 1043 1044SELECT 'abc'::name NOT LIKE '_b_' AS "false"; 1045 false 1046------- 1047 f 1048(1 row) 1049 1050SELECT 'abc'::bytea LIKE '_b_'::bytea AS "true"; 1051 true 1052------ 1053 t 1054(1 row) 1055 1056SELECT 'abc'::bytea NOT LIKE '_b_'::bytea AS "false"; 1057 false 1058------- 1059 f 1060(1 row) 1061 1062-- unused escape character 1063SELECT 'hawkeye' LIKE 'h%' ESCAPE '#' AS "true"; 1064 true 1065------ 1066 t 1067(1 row) 1068 1069SELECT 'hawkeye' NOT LIKE 'h%' ESCAPE '#' AS "false"; 1070 false 1071------- 1072 f 1073(1 row) 1074 1075SELECT 'indio' LIKE 'ind_o' ESCAPE '$' AS "true"; 1076 true 1077------ 1078 t 1079(1 row) 1080 1081SELECT 'indio' NOT LIKE 'ind_o' ESCAPE '$' AS "false"; 1082 false 1083------- 1084 f 1085(1 row) 1086 1087-- escape character 1088-- E061-05 like predicate with escape clause 1089SELECT 'h%' LIKE 'h#%' ESCAPE '#' AS "true"; 1090 true 1091------ 1092 t 1093(1 row) 1094 1095SELECT 'h%' NOT LIKE 'h#%' ESCAPE '#' AS "false"; 1096 false 1097------- 1098 f 1099(1 row) 1100 1101SELECT 'h%wkeye' LIKE 'h#%' ESCAPE '#' AS "false"; 1102 false 1103------- 1104 f 1105(1 row) 1106 1107SELECT 'h%wkeye' NOT LIKE 'h#%' ESCAPE '#' AS "true"; 1108 true 1109------ 1110 t 1111(1 row) 1112 1113SELECT 'h%wkeye' LIKE 'h#%%' ESCAPE '#' AS "true"; 1114 true 1115------ 1116 t 1117(1 row) 1118 1119SELECT 'h%wkeye' NOT LIKE 'h#%%' ESCAPE '#' AS "false"; 1120 false 1121------- 1122 f 1123(1 row) 1124 1125SELECT 'h%awkeye' LIKE 'h#%a%k%e' ESCAPE '#' AS "true"; 1126 true 1127------ 1128 t 1129(1 row) 1130 1131SELECT 'h%awkeye' NOT LIKE 'h#%a%k%e' ESCAPE '#' AS "false"; 1132 false 1133------- 1134 f 1135(1 row) 1136 1137SELECT 'indio' LIKE '_ndio' ESCAPE '$' AS "true"; 1138 true 1139------ 1140 t 1141(1 row) 1142 1143SELECT 'indio' NOT LIKE '_ndio' ESCAPE '$' AS "false"; 1144 false 1145------- 1146 f 1147(1 row) 1148 1149SELECT 'i_dio' LIKE 'i$_d_o' ESCAPE '$' AS "true"; 1150 true 1151------ 1152 t 1153(1 row) 1154 1155SELECT 'i_dio' NOT LIKE 'i$_d_o' ESCAPE '$' AS "false"; 1156 false 1157------- 1158 f 1159(1 row) 1160 1161SELECT 'i_dio' LIKE 'i$_nd_o' ESCAPE '$' AS "false"; 1162 false 1163------- 1164 f 1165(1 row) 1166 1167SELECT 'i_dio' NOT LIKE 'i$_nd_o' ESCAPE '$' AS "true"; 1168 true 1169------ 1170 t 1171(1 row) 1172 1173SELECT 'i_dio' LIKE 'i$_d%o' ESCAPE '$' AS "true"; 1174 true 1175------ 1176 t 1177(1 row) 1178 1179SELECT 'i_dio' NOT LIKE 'i$_d%o' ESCAPE '$' AS "false"; 1180 false 1181------- 1182 f 1183(1 row) 1184 1185SELECT 'a_c'::bytea LIKE 'a$__'::bytea ESCAPE '$'::bytea AS "true"; 1186 true 1187------ 1188 t 1189(1 row) 1190 1191SELECT 'a_c'::bytea NOT LIKE 'a$__'::bytea ESCAPE '$'::bytea AS "false"; 1192 false 1193------- 1194 f 1195(1 row) 1196 1197-- escape character same as pattern character 1198SELECT 'maca' LIKE 'm%aca' ESCAPE '%' AS "true"; 1199 true 1200------ 1201 t 1202(1 row) 1203 1204SELECT 'maca' NOT LIKE 'm%aca' ESCAPE '%' AS "false"; 1205 false 1206------- 1207 f 1208(1 row) 1209 1210SELECT 'ma%a' LIKE 'm%a%%a' ESCAPE '%' AS "true"; 1211 true 1212------ 1213 t 1214(1 row) 1215 1216SELECT 'ma%a' NOT LIKE 'm%a%%a' ESCAPE '%' AS "false"; 1217 false 1218------- 1219 f 1220(1 row) 1221 1222SELECT 'bear' LIKE 'b_ear' ESCAPE '_' AS "true"; 1223 true 1224------ 1225 t 1226(1 row) 1227 1228SELECT 'bear' NOT LIKE 'b_ear' ESCAPE '_' AS "false"; 1229 false 1230------- 1231 f 1232(1 row) 1233 1234SELECT 'be_r' LIKE 'b_e__r' ESCAPE '_' AS "true"; 1235 true 1236------ 1237 t 1238(1 row) 1239 1240SELECT 'be_r' NOT LIKE 'b_e__r' ESCAPE '_' AS "false"; 1241 false 1242------- 1243 f 1244(1 row) 1245 1246SELECT 'be_r' LIKE '__e__r' ESCAPE '_' AS "false"; 1247 false 1248------- 1249 f 1250(1 row) 1251 1252SELECT 'be_r' NOT LIKE '__e__r' ESCAPE '_' AS "true"; 1253 true 1254------ 1255 t 1256(1 row) 1257 1258-- 1259-- test ILIKE (case-insensitive LIKE) 1260-- Be sure to form every test as an ILIKE/NOT ILIKE pair. 1261-- 1262SELECT 'hawkeye' ILIKE 'h%' AS "true"; 1263 true 1264------ 1265 t 1266(1 row) 1267 1268SELECT 'hawkeye' NOT ILIKE 'h%' AS "false"; 1269 false 1270------- 1271 f 1272(1 row) 1273 1274SELECT 'hawkeye' ILIKE 'H%' AS "true"; 1275 true 1276------ 1277 t 1278(1 row) 1279 1280SELECT 'hawkeye' NOT ILIKE 'H%' AS "false"; 1281 false 1282------- 1283 f 1284(1 row) 1285 1286SELECT 'hawkeye' ILIKE 'H%Eye' AS "true"; 1287 true 1288------ 1289 t 1290(1 row) 1291 1292SELECT 'hawkeye' NOT ILIKE 'H%Eye' AS "false"; 1293 false 1294------- 1295 f 1296(1 row) 1297 1298SELECT 'Hawkeye' ILIKE 'h%' AS "true"; 1299 true 1300------ 1301 t 1302(1 row) 1303 1304SELECT 'Hawkeye' NOT ILIKE 'h%' AS "false"; 1305 false 1306------- 1307 f 1308(1 row) 1309 1310SELECT 'ABC'::name ILIKE '_b_' AS "true"; 1311 true 1312------ 1313 t 1314(1 row) 1315 1316SELECT 'ABC'::name NOT ILIKE '_b_' AS "false"; 1317 false 1318------- 1319 f 1320(1 row) 1321 1322-- 1323-- test %/_ combination cases, cf bugs #4821 and #5478 1324-- 1325SELECT 'foo' LIKE '_%' as t, 'f' LIKE '_%' as t, '' LIKE '_%' as f; 1326 t | t | f 1327---+---+--- 1328 t | t | f 1329(1 row) 1330 1331SELECT 'foo' LIKE '%_' as t, 'f' LIKE '%_' as t, '' LIKE '%_' as f; 1332 t | t | f 1333---+---+--- 1334 t | t | f 1335(1 row) 1336 1337SELECT 'foo' LIKE '__%' as t, 'foo' LIKE '___%' as t, 'foo' LIKE '____%' as f; 1338 t | t | f 1339---+---+--- 1340 t | t | f 1341(1 row) 1342 1343SELECT 'foo' LIKE '%__' as t, 'foo' LIKE '%___' as t, 'foo' LIKE '%____' as f; 1344 t | t | f 1345---+---+--- 1346 t | t | f 1347(1 row) 1348 1349SELECT 'jack' LIKE '%____%' AS t; 1350 t 1351--- 1352 t 1353(1 row) 1354 1355-- 1356-- basic tests of LIKE with indexes 1357-- 1358CREATE TABLE texttest (a text PRIMARY KEY, b int); 1359SELECT * FROM texttest WHERE a LIKE '%1%'; 1360 a | b 1361---+--- 1362(0 rows) 1363 1364CREATE TABLE byteatest (a bytea PRIMARY KEY, b int); 1365SELECT * FROM byteatest WHERE a LIKE '%1%'; 1366 a | b 1367---+--- 1368(0 rows) 1369 1370DROP TABLE texttest, byteatest; 1371-- 1372-- test implicit type conversion 1373-- 1374-- E021-07 character concatenation 1375SELECT 'unknown' || ' and unknown' AS "Concat unknown types"; 1376 Concat unknown types 1377---------------------- 1378 unknown and unknown 1379(1 row) 1380 1381SELECT text 'text' || ' and unknown' AS "Concat text to unknown type"; 1382 Concat text to unknown type 1383----------------------------- 1384 text and unknown 1385(1 row) 1386 1387SELECT char(20) 'characters' || ' and text' AS "Concat char to unknown type"; 1388 Concat char to unknown type 1389----------------------------- 1390 characters and text 1391(1 row) 1392 1393SELECT text 'text' || char(20) ' and characters' AS "Concat text to char"; 1394 Concat text to char 1395--------------------- 1396 text and characters 1397(1 row) 1398 1399SELECT text 'text' || varchar ' and varchar' AS "Concat text to varchar"; 1400 Concat text to varchar 1401------------------------ 1402 text and varchar 1403(1 row) 1404 1405-- 1406-- test substr with toasted text values 1407-- 1408CREATE TABLE toasttest(f1 text); 1409insert into toasttest values(repeat('1234567890',10000)); 1410insert into toasttest values(repeat('1234567890',10000)); 1411-- 1412-- Ensure that some values are uncompressed, to test the faster substring 1413-- operation used in that case 1414-- 1415alter table toasttest alter column f1 set storage external; 1416insert into toasttest values(repeat('1234567890',10000)); 1417insert into toasttest values(repeat('1234567890',10000)); 1418-- If the starting position is zero or less, then return from the start of the string 1419-- adjusting the length to be consistent with the "negative start" per SQL. 1420SELECT substr(f1, -1, 5) from toasttest; 1421 substr 1422-------- 1423 123 1424 123 1425 123 1426 123 1427(4 rows) 1428 1429-- If the length is less than zero, an ERROR is thrown. 1430SELECT substr(f1, 5, -1) from toasttest; 1431ERROR: negative substring length not allowed 1432-- If no third argument (length) is provided, the length to the end of the 1433-- string is assumed. 1434SELECT substr(f1, 99995) from toasttest; 1435 substr 1436-------- 1437 567890 1438 567890 1439 567890 1440 567890 1441(4 rows) 1442 1443-- If start plus length is > string length, the result is truncated to 1444-- string length 1445SELECT substr(f1, 99995, 10) from toasttest; 1446 substr 1447-------- 1448 567890 1449 567890 1450 567890 1451 567890 1452(4 rows) 1453 1454TRUNCATE TABLE toasttest; 1455INSERT INTO toasttest values (repeat('1234567890',300)); 1456INSERT INTO toasttest values (repeat('1234567890',300)); 1457INSERT INTO toasttest values (repeat('1234567890',300)); 1458INSERT INTO toasttest values (repeat('1234567890',300)); 1459-- expect >0 blocks 1460SELECT pg_relation_size(reltoastrelid) = 0 AS is_empty 1461 FROM pg_class where relname = 'toasttest'; 1462 is_empty 1463---------- 1464 f 1465(1 row) 1466 1467TRUNCATE TABLE toasttest; 1468ALTER TABLE toasttest set (toast_tuple_target = 4080); 1469INSERT INTO toasttest values (repeat('1234567890',300)); 1470INSERT INTO toasttest values (repeat('1234567890',300)); 1471INSERT INTO toasttest values (repeat('1234567890',300)); 1472INSERT INTO toasttest values (repeat('1234567890',300)); 1473-- expect 0 blocks 1474SELECT pg_relation_size(reltoastrelid) = 0 AS is_empty 1475 FROM pg_class where relname = 'toasttest'; 1476 is_empty 1477---------- 1478 t 1479(1 row) 1480 1481DROP TABLE toasttest; 1482-- 1483-- test substr with toasted bytea values 1484-- 1485CREATE TABLE toasttest(f1 bytea); 1486insert into toasttest values(decode(repeat('1234567890',10000),'escape')); 1487insert into toasttest values(decode(repeat('1234567890',10000),'escape')); 1488-- 1489-- Ensure that some values are uncompressed, to test the faster substring 1490-- operation used in that case 1491-- 1492alter table toasttest alter column f1 set storage external; 1493insert into toasttest values(decode(repeat('1234567890',10000),'escape')); 1494insert into toasttest values(decode(repeat('1234567890',10000),'escape')); 1495-- If the starting position is zero or less, then return from the start of the string 1496-- adjusting the length to be consistent with the "negative start" per SQL. 1497SELECT substr(f1, -1, 5) from toasttest; 1498 substr 1499-------- 1500 123 1501 123 1502 123 1503 123 1504(4 rows) 1505 1506-- If the length is less than zero, an ERROR is thrown. 1507SELECT substr(f1, 5, -1) from toasttest; 1508ERROR: negative substring length not allowed 1509-- If no third argument (length) is provided, the length to the end of the 1510-- string is assumed. 1511SELECT substr(f1, 99995) from toasttest; 1512 substr 1513-------- 1514 567890 1515 567890 1516 567890 1517 567890 1518(4 rows) 1519 1520-- If start plus length is > string length, the result is truncated to 1521-- string length 1522SELECT substr(f1, 99995, 10) from toasttest; 1523 substr 1524-------- 1525 567890 1526 567890 1527 567890 1528 567890 1529(4 rows) 1530 1531DROP TABLE toasttest; 1532-- test internally compressing datums 1533-- this tests compressing a datum to a very small size which exercises a 1534-- corner case in packed-varlena handling: even though small, the compressed 1535-- datum must be given a 4-byte header because there are no bits to indicate 1536-- compression in a 1-byte header 1537CREATE TABLE toasttest (c char(4096)); 1538INSERT INTO toasttest VALUES('x'); 1539SELECT length(c), c::text FROM toasttest; 1540 length | c 1541--------+--- 1542 1 | x 1543(1 row) 1544 1545SELECT c FROM toasttest; 1546 c 1547------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 1548 x 1549(1 row) 1550 1551DROP TABLE toasttest; 1552-- 1553-- test length 1554-- 1555SELECT length('abcdef') AS "length_6"; 1556 length_6 1557---------- 1558 6 1559(1 row) 1560 1561-- 1562-- test strpos 1563-- 1564SELECT strpos('abcdef', 'cd') AS "pos_3"; 1565 pos_3 1566------- 1567 3 1568(1 row) 1569 1570SELECT strpos('abcdef', 'xy') AS "pos_0"; 1571 pos_0 1572------- 1573 0 1574(1 row) 1575 1576SELECT strpos('abcdef', '') AS "pos_1"; 1577 pos_1 1578------- 1579 1 1580(1 row) 1581 1582SELECT strpos('', 'xy') AS "pos_0"; 1583 pos_0 1584------- 1585 0 1586(1 row) 1587 1588SELECT strpos('', '') AS "pos_1"; 1589 pos_1 1590------- 1591 1 1592(1 row) 1593 1594-- 1595-- test replace 1596-- 1597SELECT replace('abcdef', 'de', '45') AS "abc45f"; 1598 abc45f 1599-------- 1600 abc45f 1601(1 row) 1602 1603SELECT replace('yabadabadoo', 'ba', '123') AS "ya123da123doo"; 1604 ya123da123doo 1605--------------- 1606 ya123da123doo 1607(1 row) 1608 1609SELECT replace('yabadoo', 'bad', '') AS "yaoo"; 1610 yaoo 1611------ 1612 yaoo 1613(1 row) 1614 1615-- 1616-- test split_part 1617-- 1618select split_part('','@',1) AS "empty string"; 1619 empty string 1620-------------- 1621 1622(1 row) 1623 1624select split_part('','@',-1) AS "empty string"; 1625 empty string 1626-------------- 1627 1628(1 row) 1629 1630select split_part('joeuser@mydatabase','',1) AS "joeuser@mydatabase"; 1631 joeuser@mydatabase 1632-------------------- 1633 joeuser@mydatabase 1634(1 row) 1635 1636select split_part('joeuser@mydatabase','',2) AS "empty string"; 1637 empty string 1638-------------- 1639 1640(1 row) 1641 1642select split_part('joeuser@mydatabase','',-1) AS "joeuser@mydatabase"; 1643 joeuser@mydatabase 1644-------------------- 1645 joeuser@mydatabase 1646(1 row) 1647 1648select split_part('joeuser@mydatabase','',-2) AS "empty string"; 1649 empty string 1650-------------- 1651 1652(1 row) 1653 1654select split_part('joeuser@mydatabase','@',0) AS "an error"; 1655ERROR: field position must not be zero 1656select split_part('joeuser@mydatabase','@@',1) AS "joeuser@mydatabase"; 1657 joeuser@mydatabase 1658-------------------- 1659 joeuser@mydatabase 1660(1 row) 1661 1662select split_part('joeuser@mydatabase','@@',2) AS "empty string"; 1663 empty string 1664-------------- 1665 1666(1 row) 1667 1668select split_part('joeuser@mydatabase','@',1) AS "joeuser"; 1669 joeuser 1670--------- 1671 joeuser 1672(1 row) 1673 1674select split_part('joeuser@mydatabase','@',2) AS "mydatabase"; 1675 mydatabase 1676------------ 1677 mydatabase 1678(1 row) 1679 1680select split_part('joeuser@mydatabase','@',3) AS "empty string"; 1681 empty string 1682-------------- 1683 1684(1 row) 1685 1686select split_part('@joeuser@mydatabase@','@',2) AS "joeuser"; 1687 joeuser 1688--------- 1689 joeuser 1690(1 row) 1691 1692select split_part('joeuser@mydatabase','@',-1) AS "mydatabase"; 1693 mydatabase 1694------------ 1695 mydatabase 1696(1 row) 1697 1698select split_part('joeuser@mydatabase','@',-2) AS "joeuser"; 1699 joeuser 1700--------- 1701 joeuser 1702(1 row) 1703 1704select split_part('joeuser@mydatabase','@',-3) AS "empty string"; 1705 empty string 1706-------------- 1707 1708(1 row) 1709 1710select split_part('@joeuser@mydatabase@','@',-2) AS "mydatabase"; 1711 mydatabase 1712------------ 1713 mydatabase 1714(1 row) 1715 1716-- 1717-- test to_hex 1718-- 1719select to_hex(256*256*256 - 1) AS "ffffff"; 1720 ffffff 1721-------- 1722 ffffff 1723(1 row) 1724 1725select to_hex(256::bigint*256::bigint*256::bigint*256::bigint - 1) AS "ffffffff"; 1726 ffffffff 1727---------- 1728 ffffffff 1729(1 row) 1730 1731-- 1732-- MD5 test suite - from IETF RFC 1321 1733-- (see: ftp://ftp.rfc-editor.org/in-notes/rfc1321.txt) 1734-- 1735select md5('') = 'd41d8cd98f00b204e9800998ecf8427e' AS "TRUE"; 1736 TRUE 1737------ 1738 t 1739(1 row) 1740 1741select md5('a') = '0cc175b9c0f1b6a831c399e269772661' AS "TRUE"; 1742 TRUE 1743------ 1744 t 1745(1 row) 1746 1747select md5('abc') = '900150983cd24fb0d6963f7d28e17f72' AS "TRUE"; 1748 TRUE 1749------ 1750 t 1751(1 row) 1752 1753select md5('message digest') = 'f96b697d7cb7938d525a2f31aaf161d0' AS "TRUE"; 1754 TRUE 1755------ 1756 t 1757(1 row) 1758 1759select md5('abcdefghijklmnopqrstuvwxyz') = 'c3fcd3d76192e4007dfb496cca67e13b' AS "TRUE"; 1760 TRUE 1761------ 1762 t 1763(1 row) 1764 1765select md5('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789') = 'd174ab98d277d9f5a5611c2c9f419d9f' AS "TRUE"; 1766 TRUE 1767------ 1768 t 1769(1 row) 1770 1771select md5('12345678901234567890123456789012345678901234567890123456789012345678901234567890') = '57edf4a22be3c955ac49da2e2107b67a' AS "TRUE"; 1772 TRUE 1773------ 1774 t 1775(1 row) 1776 1777select md5(''::bytea) = 'd41d8cd98f00b204e9800998ecf8427e' AS "TRUE"; 1778 TRUE 1779------ 1780 t 1781(1 row) 1782 1783select md5('a'::bytea) = '0cc175b9c0f1b6a831c399e269772661' AS "TRUE"; 1784 TRUE 1785------ 1786 t 1787(1 row) 1788 1789select md5('abc'::bytea) = '900150983cd24fb0d6963f7d28e17f72' AS "TRUE"; 1790 TRUE 1791------ 1792 t 1793(1 row) 1794 1795select md5('message digest'::bytea) = 'f96b697d7cb7938d525a2f31aaf161d0' AS "TRUE"; 1796 TRUE 1797------ 1798 t 1799(1 row) 1800 1801select md5('abcdefghijklmnopqrstuvwxyz'::bytea) = 'c3fcd3d76192e4007dfb496cca67e13b' AS "TRUE"; 1802 TRUE 1803------ 1804 t 1805(1 row) 1806 1807select md5('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'::bytea) = 'd174ab98d277d9f5a5611c2c9f419d9f' AS "TRUE"; 1808 TRUE 1809------ 1810 t 1811(1 row) 1812 1813select md5('12345678901234567890123456789012345678901234567890123456789012345678901234567890'::bytea) = '57edf4a22be3c955ac49da2e2107b67a' AS "TRUE"; 1814 TRUE 1815------ 1816 t 1817(1 row) 1818 1819-- 1820-- SHA-2 1821-- 1822SET bytea_output TO hex; 1823SELECT sha224(''); 1824 sha224 1825------------------------------------------------------------ 1826 \xd14a028c2a3a2bc9476102bb288234c415a2b01f828ea62ac5b3e42f 1827(1 row) 1828 1829SELECT sha224('The quick brown fox jumps over the lazy dog.'); 1830 sha224 1831------------------------------------------------------------ 1832 \x619cba8e8e05826e9b8c519c0a5c68f4fb653e8a3d8aa04bb2c8cd4c 1833(1 row) 1834 1835SELECT sha256(''); 1836 sha256 1837-------------------------------------------------------------------- 1838 \xe3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855 1839(1 row) 1840 1841SELECT sha256('The quick brown fox jumps over the lazy dog.'); 1842 sha256 1843-------------------------------------------------------------------- 1844 \xef537f25c895bfa782526529a9b63d97aa631564d5d789c2b765448c8635fb6c 1845(1 row) 1846 1847SELECT sha384(''); 1848 sha384 1849---------------------------------------------------------------------------------------------------- 1850 \x38b060a751ac96384cd9327eb1b1e36a21fdb71114be07434c0cc7bf63f6e1da274edebfe76f65fbd51ad2f14898b95b 1851(1 row) 1852 1853SELECT sha384('The quick brown fox jumps over the lazy dog.'); 1854 sha384 1855---------------------------------------------------------------------------------------------------- 1856 \xed892481d8272ca6df370bf706e4d7bc1b5739fa2177aae6c50e946678718fc67a7af2819a021c2fc34e91bdb63409d7 1857(1 row) 1858 1859SELECT sha512(''); 1860 sha512 1861------------------------------------------------------------------------------------------------------------------------------------ 1862 \xcf83e1357eefb8bdf1542850d66d8007d620e4050b5715dc83f4a921d36ce9ce47d0d13c5d85f2b0ff8318d2877eec2f63b931bd47417a81a538327af927da3e 1863(1 row) 1864 1865SELECT sha512('The quick brown fox jumps over the lazy dog.'); 1866 sha512 1867------------------------------------------------------------------------------------------------------------------------------------ 1868 \x91ea1245f20d46ae9a037a989f54f1f790f0a47607eeb8a14d12890cea77a1bbc6c7ed9cf205e67b7f2b8fd4c7dfd3a7a8617e45f3c463d481c7e586c39ac1ed 1869(1 row) 1870 1871-- 1872-- encode/decode 1873-- 1874SELECT encode('\x1234567890abcdef00', 'hex'); 1875 encode 1876-------------------- 1877 1234567890abcdef00 1878(1 row) 1879 1880SELECT decode('1234567890abcdef00', 'hex'); 1881 decode 1882---------------------- 1883 \x1234567890abcdef00 1884(1 row) 1885 1886SELECT encode(('\x' || repeat('1234567890abcdef0001', 7))::bytea, 'base64'); 1887 encode 1888------------------------------------------------------------------------------ 1889 EjRWeJCrze8AARI0VniQq83vAAESNFZ4kKvN7wABEjRWeJCrze8AARI0VniQq83vAAESNFZ4kKvN+ 1890 7wABEjRWeJCrze8AAQ== 1891(1 row) 1892 1893SELECT decode(encode(('\x' || repeat('1234567890abcdef0001', 7))::bytea, 1894 'base64'), 'base64'); 1895 decode 1896------------------------------------------------------------------------------------------------------------------------------------------------ 1897 \x1234567890abcdef00011234567890abcdef00011234567890abcdef00011234567890abcdef00011234567890abcdef00011234567890abcdef00011234567890abcdef0001 1898(1 row) 1899 1900SELECT encode('\x1234567890abcdef00', 'escape'); 1901 encode 1902----------------------------- 1903 \x124Vx\220\253\315\357\000 1904(1 row) 1905 1906SELECT decode(encode('\x1234567890abcdef00', 'escape'), 'escape'); 1907 decode 1908---------------------- 1909 \x1234567890abcdef00 1910(1 row) 1911 1912-- 1913-- get_bit/set_bit etc 1914-- 1915SELECT get_bit('\x1234567890abcdef00'::bytea, 43); 1916 get_bit 1917--------- 1918 1 1919(1 row) 1920 1921SELECT get_bit('\x1234567890abcdef00'::bytea, 99); -- error 1922ERROR: index 99 out of valid range, 0..71 1923SELECT set_bit('\x1234567890abcdef00'::bytea, 43, 0); 1924 set_bit 1925---------------------- 1926 \x1234567890a3cdef00 1927(1 row) 1928 1929SELECT set_bit('\x1234567890abcdef00'::bytea, 99, 0); -- error 1930ERROR: index 99 out of valid range, 0..71 1931SELECT get_byte('\x1234567890abcdef00'::bytea, 3); 1932 get_byte 1933---------- 1934 120 1935(1 row) 1936 1937SELECT get_byte('\x1234567890abcdef00'::bytea, 99); -- error 1938ERROR: index 99 out of valid range, 0..8 1939SELECT set_byte('\x1234567890abcdef00'::bytea, 7, 11); 1940 set_byte 1941---------------------- 1942 \x1234567890abcd0b00 1943(1 row) 1944 1945SELECT set_byte('\x1234567890abcdef00'::bytea, 99, 11); -- error 1946ERROR: index 99 out of valid range, 0..8 1947-- 1948-- test behavior of escape_string_warning and standard_conforming_strings options 1949-- 1950set escape_string_warning = off; 1951set standard_conforming_strings = off; 1952show escape_string_warning; 1953 escape_string_warning 1954----------------------- 1955 off 1956(1 row) 1957 1958show standard_conforming_strings; 1959 standard_conforming_strings 1960----------------------------- 1961 off 1962(1 row) 1963 1964set escape_string_warning = on; 1965set standard_conforming_strings = on; 1966show escape_string_warning; 1967 escape_string_warning 1968----------------------- 1969 on 1970(1 row) 1971 1972show standard_conforming_strings; 1973 standard_conforming_strings 1974----------------------------- 1975 on 1976(1 row) 1977 1978select 'a\bcd' as f1, 'a\b''cd' as f2, 'a\b''''cd' as f3, 'abcd\' as f4, 'ab\''cd' as f5, '\\' as f6; 1979 f1 | f2 | f3 | f4 | f5 | f6 1980-------+--------+---------+-------+--------+---- 1981 a\bcd | a\b'cd | a\b''cd | abcd\ | ab\'cd | \\ 1982(1 row) 1983 1984set standard_conforming_strings = off; 1985select 'a\\bcd' as f1, 'a\\b\'cd' as f2, 'a\\b\'''cd' as f3, 'abcd\\' as f4, 'ab\\\'cd' as f5, '\\\\' as f6; 1986WARNING: nonstandard use of \\ in a string literal 1987LINE 1: select 'a\\bcd' as f1, 'a\\b\'cd' as f2, 'a\\b\'''cd' as f3,... 1988 ^ 1989HINT: Use the escape string syntax for backslashes, e.g., E'\\'. 1990WARNING: nonstandard use of \\ in a string literal 1991LINE 1: select 'a\\bcd' as f1, 'a\\b\'cd' as f2, 'a\\b\'''cd' as f3,... 1992 ^ 1993HINT: Use the escape string syntax for backslashes, e.g., E'\\'. 1994WARNING: nonstandard use of \\ in a string literal 1995LINE 1: select 'a\\bcd' as f1, 'a\\b\'cd' as f2, 'a\\b\'''cd' as f3,... 1996 ^ 1997HINT: Use the escape string syntax for backslashes, e.g., E'\\'. 1998WARNING: nonstandard use of \\ in a string literal 1999LINE 1: ...bcd' as f1, 'a\\b\'cd' as f2, 'a\\b\'''cd' as f3, 'abcd\\' ... 2000 ^ 2001HINT: Use the escape string syntax for backslashes, e.g., E'\\'. 2002WARNING: nonstandard use of \\ in a string literal 2003LINE 1: ...'cd' as f2, 'a\\b\'''cd' as f3, 'abcd\\' as f4, 'ab\\\'cd'... 2004 ^ 2005HINT: Use the escape string syntax for backslashes, e.g., E'\\'. 2006WARNING: nonstandard use of \\ in a string literal 2007LINE 1: ...'''cd' as f3, 'abcd\\' as f4, 'ab\\\'cd' as f5, '\\\\' as ... 2008 ^ 2009HINT: Use the escape string syntax for backslashes, e.g., E'\\'. 2010 f1 | f2 | f3 | f4 | f5 | f6 2011-------+--------+---------+-------+--------+---- 2012 a\bcd | a\b'cd | a\b''cd | abcd\ | ab\'cd | \\ 2013(1 row) 2014 2015set escape_string_warning = off; 2016set standard_conforming_strings = on; 2017select 'a\bcd' as f1, 'a\b''cd' as f2, 'a\b''''cd' as f3, 'abcd\' as f4, 'ab\''cd' as f5, '\\' as f6; 2018 f1 | f2 | f3 | f4 | f5 | f6 2019-------+--------+---------+-------+--------+---- 2020 a\bcd | a\b'cd | a\b''cd | abcd\ | ab\'cd | \\ 2021(1 row) 2022 2023set standard_conforming_strings = off; 2024select 'a\\bcd' as f1, 'a\\b\'cd' as f2, 'a\\b\'''cd' as f3, 'abcd\\' as f4, 'ab\\\'cd' as f5, '\\\\' as f6; 2025 f1 | f2 | f3 | f4 | f5 | f6 2026-------+--------+---------+-------+--------+---- 2027 a\bcd | a\b'cd | a\b''cd | abcd\ | ab\'cd | \\ 2028(1 row) 2029 2030reset standard_conforming_strings; 2031-- 2032-- Additional string functions 2033-- 2034SET bytea_output TO escape; 2035SELECT initcap('hi THOMAS'); 2036 initcap 2037----------- 2038 Hi Thomas 2039(1 row) 2040 2041SELECT lpad('hi', 5, 'xy'); 2042 lpad 2043------- 2044 xyxhi 2045(1 row) 2046 2047SELECT lpad('hi', 5); 2048 lpad 2049------- 2050 hi 2051(1 row) 2052 2053SELECT lpad('hi', -5, 'xy'); 2054 lpad 2055------ 2056 2057(1 row) 2058 2059SELECT lpad('hello', 2); 2060 lpad 2061------ 2062 he 2063(1 row) 2064 2065SELECT lpad('hi', 5, ''); 2066 lpad 2067------ 2068 hi 2069(1 row) 2070 2071SELECT rpad('hi', 5, 'xy'); 2072 rpad 2073------- 2074 hixyx 2075(1 row) 2076 2077SELECT rpad('hi', 5); 2078 rpad 2079------- 2080 hi 2081(1 row) 2082 2083SELECT rpad('hi', -5, 'xy'); 2084 rpad 2085------ 2086 2087(1 row) 2088 2089SELECT rpad('hello', 2); 2090 rpad 2091------ 2092 he 2093(1 row) 2094 2095SELECT rpad('hi', 5, ''); 2096 rpad 2097------ 2098 hi 2099(1 row) 2100 2101SELECT ltrim('zzzytrim', 'xyz'); 2102 ltrim 2103------- 2104 trim 2105(1 row) 2106 2107SELECT translate('', '14', 'ax'); 2108 translate 2109----------- 2110 2111(1 row) 2112 2113SELECT translate('12345', '14', 'ax'); 2114 translate 2115----------- 2116 a23x5 2117(1 row) 2118 2119SELECT ascii('x'); 2120 ascii 2121------- 2122 120 2123(1 row) 2124 2125SELECT ascii(''); 2126 ascii 2127------- 2128 0 2129(1 row) 2130 2131SELECT chr(65); 2132 chr 2133----- 2134 A 2135(1 row) 2136 2137SELECT chr(0); 2138ERROR: null character not permitted 2139SELECT repeat('Pg', 4); 2140 repeat 2141---------- 2142 PgPgPgPg 2143(1 row) 2144 2145SELECT repeat('Pg', -4); 2146 repeat 2147-------- 2148 2149(1 row) 2150 2151SELECT SUBSTRING('1234567890'::bytea FROM 3) "34567890"; 2152 34567890 2153---------- 2154 34567890 2155(1 row) 2156 2157SELECT SUBSTRING('1234567890'::bytea FROM 4 FOR 3) AS "456"; 2158 456 2159----- 2160 456 2161(1 row) 2162 2163SELECT SUBSTRING('string'::bytea FROM 2 FOR 2147483646) AS "tring"; 2164 tring 2165------- 2166 tring 2167(1 row) 2168 2169SELECT SUBSTRING('string'::bytea FROM -10 FOR 2147483646) AS "string"; 2170 string 2171-------- 2172 string 2173(1 row) 2174 2175SELECT SUBSTRING('string'::bytea FROM -10 FOR -2147483646) AS "error"; 2176ERROR: negative substring length not allowed 2177SELECT trim(E'\\000'::bytea from E'\\000Tom\\000'::bytea); 2178 btrim 2179------- 2180 Tom 2181(1 row) 2182 2183SELECT trim(leading E'\\000'::bytea from E'\\000Tom\\000'::bytea); 2184 ltrim 2185--------- 2186 Tom\000 2187(1 row) 2188 2189SELECT trim(trailing E'\\000'::bytea from E'\\000Tom\\000'::bytea); 2190 rtrim 2191--------- 2192 \000Tom 2193(1 row) 2194 2195SELECT btrim(E'\\000trim\\000'::bytea, E'\\000'::bytea); 2196 btrim 2197------- 2198 trim 2199(1 row) 2200 2201SELECT btrim(''::bytea, E'\\000'::bytea); 2202 btrim 2203------- 2204 2205(1 row) 2206 2207SELECT btrim(E'\\000trim\\000'::bytea, ''::bytea); 2208 btrim 2209-------------- 2210 \000trim\000 2211(1 row) 2212 2213SELECT encode(overlay(E'Th\\000omas'::bytea placing E'Th\\001omas'::bytea from 2),'escape'); 2214 encode 2215------------- 2216 TTh\x01omas 2217(1 row) 2218 2219SELECT encode(overlay(E'Th\\000omas'::bytea placing E'\\002\\003'::bytea from 8),'escape'); 2220 encode 2221-------------------- 2222 Th\000omas\x02\x03 2223(1 row) 2224 2225SELECT encode(overlay(E'Th\\000omas'::bytea placing E'\\002\\003'::bytea from 5 for 3),'escape'); 2226 encode 2227----------------- 2228 Th\000o\x02\x03 2229(1 row) 2230 2231SELECT bit_count('\x1234567890'::bytea); 2232 bit_count 2233----------- 2234 15 2235(1 row) 2236 2237SELECT unistr('\0064at\+0000610'); 2238 unistr 2239-------- 2240 data0 2241(1 row) 2242 2243SELECT unistr('d\u0061t\U000000610'); 2244 unistr 2245-------- 2246 data0 2247(1 row) 2248 2249SELECT unistr('a\\b'); 2250 unistr 2251-------- 2252 a\b 2253(1 row) 2254 2255-- errors: 2256SELECT unistr('wrong: \db99'); 2257ERROR: invalid Unicode surrogate pair 2258SELECT unistr('wrong: \db99\0061'); 2259ERROR: invalid Unicode surrogate pair 2260SELECT unistr('wrong: \+00db99\+000061'); 2261ERROR: invalid Unicode surrogate pair 2262SELECT unistr('wrong: \+2FFFFF'); 2263ERROR: invalid Unicode code point: 2FFFFF 2264SELECT unistr('wrong: \udb99\u0061'); 2265ERROR: invalid Unicode surrogate pair 2266SELECT unistr('wrong: \U0000db99\U00000061'); 2267ERROR: invalid Unicode surrogate pair 2268SELECT unistr('wrong: \U002FFFFF'); 2269ERROR: invalid Unicode code point: 2FFFFF 2270SELECT unistr('wrong: \xyz'); 2271ERROR: invalid Unicode escape 2272HINT: Unicode escapes must be \XXXX, \+XXXXXX, \uXXXX, or \UXXXXXXXX. 2273