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