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 '(b|c)' FOR NULL) IS NULL AS "True"; 347 True 348------ 349 t 350(1 row) 351 352SELECT SUBSTRING(NULL FROM '(b|c)' 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-- PostgreSQL extension to allow omitting the escape character; 365-- here the regexp is taken as Posix syntax 366SELECT SUBSTRING('abcdefg' FROM 'c.e') AS "cde"; 367 cde 368----- 369 cde 370(1 row) 371 372-- With a parenthesized subexpression, return only what matches the subexpr 373SELECT SUBSTRING('abcdefg' FROM 'b(.*)f') AS "cde"; 374 cde 375----- 376 cde 377(1 row) 378 379-- PostgreSQL extension to allow using back reference in replace string; 380SELECT regexp_replace('1112223333', E'(\\d{3})(\\d{3})(\\d{4})', E'(\\1) \\2-\\3'); 381 regexp_replace 382---------------- 383 (111) 222-3333 384(1 row) 385 386SELECT regexp_replace('AAA BBB CCC ', E'\\s+', ' ', 'g'); 387 regexp_replace 388---------------- 389 AAA BBB CCC 390(1 row) 391 392SELECT regexp_replace('AAA', '^|$', 'Z', 'g'); 393 regexp_replace 394---------------- 395 ZAAAZ 396(1 row) 397 398SELECT regexp_replace('AAA aaa', 'A+', 'Z', 'gi'); 399 regexp_replace 400---------------- 401 Z Z 402(1 row) 403 404-- invalid regexp option 405SELECT regexp_replace('AAA aaa', 'A+', 'Z', 'z'); 406ERROR: invalid regexp option: "z" 407-- set so we can tell NULL from empty string 408\pset null '\\N' 409-- return all matches from regexp 410SELECT regexp_matches('foobarbequebaz', $re$(bar)(beque)$re$); 411 regexp_matches 412---------------- 413 {bar,beque} 414(1 row) 415 416-- test case insensitive 417SELECT regexp_matches('foObARbEqUEbAz', $re$(bar)(beque)$re$, 'i'); 418 regexp_matches 419---------------- 420 {bAR,bEqUE} 421(1 row) 422 423-- global option - more than one match 424SELECT regexp_matches('foobarbequebazilbarfbonk', $re$(b[^b]+)(b[^b]+)$re$, 'g'); 425 regexp_matches 426---------------- 427 {bar,beque} 428 {bazil,barf} 429(2 rows) 430 431-- empty capture group (matched empty string) 432SELECT regexp_matches('foobarbequebaz', $re$(bar)(.*)(beque)$re$); 433 regexp_matches 434---------------- 435 {bar,"",beque} 436(1 row) 437 438-- no match 439SELECT regexp_matches('foobarbequebaz', $re$(bar)(.+)(beque)$re$); 440 regexp_matches 441---------------- 442(0 rows) 443 444-- optional capture group did not match, null entry in array 445SELECT regexp_matches('foobarbequebaz', $re$(bar)(.+)?(beque)$re$); 446 regexp_matches 447------------------ 448 {bar,NULL,beque} 449(1 row) 450 451-- no capture groups 452SELECT regexp_matches('foobarbequebaz', $re$barbeque$re$); 453 regexp_matches 454---------------- 455 {barbeque} 456(1 row) 457 458-- start/end-of-line matches are of zero length 459SELECT regexp_matches('foo' || chr(10) || 'bar' || chr(10) || 'bequq' || chr(10) || 'baz', '^', 'mg'); 460 regexp_matches 461---------------- 462 {""} 463 {""} 464 {""} 465 {""} 466(4 rows) 467 468SELECT regexp_matches('foo' || chr(10) || 'bar' || chr(10) || 'bequq' || chr(10) || 'baz', '$', 'mg'); 469 regexp_matches 470---------------- 471 {""} 472 {""} 473 {""} 474 {""} 475(4 rows) 476 477SELECT regexp_matches('1' || chr(10) || '2' || chr(10) || '3' || chr(10) || '4' || chr(10), '^.?', 'mg'); 478 regexp_matches 479---------------- 480 {1} 481 {2} 482 {3} 483 {4} 484 {""} 485(5 rows) 486 487SELECT regexp_matches(chr(10) || '1' || chr(10) || '2' || chr(10) || '3' || chr(10) || '4' || chr(10), '.?$', 'mg'); 488 regexp_matches 489---------------- 490 {""} 491 {1} 492 {""} 493 {2} 494 {""} 495 {3} 496 {""} 497 {4} 498 {""} 499 {""} 500(10 rows) 501 502SELECT regexp_matches(chr(10) || '1' || chr(10) || '2' || chr(10) || '3' || chr(10) || '4', '.?$', 'mg'); 503 regexp_matches 504---------------- 505 {""} 506 {1} 507 {""} 508 {2} 509 {""} 510 {3} 511 {""} 512 {4} 513 {""} 514(9 rows) 515 516-- give me errors 517SELECT regexp_matches('foobarbequebaz', $re$(bar)(beque)$re$, 'gz'); 518ERROR: invalid regexp option: "z" 519SELECT regexp_matches('foobarbequebaz', $re$(barbeque$re$); 520ERROR: invalid regular expression: parentheses () not balanced 521SELECT regexp_matches('foobarbequebaz', $re$(bar)(beque){2,1}$re$); 522ERROR: invalid regular expression: invalid repetition count(s) 523-- split string on regexp 524SELECT foo, length(foo) FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', $re$\s+$re$) AS foo; 525 foo | length 526-------+-------- 527 the | 3 528 quick | 5 529 brown | 5 530 fox | 3 531 jumps | 5 532 over | 4 533 the | 3 534 lazy | 4 535 dog | 3 536(9 rows) 537 538SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', $re$\s+$re$); 539 regexp_split_to_array 540----------------------------------------------- 541 {the,quick,brown,fox,jumps,over,the,lazy,dog} 542(1 row) 543 544SELECT foo, length(foo) FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', $re$\s*$re$) AS foo; 545 foo | length 546-----+-------- 547 t | 1 548 h | 1 549 e | 1 550 q | 1 551 u | 1 552 i | 1 553 c | 1 554 k | 1 555 b | 1 556 r | 1 557 o | 1 558 w | 1 559 n | 1 560 f | 1 561 o | 1 562 x | 1 563 j | 1 564 u | 1 565 m | 1 566 p | 1 567 s | 1 568 o | 1 569 v | 1 570 e | 1 571 r | 1 572 t | 1 573 h | 1 574 e | 1 575 l | 1 576 a | 1 577 z | 1 578 y | 1 579 d | 1 580 o | 1 581 g | 1 582(35 rows) 583 584SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', $re$\s*$re$); 585 regexp_split_to_array 586------------------------------------------------------------------------- 587 {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} 588(1 row) 589 590SELECT foo, length(foo) FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', '') AS foo; 591 foo | length 592-----+-------- 593 t | 1 594 h | 1 595 e | 1 596 | 1 597 q | 1 598 u | 1 599 i | 1 600 c | 1 601 k | 1 602 | 1 603 b | 1 604 r | 1 605 o | 1 606 w | 1 607 n | 1 608 | 1 609 f | 1 610 o | 1 611 x | 1 612 | 1 613 j | 1 614 u | 1 615 m | 1 616 p | 1 617 s | 1 618 | 1 619 o | 1 620 v | 1 621 e | 1 622 r | 1 623 | 1 624 t | 1 625 h | 1 626 e | 1 627 | 1 628 l | 1 629 a | 1 630 z | 1 631 y | 1 632 | 1 633 d | 1 634 o | 1 635 g | 1 636(43 rows) 637 638SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', ''); 639 regexp_split_to_array 640--------------------------------------------------------------------------------------------------------- 641 {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} 642(1 row) 643 644-- case insensitive 645SELECT foo, length(foo) FROM regexp_split_to_table('thE QUick bROWn FOx jUMPs ovEr The lazy dOG', 'e', 'i') AS foo; 646 foo | length 647---------------------------+-------- 648 th | 2 649 QUick bROWn FOx jUMPs ov | 25 650 r Th | 4 651 lazy dOG | 9 652(4 rows) 653 654SELECT regexp_split_to_array('thE QUick bROWn FOx jUMPs ovEr The lazy dOG', 'e', 'i'); 655 regexp_split_to_array 656----------------------------------------------------- 657 {th," QUick bROWn FOx jUMPs ov","r Th"," lazy dOG"} 658(1 row) 659 660-- no match of pattern 661SELECT foo, length(foo) FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', 'nomatch') AS foo; 662 foo | length 663---------------------------------------------+-------- 664 the quick brown fox jumps over the lazy dog | 43 665(1 row) 666 667SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', 'nomatch'); 668 regexp_split_to_array 669------------------------------------------------- 670 {"the quick brown fox jumps over the lazy dog"} 671(1 row) 672 673-- some corner cases 674SELECT regexp_split_to_array('123456','1'); 675 regexp_split_to_array 676----------------------- 677 {"",23456} 678(1 row) 679 680SELECT regexp_split_to_array('123456','6'); 681 regexp_split_to_array 682----------------------- 683 {12345,""} 684(1 row) 685 686SELECT regexp_split_to_array('123456','.'); 687 regexp_split_to_array 688------------------------ 689 {"","","","","","",""} 690(1 row) 691 692SELECT regexp_split_to_array('123456',''); 693 regexp_split_to_array 694----------------------- 695 {1,2,3,4,5,6} 696(1 row) 697 698SELECT regexp_split_to_array('123456','(?:)'); 699 regexp_split_to_array 700----------------------- 701 {1,2,3,4,5,6} 702(1 row) 703 704SELECT regexp_split_to_array('1',''); 705 regexp_split_to_array 706----------------------- 707 {1} 708(1 row) 709 710-- errors 711SELECT foo, length(foo) FROM regexp_split_to_table('thE QUick bROWn FOx jUMPs ovEr The lazy dOG', 'e', 'zippy') AS foo; 712ERROR: invalid regexp option: "z" 713SELECT regexp_split_to_array('thE QUick bROWn FOx jUMPs ovEr The lazy dOG', 'e', 'iz'); 714ERROR: invalid regexp option: "z" 715-- global option meaningless for regexp_split 716SELECT foo, length(foo) FROM regexp_split_to_table('thE QUick bROWn FOx jUMPs ovEr The lazy dOG', 'e', 'g') AS foo; 717ERROR: regexp_split_to_table does not support the global option 718SELECT regexp_split_to_array('thE QUick bROWn FOx jUMPs ovEr The lazy dOG', 'e', 'g'); 719ERROR: regexp_split_to_array does not support the global option 720-- change NULL-display back 721\pset null '' 722-- E021-11 position expression 723SELECT POSITION('4' IN '1234567890') = '4' AS "4"; 724 4 725--- 726 t 727(1 row) 728 729SELECT POSITION('5' IN '1234567890') = '5' AS "5"; 730 5 731--- 732 t 733(1 row) 734 735-- T312 character overlay function 736SELECT OVERLAY('abcdef' PLACING '45' FROM 4) AS "abc45f"; 737 abc45f 738-------- 739 abc45f 740(1 row) 741 742SELECT OVERLAY('yabadoo' PLACING 'daba' FROM 5) AS "yabadaba"; 743 yabadaba 744---------- 745 yabadaba 746(1 row) 747 748SELECT OVERLAY('yabadoo' PLACING 'daba' FROM 5 FOR 0) AS "yabadabadoo"; 749 yabadabadoo 750------------- 751 yabadabadoo 752(1 row) 753 754SELECT OVERLAY('babosa' PLACING 'ubb' FROM 2 FOR 4) AS "bubba"; 755 bubba 756------- 757 bubba 758(1 row) 759 760-- 761-- test LIKE 762-- Be sure to form every test as a LIKE/NOT LIKE pair. 763-- 764-- simplest examples 765-- E061-04 like predicate 766SELECT 'hawkeye' LIKE 'h%' AS "true"; 767 true 768------ 769 t 770(1 row) 771 772SELECT 'hawkeye' NOT LIKE 'h%' AS "false"; 773 false 774------- 775 f 776(1 row) 777 778SELECT 'hawkeye' LIKE 'H%' AS "false"; 779 false 780------- 781 f 782(1 row) 783 784SELECT 'hawkeye' NOT LIKE 'H%' AS "true"; 785 true 786------ 787 t 788(1 row) 789 790SELECT 'hawkeye' LIKE 'indio%' AS "false"; 791 false 792------- 793 f 794(1 row) 795 796SELECT 'hawkeye' NOT LIKE 'indio%' AS "true"; 797 true 798------ 799 t 800(1 row) 801 802SELECT 'hawkeye' LIKE 'h%eye' AS "true"; 803 true 804------ 805 t 806(1 row) 807 808SELECT 'hawkeye' NOT LIKE 'h%eye' AS "false"; 809 false 810------- 811 f 812(1 row) 813 814SELECT 'indio' LIKE '_ndio' AS "true"; 815 true 816------ 817 t 818(1 row) 819 820SELECT 'indio' NOT LIKE '_ndio' AS "false"; 821 false 822------- 823 f 824(1 row) 825 826SELECT 'indio' LIKE 'in__o' AS "true"; 827 true 828------ 829 t 830(1 row) 831 832SELECT 'indio' NOT LIKE 'in__o' AS "false"; 833 false 834------- 835 f 836(1 row) 837 838SELECT 'indio' LIKE 'in_o' AS "false"; 839 false 840------- 841 f 842(1 row) 843 844SELECT 'indio' NOT LIKE 'in_o' AS "true"; 845 true 846------ 847 t 848(1 row) 849 850-- unused escape character 851SELECT 'hawkeye' LIKE 'h%' ESCAPE '#' AS "true"; 852 true 853------ 854 t 855(1 row) 856 857SELECT 'hawkeye' NOT LIKE 'h%' ESCAPE '#' AS "false"; 858 false 859------- 860 f 861(1 row) 862 863SELECT 'indio' LIKE 'ind_o' ESCAPE '$' AS "true"; 864 true 865------ 866 t 867(1 row) 868 869SELECT 'indio' NOT LIKE 'ind_o' ESCAPE '$' AS "false"; 870 false 871------- 872 f 873(1 row) 874 875-- escape character 876-- E061-05 like predicate with escape clause 877SELECT 'h%' LIKE 'h#%' ESCAPE '#' AS "true"; 878 true 879------ 880 t 881(1 row) 882 883SELECT 'h%' NOT LIKE 'h#%' ESCAPE '#' AS "false"; 884 false 885------- 886 f 887(1 row) 888 889SELECT 'h%wkeye' LIKE 'h#%' ESCAPE '#' AS "false"; 890 false 891------- 892 f 893(1 row) 894 895SELECT 'h%wkeye' NOT LIKE 'h#%' ESCAPE '#' AS "true"; 896 true 897------ 898 t 899(1 row) 900 901SELECT 'h%wkeye' LIKE 'h#%%' ESCAPE '#' AS "true"; 902 true 903------ 904 t 905(1 row) 906 907SELECT 'h%wkeye' NOT LIKE 'h#%%' ESCAPE '#' AS "false"; 908 false 909------- 910 f 911(1 row) 912 913SELECT 'h%awkeye' LIKE 'h#%a%k%e' ESCAPE '#' AS "true"; 914 true 915------ 916 t 917(1 row) 918 919SELECT 'h%awkeye' NOT LIKE 'h#%a%k%e' ESCAPE '#' AS "false"; 920 false 921------- 922 f 923(1 row) 924 925SELECT 'indio' LIKE '_ndio' ESCAPE '$' AS "true"; 926 true 927------ 928 t 929(1 row) 930 931SELECT 'indio' NOT LIKE '_ndio' ESCAPE '$' AS "false"; 932 false 933------- 934 f 935(1 row) 936 937SELECT 'i_dio' LIKE 'i$_d_o' ESCAPE '$' AS "true"; 938 true 939------ 940 t 941(1 row) 942 943SELECT 'i_dio' NOT LIKE 'i$_d_o' ESCAPE '$' AS "false"; 944 false 945------- 946 f 947(1 row) 948 949SELECT 'i_dio' LIKE 'i$_nd_o' ESCAPE '$' AS "false"; 950 false 951------- 952 f 953(1 row) 954 955SELECT 'i_dio' NOT LIKE 'i$_nd_o' ESCAPE '$' AS "true"; 956 true 957------ 958 t 959(1 row) 960 961SELECT 'i_dio' LIKE 'i$_d%o' ESCAPE '$' AS "true"; 962 true 963------ 964 t 965(1 row) 966 967SELECT 'i_dio' NOT LIKE 'i$_d%o' ESCAPE '$' AS "false"; 968 false 969------- 970 f 971(1 row) 972 973-- escape character same as pattern character 974SELECT 'maca' LIKE 'm%aca' ESCAPE '%' AS "true"; 975 true 976------ 977 t 978(1 row) 979 980SELECT 'maca' NOT LIKE 'm%aca' ESCAPE '%' AS "false"; 981 false 982------- 983 f 984(1 row) 985 986SELECT 'ma%a' LIKE 'm%a%%a' ESCAPE '%' AS "true"; 987 true 988------ 989 t 990(1 row) 991 992SELECT 'ma%a' NOT LIKE 'm%a%%a' ESCAPE '%' AS "false"; 993 false 994------- 995 f 996(1 row) 997 998SELECT 'bear' LIKE 'b_ear' ESCAPE '_' AS "true"; 999 true 1000------ 1001 t 1002(1 row) 1003 1004SELECT 'bear' NOT LIKE 'b_ear' ESCAPE '_' AS "false"; 1005 false 1006------- 1007 f 1008(1 row) 1009 1010SELECT 'be_r' LIKE 'b_e__r' ESCAPE '_' AS "true"; 1011 true 1012------ 1013 t 1014(1 row) 1015 1016SELECT 'be_r' NOT LIKE 'b_e__r' ESCAPE '_' AS "false"; 1017 false 1018------- 1019 f 1020(1 row) 1021 1022SELECT 'be_r' LIKE '__e__r' ESCAPE '_' AS "false"; 1023 false 1024------- 1025 f 1026(1 row) 1027 1028SELECT 'be_r' NOT LIKE '__e__r' ESCAPE '_' AS "true"; 1029 true 1030------ 1031 t 1032(1 row) 1033 1034-- 1035-- test ILIKE (case-insensitive LIKE) 1036-- Be sure to form every test as an ILIKE/NOT ILIKE pair. 1037-- 1038SELECT 'hawkeye' ILIKE 'h%' AS "true"; 1039 true 1040------ 1041 t 1042(1 row) 1043 1044SELECT 'hawkeye' NOT ILIKE 'h%' AS "false"; 1045 false 1046------- 1047 f 1048(1 row) 1049 1050SELECT 'hawkeye' ILIKE 'H%' AS "true"; 1051 true 1052------ 1053 t 1054(1 row) 1055 1056SELECT 'hawkeye' NOT ILIKE 'H%' AS "false"; 1057 false 1058------- 1059 f 1060(1 row) 1061 1062SELECT 'hawkeye' ILIKE 'H%Eye' AS "true"; 1063 true 1064------ 1065 t 1066(1 row) 1067 1068SELECT 'hawkeye' NOT ILIKE 'H%Eye' AS "false"; 1069 false 1070------- 1071 f 1072(1 row) 1073 1074SELECT 'Hawkeye' ILIKE 'h%' AS "true"; 1075 true 1076------ 1077 t 1078(1 row) 1079 1080SELECT 'Hawkeye' NOT ILIKE 'h%' AS "false"; 1081 false 1082------- 1083 f 1084(1 row) 1085 1086-- 1087-- test %/_ combination cases, cf bugs #4821 and #5478 1088-- 1089SELECT 'foo' LIKE '_%' as t, 'f' LIKE '_%' as t, '' LIKE '_%' as f; 1090 t | t | f 1091---+---+--- 1092 t | t | f 1093(1 row) 1094 1095SELECT 'foo' LIKE '%_' as t, 'f' LIKE '%_' as t, '' LIKE '%_' as f; 1096 t | t | f 1097---+---+--- 1098 t | t | f 1099(1 row) 1100 1101SELECT 'foo' LIKE '__%' as t, 'foo' LIKE '___%' as t, 'foo' LIKE '____%' as f; 1102 t | t | f 1103---+---+--- 1104 t | t | f 1105(1 row) 1106 1107SELECT 'foo' LIKE '%__' as t, 'foo' LIKE '%___' as t, 'foo' LIKE '%____' as f; 1108 t | t | f 1109---+---+--- 1110 t | t | f 1111(1 row) 1112 1113SELECT 'jack' LIKE '%____%' AS t; 1114 t 1115--- 1116 t 1117(1 row) 1118 1119-- 1120-- test implicit type conversion 1121-- 1122-- E021-07 character concatenation 1123SELECT 'unknown' || ' and unknown' AS "Concat unknown types"; 1124 Concat unknown types 1125---------------------- 1126 unknown and unknown 1127(1 row) 1128 1129SELECT text 'text' || ' and unknown' AS "Concat text to unknown type"; 1130 Concat text to unknown type 1131----------------------------- 1132 text and unknown 1133(1 row) 1134 1135SELECT char(20) 'characters' || ' and text' AS "Concat char to unknown type"; 1136 Concat char to unknown type 1137----------------------------- 1138 characters and text 1139(1 row) 1140 1141SELECT text 'text' || char(20) ' and characters' AS "Concat text to char"; 1142 Concat text to char 1143--------------------- 1144 text and characters 1145(1 row) 1146 1147SELECT text 'text' || varchar ' and varchar' AS "Concat text to varchar"; 1148 Concat text to varchar 1149------------------------ 1150 text and varchar 1151(1 row) 1152 1153-- 1154-- test substr with toasted text values 1155-- 1156CREATE TABLE toasttest(f1 text); 1157insert into toasttest values(repeat('1234567890',10000)); 1158insert into toasttest values(repeat('1234567890',10000)); 1159-- 1160-- Ensure that some values are uncompressed, to test the faster substring 1161-- operation used in that case 1162-- 1163alter table toasttest alter column f1 set storage external; 1164insert into toasttest values(repeat('1234567890',10000)); 1165insert into toasttest values(repeat('1234567890',10000)); 1166-- If the starting position is zero or less, then return from the start of the string 1167-- adjusting the length to be consistent with the "negative start" per SQL. 1168SELECT substr(f1, -1, 5) from toasttest; 1169 substr 1170-------- 1171 123 1172 123 1173 123 1174 123 1175(4 rows) 1176 1177-- If the length is less than zero, an ERROR is thrown. 1178SELECT substr(f1, 5, -1) from toasttest; 1179ERROR: negative substring length not allowed 1180-- If no third argument (length) is provided, the length to the end of the 1181-- string is assumed. 1182SELECT substr(f1, 99995) from toasttest; 1183 substr 1184-------- 1185 567890 1186 567890 1187 567890 1188 567890 1189(4 rows) 1190 1191-- If start plus length is > string length, the result is truncated to 1192-- string length 1193SELECT substr(f1, 99995, 10) from toasttest; 1194 substr 1195-------- 1196 567890 1197 567890 1198 567890 1199 567890 1200(4 rows) 1201 1202TRUNCATE TABLE toasttest; 1203INSERT INTO toasttest values (repeat('1234567890',300)); 1204INSERT INTO toasttest values (repeat('1234567890',300)); 1205INSERT INTO toasttest values (repeat('1234567890',300)); 1206INSERT INTO toasttest values (repeat('1234567890',300)); 1207-- expect >0 blocks 1208select 0 = pg_relation_size('pg_toast.pg_toast_'||(select oid from pg_class where relname = 'toasttest'))/current_setting('block_size')::integer as blocks; 1209 blocks 1210-------- 1211 f 1212(1 row) 1213 1214TRUNCATE TABLE toasttest; 1215ALTER TABLE toasttest set (toast_tuple_target = 4080); 1216INSERT INTO toasttest values (repeat('1234567890',300)); 1217INSERT INTO toasttest values (repeat('1234567890',300)); 1218INSERT INTO toasttest values (repeat('1234567890',300)); 1219INSERT INTO toasttest values (repeat('1234567890',300)); 1220-- expect 0 blocks 1221select 0 = pg_relation_size('pg_toast.pg_toast_'||(select oid from pg_class where relname = 'toasttest'))/current_setting('block_size')::integer as blocks; 1222 blocks 1223-------- 1224 t 1225(1 row) 1226 1227DROP TABLE toasttest; 1228-- 1229-- test substr with toasted bytea values 1230-- 1231CREATE TABLE toasttest(f1 bytea); 1232insert into toasttest values(decode(repeat('1234567890',10000),'escape')); 1233insert into toasttest values(decode(repeat('1234567890',10000),'escape')); 1234-- 1235-- Ensure that some values are uncompressed, to test the faster substring 1236-- operation used in that case 1237-- 1238alter table toasttest alter column f1 set storage external; 1239insert into toasttest values(decode(repeat('1234567890',10000),'escape')); 1240insert into toasttest values(decode(repeat('1234567890',10000),'escape')); 1241-- If the starting position is zero or less, then return from the start of the string 1242-- adjusting the length to be consistent with the "negative start" per SQL. 1243SELECT substr(f1, -1, 5) from toasttest; 1244 substr 1245-------- 1246 123 1247 123 1248 123 1249 123 1250(4 rows) 1251 1252-- If the length is less than zero, an ERROR is thrown. 1253SELECT substr(f1, 5, -1) from toasttest; 1254ERROR: negative substring length not allowed 1255-- If no third argument (length) is provided, the length to the end of the 1256-- string is assumed. 1257SELECT substr(f1, 99995) from toasttest; 1258 substr 1259-------- 1260 567890 1261 567890 1262 567890 1263 567890 1264(4 rows) 1265 1266-- If start plus length is > string length, the result is truncated to 1267-- string length 1268SELECT substr(f1, 99995, 10) from toasttest; 1269 substr 1270-------- 1271 567890 1272 567890 1273 567890 1274 567890 1275(4 rows) 1276 1277DROP TABLE toasttest; 1278-- test internally compressing datums 1279-- this tests compressing a datum to a very small size which exercises a 1280-- corner case in packed-varlena handling: even though small, the compressed 1281-- datum must be given a 4-byte header because there are no bits to indicate 1282-- compression in a 1-byte header 1283CREATE TABLE toasttest (c char(4096)); 1284INSERT INTO toasttest VALUES('x'); 1285SELECT length(c), c::text FROM toasttest; 1286 length | c 1287--------+--- 1288 1 | x 1289(1 row) 1290 1291SELECT c FROM toasttest; 1292 c 1293------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 1294 x 1295(1 row) 1296 1297DROP TABLE toasttest; 1298-- 1299-- test length 1300-- 1301SELECT length('abcdef') AS "length_6"; 1302 length_6 1303---------- 1304 6 1305(1 row) 1306 1307-- 1308-- test strpos 1309-- 1310SELECT strpos('abcdef', 'cd') AS "pos_3"; 1311 pos_3 1312------- 1313 3 1314(1 row) 1315 1316SELECT strpos('abcdef', 'xy') AS "pos_0"; 1317 pos_0 1318------- 1319 0 1320(1 row) 1321 1322-- 1323-- test replace 1324-- 1325SELECT replace('abcdef', 'de', '45') AS "abc45f"; 1326 abc45f 1327-------- 1328 abc45f 1329(1 row) 1330 1331SELECT replace('yabadabadoo', 'ba', '123') AS "ya123da123doo"; 1332 ya123da123doo 1333--------------- 1334 ya123da123doo 1335(1 row) 1336 1337SELECT replace('yabadoo', 'bad', '') AS "yaoo"; 1338 yaoo 1339------ 1340 yaoo 1341(1 row) 1342 1343-- 1344-- test split_part 1345-- 1346select split_part('joeuser@mydatabase','@',0) AS "an error"; 1347ERROR: field position must be greater than zero 1348select split_part('joeuser@mydatabase','@',1) AS "joeuser"; 1349 joeuser 1350--------- 1351 joeuser 1352(1 row) 1353 1354select split_part('joeuser@mydatabase','@',2) AS "mydatabase"; 1355 mydatabase 1356------------ 1357 mydatabase 1358(1 row) 1359 1360select split_part('joeuser@mydatabase','@',3) AS "empty string"; 1361 empty string 1362-------------- 1363 1364(1 row) 1365 1366select split_part('@joeuser@mydatabase@','@',2) AS "joeuser"; 1367 joeuser 1368--------- 1369 joeuser 1370(1 row) 1371 1372-- 1373-- test to_hex 1374-- 1375select to_hex(256*256*256 - 1) AS "ffffff"; 1376 ffffff 1377-------- 1378 ffffff 1379(1 row) 1380 1381select to_hex(256::bigint*256::bigint*256::bigint*256::bigint - 1) AS "ffffffff"; 1382 ffffffff 1383---------- 1384 ffffffff 1385(1 row) 1386 1387-- 1388-- MD5 test suite - from IETF RFC 1321 1389-- (see: ftp://ftp.rfc-editor.org/in-notes/rfc1321.txt) 1390-- 1391select md5('') = 'd41d8cd98f00b204e9800998ecf8427e' AS "TRUE"; 1392 TRUE 1393------ 1394 t 1395(1 row) 1396 1397select md5('a') = '0cc175b9c0f1b6a831c399e269772661' AS "TRUE"; 1398 TRUE 1399------ 1400 t 1401(1 row) 1402 1403select md5('abc') = '900150983cd24fb0d6963f7d28e17f72' AS "TRUE"; 1404 TRUE 1405------ 1406 t 1407(1 row) 1408 1409select md5('message digest') = 'f96b697d7cb7938d525a2f31aaf161d0' AS "TRUE"; 1410 TRUE 1411------ 1412 t 1413(1 row) 1414 1415select md5('abcdefghijklmnopqrstuvwxyz') = 'c3fcd3d76192e4007dfb496cca67e13b' AS "TRUE"; 1416 TRUE 1417------ 1418 t 1419(1 row) 1420 1421select md5('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789') = 'd174ab98d277d9f5a5611c2c9f419d9f' AS "TRUE"; 1422 TRUE 1423------ 1424 t 1425(1 row) 1426 1427select md5('12345678901234567890123456789012345678901234567890123456789012345678901234567890') = '57edf4a22be3c955ac49da2e2107b67a' AS "TRUE"; 1428 TRUE 1429------ 1430 t 1431(1 row) 1432 1433select md5(''::bytea) = 'd41d8cd98f00b204e9800998ecf8427e' AS "TRUE"; 1434 TRUE 1435------ 1436 t 1437(1 row) 1438 1439select md5('a'::bytea) = '0cc175b9c0f1b6a831c399e269772661' AS "TRUE"; 1440 TRUE 1441------ 1442 t 1443(1 row) 1444 1445select md5('abc'::bytea) = '900150983cd24fb0d6963f7d28e17f72' AS "TRUE"; 1446 TRUE 1447------ 1448 t 1449(1 row) 1450 1451select md5('message digest'::bytea) = 'f96b697d7cb7938d525a2f31aaf161d0' AS "TRUE"; 1452 TRUE 1453------ 1454 t 1455(1 row) 1456 1457select md5('abcdefghijklmnopqrstuvwxyz'::bytea) = 'c3fcd3d76192e4007dfb496cca67e13b' AS "TRUE"; 1458 TRUE 1459------ 1460 t 1461(1 row) 1462 1463select md5('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'::bytea) = 'd174ab98d277d9f5a5611c2c9f419d9f' AS "TRUE"; 1464 TRUE 1465------ 1466 t 1467(1 row) 1468 1469select md5('12345678901234567890123456789012345678901234567890123456789012345678901234567890'::bytea) = '57edf4a22be3c955ac49da2e2107b67a' AS "TRUE"; 1470 TRUE 1471------ 1472 t 1473(1 row) 1474 1475-- 1476-- SHA-2 1477-- 1478SET bytea_output TO hex; 1479SELECT sha224(''); 1480 sha224 1481------------------------------------------------------------ 1482 \xd14a028c2a3a2bc9476102bb288234c415a2b01f828ea62ac5b3e42f 1483(1 row) 1484 1485SELECT sha224('The quick brown fox jumps over the lazy dog.'); 1486 sha224 1487------------------------------------------------------------ 1488 \x619cba8e8e05826e9b8c519c0a5c68f4fb653e8a3d8aa04bb2c8cd4c 1489(1 row) 1490 1491SELECT sha256(''); 1492 sha256 1493-------------------------------------------------------------------- 1494 \xe3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855 1495(1 row) 1496 1497SELECT sha256('The quick brown fox jumps over the lazy dog.'); 1498 sha256 1499-------------------------------------------------------------------- 1500 \xef537f25c895bfa782526529a9b63d97aa631564d5d789c2b765448c8635fb6c 1501(1 row) 1502 1503SELECT sha384(''); 1504 sha384 1505---------------------------------------------------------------------------------------------------- 1506 \x38b060a751ac96384cd9327eb1b1e36a21fdb71114be07434c0cc7bf63f6e1da274edebfe76f65fbd51ad2f14898b95b 1507(1 row) 1508 1509SELECT sha384('The quick brown fox jumps over the lazy dog.'); 1510 sha384 1511---------------------------------------------------------------------------------------------------- 1512 \xed892481d8272ca6df370bf706e4d7bc1b5739fa2177aae6c50e946678718fc67a7af2819a021c2fc34e91bdb63409d7 1513(1 row) 1514 1515SELECT sha512(''); 1516 sha512 1517------------------------------------------------------------------------------------------------------------------------------------ 1518 \xcf83e1357eefb8bdf1542850d66d8007d620e4050b5715dc83f4a921d36ce9ce47d0d13c5d85f2b0ff8318d2877eec2f63b931bd47417a81a538327af927da3e 1519(1 row) 1520 1521SELECT sha512('The quick brown fox jumps over the lazy dog.'); 1522 sha512 1523------------------------------------------------------------------------------------------------------------------------------------ 1524 \x91ea1245f20d46ae9a037a989f54f1f790f0a47607eeb8a14d12890cea77a1bbc6c7ed9cf205e67b7f2b8fd4c7dfd3a7a8617e45f3c463d481c7e586c39ac1ed 1525(1 row) 1526 1527-- 1528-- encode/decode 1529-- 1530SELECT encode('\x1234567890abcdef00', 'hex'); 1531 encode 1532-------------------- 1533 1234567890abcdef00 1534(1 row) 1535 1536SELECT decode('1234567890abcdef00', 'hex'); 1537 decode 1538---------------------- 1539 \x1234567890abcdef00 1540(1 row) 1541 1542SELECT encode(('\x' || repeat('1234567890abcdef0001', 7))::bytea, 'base64'); 1543 encode 1544------------------------------------------------------------------------------ 1545 EjRWeJCrze8AARI0VniQq83vAAESNFZ4kKvN7wABEjRWeJCrze8AARI0VniQq83vAAESNFZ4kKvN+ 1546 7wABEjRWeJCrze8AAQ== 1547(1 row) 1548 1549SELECT decode(encode(('\x' || repeat('1234567890abcdef0001', 7))::bytea, 1550 'base64'), 'base64'); 1551 decode 1552------------------------------------------------------------------------------------------------------------------------------------------------ 1553 \x1234567890abcdef00011234567890abcdef00011234567890abcdef00011234567890abcdef00011234567890abcdef00011234567890abcdef00011234567890abcdef0001 1554(1 row) 1555 1556SELECT encode('\x1234567890abcdef00', 'escape'); 1557 encode 1558----------------------------- 1559 \x124Vx\220\253\315\357\000 1560(1 row) 1561 1562SELECT decode(encode('\x1234567890abcdef00', 'escape'), 'escape'); 1563 decode 1564---------------------- 1565 \x1234567890abcdef00 1566(1 row) 1567 1568-- 1569-- get_bit/set_bit etc 1570-- 1571SELECT get_bit('\x1234567890abcdef00'::bytea, 43); 1572 get_bit 1573--------- 1574 1 1575(1 row) 1576 1577SELECT get_bit('\x1234567890abcdef00'::bytea, 99); -- error 1578ERROR: index 99 out of valid range, 0..71 1579SELECT set_bit('\x1234567890abcdef00'::bytea, 43, 0); 1580 set_bit 1581---------------------- 1582 \x1234567890a3cdef00 1583(1 row) 1584 1585SELECT set_bit('\x1234567890abcdef00'::bytea, 99, 0); -- error 1586ERROR: index 99 out of valid range, 0..71 1587SELECT get_byte('\x1234567890abcdef00'::bytea, 3); 1588 get_byte 1589---------- 1590 120 1591(1 row) 1592 1593SELECT get_byte('\x1234567890abcdef00'::bytea, 99); -- error 1594ERROR: index 99 out of valid range, 0..8 1595SELECT set_byte('\x1234567890abcdef00'::bytea, 7, 11); 1596 set_byte 1597---------------------- 1598 \x1234567890abcd0b00 1599(1 row) 1600 1601SELECT set_byte('\x1234567890abcdef00'::bytea, 99, 11); -- error 1602ERROR: index 99 out of valid range, 0..8 1603-- 1604-- test behavior of escape_string_warning and standard_conforming_strings options 1605-- 1606set escape_string_warning = off; 1607set standard_conforming_strings = off; 1608show escape_string_warning; 1609 escape_string_warning 1610----------------------- 1611 off 1612(1 row) 1613 1614show standard_conforming_strings; 1615 standard_conforming_strings 1616----------------------------- 1617 off 1618(1 row) 1619 1620set escape_string_warning = on; 1621set standard_conforming_strings = on; 1622show escape_string_warning; 1623 escape_string_warning 1624----------------------- 1625 on 1626(1 row) 1627 1628show standard_conforming_strings; 1629 standard_conforming_strings 1630----------------------------- 1631 on 1632(1 row) 1633 1634select 'a\bcd' as f1, 'a\b''cd' as f2, 'a\b''''cd' as f3, 'abcd\' as f4, 'ab\''cd' as f5, '\\' as f6; 1635 f1 | f2 | f3 | f4 | f5 | f6 1636-------+--------+---------+-------+--------+---- 1637 a\bcd | a\b'cd | a\b''cd | abcd\ | ab\'cd | \\ 1638(1 row) 1639 1640set standard_conforming_strings = off; 1641select 'a\\bcd' as f1, 'a\\b\'cd' as f2, 'a\\b\'''cd' as f3, 'abcd\\' as f4, 'ab\\\'cd' as f5, '\\\\' as f6; 1642WARNING: nonstandard use of \\ in a string literal 1643LINE 1: select 'a\\bcd' as f1, 'a\\b\'cd' as f2, 'a\\b\'''cd' as f3,... 1644 ^ 1645HINT: Use the escape string syntax for backslashes, e.g., E'\\'. 1646WARNING: nonstandard use of \\ in a string literal 1647LINE 1: select 'a\\bcd' as f1, 'a\\b\'cd' as f2, 'a\\b\'''cd' as f3,... 1648 ^ 1649HINT: Use the escape string syntax for backslashes, e.g., E'\\'. 1650WARNING: nonstandard use of \\ in a string literal 1651LINE 1: select 'a\\bcd' as f1, 'a\\b\'cd' as f2, 'a\\b\'''cd' as f3,... 1652 ^ 1653HINT: Use the escape string syntax for backslashes, e.g., E'\\'. 1654WARNING: nonstandard use of \\ in a string literal 1655LINE 1: ...bcd' as f1, 'a\\b\'cd' as f2, 'a\\b\'''cd' as f3, 'abcd\\' ... 1656 ^ 1657HINT: Use the escape string syntax for backslashes, e.g., E'\\'. 1658WARNING: nonstandard use of \\ in a string literal 1659LINE 1: ...'cd' as f2, 'a\\b\'''cd' as f3, 'abcd\\' as f4, 'ab\\\'cd'... 1660 ^ 1661HINT: Use the escape string syntax for backslashes, e.g., E'\\'. 1662WARNING: nonstandard use of \\ in a string literal 1663LINE 1: ...'''cd' as f3, 'abcd\\' as f4, 'ab\\\'cd' as f5, '\\\\' as ... 1664 ^ 1665HINT: Use the escape string syntax for backslashes, e.g., E'\\'. 1666 f1 | f2 | f3 | f4 | f5 | f6 1667-------+--------+---------+-------+--------+---- 1668 a\bcd | a\b'cd | a\b''cd | abcd\ | ab\'cd | \\ 1669(1 row) 1670 1671set escape_string_warning = off; 1672set standard_conforming_strings = on; 1673select 'a\bcd' as f1, 'a\b''cd' as f2, 'a\b''''cd' as f3, 'abcd\' as f4, 'ab\''cd' as f5, '\\' as f6; 1674 f1 | f2 | f3 | f4 | f5 | f6 1675-------+--------+---------+-------+--------+---- 1676 a\bcd | a\b'cd | a\b''cd | abcd\ | ab\'cd | \\ 1677(1 row) 1678 1679set standard_conforming_strings = off; 1680select 'a\\bcd' as f1, 'a\\b\'cd' as f2, 'a\\b\'''cd' as f3, 'abcd\\' as f4, 'ab\\\'cd' as f5, '\\\\' as f6; 1681 f1 | f2 | f3 | f4 | f5 | f6 1682-------+--------+---------+-------+--------+---- 1683 a\bcd | a\b'cd | a\b''cd | abcd\ | ab\'cd | \\ 1684(1 row) 1685 1686-- 1687-- Additional string functions 1688-- 1689SET bytea_output TO escape; 1690SELECT initcap('hi THOMAS'); 1691 initcap 1692----------- 1693 Hi Thomas 1694(1 row) 1695 1696SELECT lpad('hi', 5, 'xy'); 1697 lpad 1698------- 1699 xyxhi 1700(1 row) 1701 1702SELECT lpad('hi', 5); 1703 lpad 1704------- 1705 hi 1706(1 row) 1707 1708SELECT lpad('hi', -5, 'xy'); 1709 lpad 1710------ 1711 1712(1 row) 1713 1714SELECT lpad('hello', 2); 1715 lpad 1716------ 1717 he 1718(1 row) 1719 1720SELECT lpad('hi', 5, ''); 1721 lpad 1722------ 1723 hi 1724(1 row) 1725 1726SELECT rpad('hi', 5, 'xy'); 1727 rpad 1728------- 1729 hixyx 1730(1 row) 1731 1732SELECT rpad('hi', 5); 1733 rpad 1734------- 1735 hi 1736(1 row) 1737 1738SELECT rpad('hi', -5, 'xy'); 1739 rpad 1740------ 1741 1742(1 row) 1743 1744SELECT rpad('hello', 2); 1745 rpad 1746------ 1747 he 1748(1 row) 1749 1750SELECT rpad('hi', 5, ''); 1751 rpad 1752------ 1753 hi 1754(1 row) 1755 1756SELECT ltrim('zzzytrim', 'xyz'); 1757 ltrim 1758------- 1759 trim 1760(1 row) 1761 1762SELECT translate('', '14', 'ax'); 1763 translate 1764----------- 1765 1766(1 row) 1767 1768SELECT translate('12345', '14', 'ax'); 1769 translate 1770----------- 1771 a23x5 1772(1 row) 1773 1774SELECT ascii('x'); 1775 ascii 1776------- 1777 120 1778(1 row) 1779 1780SELECT ascii(''); 1781 ascii 1782------- 1783 0 1784(1 row) 1785 1786SELECT chr(65); 1787 chr 1788----- 1789 A 1790(1 row) 1791 1792SELECT chr(0); 1793ERROR: null character not permitted 1794SELECT repeat('Pg', 4); 1795 repeat 1796---------- 1797 PgPgPgPg 1798(1 row) 1799 1800SELECT repeat('Pg', -4); 1801 repeat 1802-------- 1803 1804(1 row) 1805 1806SELECT SUBSTRING('1234567890'::bytea FROM 3) "34567890"; 1807 34567890 1808---------- 1809 34567890 1810(1 row) 1811 1812SELECT SUBSTRING('1234567890'::bytea FROM 4 FOR 3) AS "456"; 1813 456 1814----- 1815 456 1816(1 row) 1817 1818SELECT SUBSTRING('string'::bytea FROM 2 FOR 2147483646) AS "tring"; 1819 tring 1820------- 1821 tring 1822(1 row) 1823 1824SELECT SUBSTRING('string'::bytea FROM -10 FOR 2147483646) AS "string"; 1825 string 1826-------- 1827 string 1828(1 row) 1829 1830SELECT SUBSTRING('string'::bytea FROM -10 FOR -2147483646) AS "error"; 1831ERROR: negative substring length not allowed 1832SELECT trim(E'\\000'::bytea from E'\\000Tom\\000'::bytea); 1833 btrim 1834------- 1835 Tom 1836(1 row) 1837 1838SELECT btrim(E'\\000trim\\000'::bytea, E'\\000'::bytea); 1839 btrim 1840------- 1841 trim 1842(1 row) 1843 1844SELECT btrim(''::bytea, E'\\000'::bytea); 1845 btrim 1846------- 1847 1848(1 row) 1849 1850SELECT btrim(E'\\000trim\\000'::bytea, ''::bytea); 1851 btrim 1852-------------- 1853 \000trim\000 1854(1 row) 1855 1856SELECT encode(overlay(E'Th\\000omas'::bytea placing E'Th\\001omas'::bytea from 2),'escape'); 1857 encode 1858------------- 1859 TTh\x01omas 1860(1 row) 1861 1862SELECT encode(overlay(E'Th\\000omas'::bytea placing E'\\002\\003'::bytea from 8),'escape'); 1863 encode 1864-------------------- 1865 Th\000omas\x02\x03 1866(1 row) 1867 1868SELECT encode(overlay(E'Th\\000omas'::bytea placing E'\\002\\003'::bytea from 5 for 3),'escape'); 1869 encode 1870----------------- 1871 Th\000o\x02\x03 1872(1 row) 1873 1874