1-- 2-- STRINGS 3-- Test various data entry syntaxes. 4-- 5 6-- SQL string continuation syntax 7-- E021-03 character string literals 8SELECT 'first line' 9' - next line' 10 ' - third line' 11 AS "Three lines to one"; 12 13-- illegal string continuation syntax 14SELECT 'first line' 15' - next line' /* this comment is not allowed here */ 16' - third line' 17 AS "Illegal comment within continuation"; 18 19-- Unicode escapes 20SET standard_conforming_strings TO on; 21 22SELECT U&'d\0061t\+000061' AS U&"d\0061t\+000061"; 23SELECT U&'d!0061t\+000061' UESCAPE '!' AS U&"d*0061t\+000061" UESCAPE '*'; 24SELECT U&'a\\b' AS "a\b"; 25 26SELECT U&' \' UESCAPE '!' AS "tricky"; 27SELECT 'tricky' AS U&"\" UESCAPE '!'; 28 29SELECT U&'wrong: \061'; 30SELECT U&'wrong: \+0061'; 31SELECT U&'wrong: +0061' UESCAPE +; 32SELECT U&'wrong: +0061' UESCAPE '+'; 33 34SELECT U&'wrong: \db99'; 35SELECT U&'wrong: \db99xy'; 36SELECT U&'wrong: \db99\\'; 37SELECT U&'wrong: \db99\0061'; 38SELECT U&'wrong: \+00db99\+000061'; 39SELECT U&'wrong: \+2FFFFF'; 40 41-- while we're here, check the same cases in E-style literals 42SELECT E'd\u0061t\U00000061' AS "data"; 43SELECT E'a\\b' AS "a\b"; 44SELECT E'wrong: \u061'; 45SELECT E'wrong: \U0061'; 46SELECT E'wrong: \udb99'; 47SELECT E'wrong: \udb99xy'; 48SELECT E'wrong: \udb99\\'; 49SELECT E'wrong: \udb99\u0061'; 50SELECT E'wrong: \U0000db99\U00000061'; 51SELECT E'wrong: \U002FFFFF'; 52 53SET standard_conforming_strings TO off; 54 55SELECT U&'d\0061t\+000061' AS U&"d\0061t\+000061"; 56SELECT U&'d!0061t\+000061' UESCAPE '!' AS U&"d*0061t\+000061" UESCAPE '*'; 57 58SELECT U&' \' UESCAPE '!' AS "tricky"; 59SELECT 'tricky' AS U&"\" UESCAPE '!'; 60 61SELECT U&'wrong: \061'; 62SELECT U&'wrong: \+0061'; 63SELECT U&'wrong: +0061' UESCAPE '+'; 64 65RESET standard_conforming_strings; 66 67-- bytea 68SET bytea_output TO hex; 69SELECT E'\\xDeAdBeEf'::bytea; 70SELECT E'\\x De Ad Be Ef '::bytea; 71SELECT E'\\xDeAdBeE'::bytea; 72SELECT E'\\xDeAdBeEx'::bytea; 73SELECT E'\\xDe00BeEf'::bytea; 74SELECT E'DeAdBeEf'::bytea; 75SELECT E'De\\000dBeEf'::bytea; 76SELECT E'De\123dBeEf'::bytea; 77SELECT E'De\\123dBeEf'::bytea; 78SELECT E'De\\678dBeEf'::bytea; 79 80SET bytea_output TO escape; 81SELECT E'\\xDeAdBeEf'::bytea; 82SELECT E'\\x De Ad Be Ef '::bytea; 83SELECT E'\\xDe00BeEf'::bytea; 84SELECT E'DeAdBeEf'::bytea; 85SELECT E'De\\000dBeEf'::bytea; 86SELECT E'De\\123dBeEf'::bytea; 87 88-- 89-- test conversions between various string types 90-- E021-10 implicit casting among the character data types 91-- 92 93SELECT CAST(f1 AS text) AS "text(char)" FROM CHAR_TBL; 94 95SELECT CAST(f1 AS text) AS "text(varchar)" FROM VARCHAR_TBL; 96 97SELECT CAST(name 'namefield' AS text) AS "text(name)"; 98 99-- since this is an explicit cast, it should truncate w/o error: 100SELECT CAST(f1 AS char(10)) AS "char(text)" FROM TEXT_TBL; 101-- note: implicit-cast case is tested in char.sql 102 103SELECT CAST(f1 AS char(20)) AS "char(text)" FROM TEXT_TBL; 104 105SELECT CAST(f1 AS char(10)) AS "char(varchar)" FROM VARCHAR_TBL; 106 107SELECT CAST(name 'namefield' AS char(10)) AS "char(name)"; 108 109SELECT CAST(f1 AS varchar) AS "varchar(text)" FROM TEXT_TBL; 110 111SELECT CAST(f1 AS varchar) AS "varchar(char)" FROM CHAR_TBL; 112 113SELECT CAST(name 'namefield' AS varchar) AS "varchar(name)"; 114 115-- 116-- test SQL string functions 117-- E### and T### are feature reference numbers from SQL99 118-- 119 120-- E021-09 trim function 121SELECT TRIM(BOTH FROM ' bunch o blanks ') = 'bunch o blanks' AS "bunch o blanks"; 122 123SELECT TRIM(LEADING FROM ' bunch o blanks ') = 'bunch o blanks ' AS "bunch o blanks "; 124 125SELECT TRIM(TRAILING FROM ' bunch o blanks ') = ' bunch o blanks' AS " bunch o blanks"; 126 127SELECT TRIM(BOTH 'x' FROM 'xxxxxsome Xsxxxxx') = 'some Xs' AS "some Xs"; 128 129-- E021-06 substring expression 130SELECT SUBSTRING('1234567890' FROM 3) = '34567890' AS "34567890"; 131 132SELECT SUBSTRING('1234567890' FROM 4 FOR 3) = '456' AS "456"; 133 134-- test overflow cases 135SELECT SUBSTRING('string' FROM 2 FOR 2147483646) AS "tring"; 136SELECT SUBSTRING('string' FROM -10 FOR 2147483646) AS "string"; 137SELECT SUBSTRING('string' FROM -10 FOR -2147483646) AS "error"; 138 139-- T581 regular expression substring (with SQL's bizarre regexp syntax) 140SELECT SUBSTRING('abcdefg' SIMILAR 'a#"(b_d)#"%' ESCAPE '#') AS "bcd"; 141-- obsolete SQL99 syntax 142SELECT SUBSTRING('abcdefg' FROM 'a#"(b_d)#"%' FOR '#') AS "bcd"; 143 144-- No match should return NULL 145SELECT SUBSTRING('abcdefg' SIMILAR '#"(b_d)#"%' ESCAPE '#') IS NULL AS "True"; 146 147-- Null inputs should return NULL 148SELECT SUBSTRING('abcdefg' SIMILAR '%' ESCAPE NULL) IS NULL AS "True"; 149SELECT SUBSTRING(NULL SIMILAR '%' ESCAPE '#') IS NULL AS "True"; 150SELECT SUBSTRING('abcdefg' SIMILAR NULL ESCAPE '#') IS NULL AS "True"; 151 152-- The first and last parts should act non-greedy 153SELECT SUBSTRING('abcdefg' SIMILAR 'a#"%#"g' ESCAPE '#') AS "bcdef"; 154SELECT SUBSTRING('abcdefg' SIMILAR 'a*#"%#"g*' ESCAPE '#') AS "abcdefg"; 155 156-- Vertical bar in any part affects only that part 157SELECT SUBSTRING('abcdefg' SIMILAR 'a|b#"%#"g' ESCAPE '#') AS "bcdef"; 158SELECT SUBSTRING('abcdefg' SIMILAR 'a#"%#"x|g' ESCAPE '#') AS "bcdef"; 159SELECT SUBSTRING('abcdefg' SIMILAR 'a#"%|ab#"g' ESCAPE '#') AS "bcdef"; 160 161-- Can't have more than two part separators 162SELECT SUBSTRING('abcdefg' SIMILAR 'a*#"%#"g*#"x' ESCAPE '#') AS "error"; 163 164-- Postgres extension: with 0 or 1 separator, assume parts 1 and 3 are empty 165SELECT SUBSTRING('abcdefg' SIMILAR 'a#"%g' ESCAPE '#') AS "bcdefg"; 166SELECT SUBSTRING('abcdefg' SIMILAR 'a%g' ESCAPE '#') AS "abcdefg"; 167 168-- substring() with just two arguments is not allowed by SQL spec; 169-- we accept it, but we interpret the pattern as a POSIX regexp not SQL 170SELECT SUBSTRING('abcdefg' FROM 'c.e') AS "cde"; 171 172-- With a parenthesized subexpression, return only what matches the subexpr 173SELECT SUBSTRING('abcdefg' FROM 'b(.*)f') AS "cde"; 174 175-- Check behavior of SIMILAR TO, which uses largely the same regexp variant 176SELECT 'abcdefg' SIMILAR TO '_bcd%' AS true; 177SELECT 'abcdefg' SIMILAR TO 'bcd%' AS false; 178SELECT 'abcdefg' SIMILAR TO '_bcd#%' ESCAPE '#' AS false; 179SELECT 'abcd%' SIMILAR TO '_bcd#%' ESCAPE '#' AS true; 180-- Postgres uses '\' as the default escape character, which is not per spec 181SELECT 'abcdefg' SIMILAR TO '_bcd\%' AS false; 182-- and an empty string to mean "no escape", which is also not per spec 183SELECT 'abcd\efg' SIMILAR TO '_bcd\%' ESCAPE '' AS true; 184-- these behaviors are per spec, though: 185SELECT 'abcdefg' SIMILAR TO '_bcd%' ESCAPE NULL AS null; 186SELECT 'abcdefg' SIMILAR TO '_bcd#%' ESCAPE '##' AS error; 187 188-- Test back reference in regexp_replace 189SELECT regexp_replace('1112223333', E'(\\d{3})(\\d{3})(\\d{4})', E'(\\1) \\2-\\3'); 190SELECT regexp_replace('AAA BBB CCC ', E'\\s+', ' ', 'g'); 191SELECT regexp_replace('AAA', '^|$', 'Z', 'g'); 192SELECT regexp_replace('AAA aaa', 'A+', 'Z', 'gi'); 193-- invalid regexp option 194SELECT regexp_replace('AAA aaa', 'A+', 'Z', 'z'); 195 196-- set so we can tell NULL from empty string 197\pset null '\\N' 198 199-- return all matches from regexp 200SELECT regexp_matches('foobarbequebaz', $re$(bar)(beque)$re$); 201 202-- test case insensitive 203SELECT regexp_matches('foObARbEqUEbAz', $re$(bar)(beque)$re$, 'i'); 204 205-- global option - more than one match 206SELECT regexp_matches('foobarbequebazilbarfbonk', $re$(b[^b]+)(b[^b]+)$re$, 'g'); 207 208-- empty capture group (matched empty string) 209SELECT regexp_matches('foobarbequebaz', $re$(bar)(.*)(beque)$re$); 210-- no match 211SELECT regexp_matches('foobarbequebaz', $re$(bar)(.+)(beque)$re$); 212-- optional capture group did not match, null entry in array 213SELECT regexp_matches('foobarbequebaz', $re$(bar)(.+)?(beque)$re$); 214 215-- no capture groups 216SELECT regexp_matches('foobarbequebaz', $re$barbeque$re$); 217 218-- start/end-of-line matches are of zero length 219SELECT regexp_matches('foo' || chr(10) || 'bar' || chr(10) || 'bequq' || chr(10) || 'baz', '^', 'mg'); 220SELECT regexp_matches('foo' || chr(10) || 'bar' || chr(10) || 'bequq' || chr(10) || 'baz', '$', 'mg'); 221SELECT regexp_matches('1' || chr(10) || '2' || chr(10) || '3' || chr(10) || '4' || chr(10), '^.?', 'mg'); 222SELECT regexp_matches(chr(10) || '1' || chr(10) || '2' || chr(10) || '3' || chr(10) || '4' || chr(10), '.?$', 'mg'); 223SELECT regexp_matches(chr(10) || '1' || chr(10) || '2' || chr(10) || '3' || chr(10) || '4', '.?$', 'mg'); 224 225-- give me errors 226SELECT regexp_matches('foobarbequebaz', $re$(bar)(beque)$re$, 'gz'); 227SELECT regexp_matches('foobarbequebaz', $re$(barbeque$re$); 228SELECT regexp_matches('foobarbequebaz', $re$(bar)(beque){2,1}$re$); 229 230-- split string on regexp 231SELECT foo, length(foo) FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', $re$\s+$re$) AS foo; 232SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', $re$\s+$re$); 233 234SELECT foo, length(foo) FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', $re$\s*$re$) AS foo; 235SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', $re$\s*$re$); 236SELECT foo, length(foo) FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', '') AS foo; 237SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', ''); 238-- case insensitive 239SELECT foo, length(foo) FROM regexp_split_to_table('thE QUick bROWn FOx jUMPs ovEr The lazy dOG', 'e', 'i') AS foo; 240SELECT regexp_split_to_array('thE QUick bROWn FOx jUMPs ovEr The lazy dOG', 'e', 'i'); 241-- no match of pattern 242SELECT foo, length(foo) FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', 'nomatch') AS foo; 243SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', 'nomatch'); 244-- some corner cases 245SELECT regexp_split_to_array('123456','1'); 246SELECT regexp_split_to_array('123456','6'); 247SELECT regexp_split_to_array('123456','.'); 248SELECT regexp_split_to_array('123456',''); 249SELECT regexp_split_to_array('123456','(?:)'); 250SELECT regexp_split_to_array('1',''); 251-- errors 252SELECT foo, length(foo) FROM regexp_split_to_table('thE QUick bROWn FOx jUMPs ovEr The lazy dOG', 'e', 'zippy') AS foo; 253SELECT regexp_split_to_array('thE QUick bROWn FOx jUMPs ovEr The lazy dOG', 'e', 'iz'); 254-- global option meaningless for regexp_split 255SELECT foo, length(foo) FROM regexp_split_to_table('thE QUick bROWn FOx jUMPs ovEr The lazy dOG', 'e', 'g') AS foo; 256SELECT regexp_split_to_array('thE QUick bROWn FOx jUMPs ovEr The lazy dOG', 'e', 'g'); 257 258-- change NULL-display back 259\pset null '' 260 261-- E021-11 position expression 262SELECT POSITION('4' IN '1234567890') = '4' AS "4"; 263 264SELECT POSITION('5' IN '1234567890') = '5' AS "5"; 265 266-- T312 character overlay function 267SELECT OVERLAY('abcdef' PLACING '45' FROM 4) AS "abc45f"; 268 269SELECT OVERLAY('yabadoo' PLACING 'daba' FROM 5) AS "yabadaba"; 270 271SELECT OVERLAY('yabadoo' PLACING 'daba' FROM 5 FOR 0) AS "yabadabadoo"; 272 273SELECT OVERLAY('babosa' PLACING 'ubb' FROM 2 FOR 4) AS "bubba"; 274 275-- 276-- test LIKE 277-- Be sure to form every test as a LIKE/NOT LIKE pair. 278-- 279 280-- simplest examples 281-- E061-04 like predicate 282SELECT 'hawkeye' LIKE 'h%' AS "true"; 283SELECT 'hawkeye' NOT LIKE 'h%' AS "false"; 284 285SELECT 'hawkeye' LIKE 'H%' AS "false"; 286SELECT 'hawkeye' NOT LIKE 'H%' AS "true"; 287 288SELECT 'hawkeye' LIKE 'indio%' AS "false"; 289SELECT 'hawkeye' NOT LIKE 'indio%' AS "true"; 290 291SELECT 'hawkeye' LIKE 'h%eye' AS "true"; 292SELECT 'hawkeye' NOT LIKE 'h%eye' AS "false"; 293 294SELECT 'indio' LIKE '_ndio' AS "true"; 295SELECT 'indio' NOT LIKE '_ndio' AS "false"; 296 297SELECT 'indio' LIKE 'in__o' AS "true"; 298SELECT 'indio' NOT LIKE 'in__o' AS "false"; 299 300SELECT 'indio' LIKE 'in_o' AS "false"; 301SELECT 'indio' NOT LIKE 'in_o' AS "true"; 302 303SELECT 'abc'::name LIKE '_b_' AS "true"; 304SELECT 'abc'::name NOT LIKE '_b_' AS "false"; 305 306SELECT 'abc'::bytea LIKE '_b_'::bytea AS "true"; 307SELECT 'abc'::bytea NOT LIKE '_b_'::bytea AS "false"; 308 309-- unused escape character 310SELECT 'hawkeye' LIKE 'h%' ESCAPE '#' AS "true"; 311SELECT 'hawkeye' NOT LIKE 'h%' ESCAPE '#' AS "false"; 312 313SELECT 'indio' LIKE 'ind_o' ESCAPE '$' AS "true"; 314SELECT 'indio' NOT LIKE 'ind_o' ESCAPE '$' AS "false"; 315 316-- escape character 317-- E061-05 like predicate with escape clause 318SELECT 'h%' LIKE 'h#%' ESCAPE '#' AS "true"; 319SELECT 'h%' NOT LIKE 'h#%' ESCAPE '#' AS "false"; 320 321SELECT 'h%wkeye' LIKE 'h#%' ESCAPE '#' AS "false"; 322SELECT 'h%wkeye' NOT LIKE 'h#%' ESCAPE '#' AS "true"; 323 324SELECT 'h%wkeye' LIKE 'h#%%' ESCAPE '#' AS "true"; 325SELECT 'h%wkeye' NOT LIKE 'h#%%' ESCAPE '#' AS "false"; 326 327SELECT 'h%awkeye' LIKE 'h#%a%k%e' ESCAPE '#' AS "true"; 328SELECT 'h%awkeye' NOT LIKE 'h#%a%k%e' ESCAPE '#' AS "false"; 329 330SELECT 'indio' LIKE '_ndio' ESCAPE '$' AS "true"; 331SELECT 'indio' NOT LIKE '_ndio' ESCAPE '$' AS "false"; 332 333SELECT 'i_dio' LIKE 'i$_d_o' ESCAPE '$' AS "true"; 334SELECT 'i_dio' NOT LIKE 'i$_d_o' ESCAPE '$' AS "false"; 335 336SELECT 'i_dio' LIKE 'i$_nd_o' ESCAPE '$' AS "false"; 337SELECT 'i_dio' NOT LIKE 'i$_nd_o' ESCAPE '$' AS "true"; 338 339SELECT 'i_dio' LIKE 'i$_d%o' ESCAPE '$' AS "true"; 340SELECT 'i_dio' NOT LIKE 'i$_d%o' ESCAPE '$' AS "false"; 341 342SELECT 'a_c'::bytea LIKE 'a$__'::bytea ESCAPE '$'::bytea AS "true"; 343SELECT 'a_c'::bytea NOT LIKE 'a$__'::bytea ESCAPE '$'::bytea AS "false"; 344 345-- escape character same as pattern character 346SELECT 'maca' LIKE 'm%aca' ESCAPE '%' AS "true"; 347SELECT 'maca' NOT LIKE 'm%aca' ESCAPE '%' AS "false"; 348 349SELECT 'ma%a' LIKE 'm%a%%a' ESCAPE '%' AS "true"; 350SELECT 'ma%a' NOT LIKE 'm%a%%a' ESCAPE '%' AS "false"; 351 352SELECT 'bear' LIKE 'b_ear' ESCAPE '_' AS "true"; 353SELECT 'bear' NOT LIKE 'b_ear' ESCAPE '_' AS "false"; 354 355SELECT 'be_r' LIKE 'b_e__r' ESCAPE '_' AS "true"; 356SELECT 'be_r' NOT LIKE 'b_e__r' ESCAPE '_' AS "false"; 357 358SELECT 'be_r' LIKE '__e__r' ESCAPE '_' AS "false"; 359SELECT 'be_r' NOT LIKE '__e__r' ESCAPE '_' AS "true"; 360 361 362-- 363-- test ILIKE (case-insensitive LIKE) 364-- Be sure to form every test as an ILIKE/NOT ILIKE pair. 365-- 366 367SELECT 'hawkeye' ILIKE 'h%' AS "true"; 368SELECT 'hawkeye' NOT ILIKE 'h%' AS "false"; 369 370SELECT 'hawkeye' ILIKE 'H%' AS "true"; 371SELECT 'hawkeye' NOT ILIKE 'H%' AS "false"; 372 373SELECT 'hawkeye' ILIKE 'H%Eye' AS "true"; 374SELECT 'hawkeye' NOT ILIKE 'H%Eye' AS "false"; 375 376SELECT 'Hawkeye' ILIKE 'h%' AS "true"; 377SELECT 'Hawkeye' NOT ILIKE 'h%' AS "false"; 378 379SELECT 'ABC'::name ILIKE '_b_' AS "true"; 380SELECT 'ABC'::name NOT ILIKE '_b_' AS "false"; 381 382-- 383-- test %/_ combination cases, cf bugs #4821 and #5478 384-- 385 386SELECT 'foo' LIKE '_%' as t, 'f' LIKE '_%' as t, '' LIKE '_%' as f; 387SELECT 'foo' LIKE '%_' as t, 'f' LIKE '%_' as t, '' LIKE '%_' as f; 388 389SELECT 'foo' LIKE '__%' as t, 'foo' LIKE '___%' as t, 'foo' LIKE '____%' as f; 390SELECT 'foo' LIKE '%__' as t, 'foo' LIKE '%___' as t, 'foo' LIKE '%____' as f; 391 392SELECT 'jack' LIKE '%____%' AS t; 393 394 395-- 396-- basic tests of LIKE with indexes 397-- 398 399CREATE TABLE texttest (a text PRIMARY KEY, b int); 400SELECT * FROM texttest WHERE a LIKE '%1%'; 401 402CREATE TABLE byteatest (a bytea PRIMARY KEY, b int); 403SELECT * FROM byteatest WHERE a LIKE '%1%'; 404 405DROP TABLE texttest, byteatest; 406 407 408-- 409-- test implicit type conversion 410-- 411 412-- E021-07 character concatenation 413SELECT 'unknown' || ' and unknown' AS "Concat unknown types"; 414 415SELECT text 'text' || ' and unknown' AS "Concat text to unknown type"; 416 417SELECT char(20) 'characters' || ' and text' AS "Concat char to unknown type"; 418 419SELECT text 'text' || char(20) ' and characters' AS "Concat text to char"; 420 421SELECT text 'text' || varchar ' and varchar' AS "Concat text to varchar"; 422 423-- 424-- test substr with toasted text values 425-- 426CREATE TABLE toasttest(f1 text); 427 428insert into toasttest values(repeat('1234567890',10000)); 429insert into toasttest values(repeat('1234567890',10000)); 430 431-- 432-- Ensure that some values are uncompressed, to test the faster substring 433-- operation used in that case 434-- 435alter table toasttest alter column f1 set storage external; 436insert into toasttest values(repeat('1234567890',10000)); 437insert into toasttest values(repeat('1234567890',10000)); 438 439-- If the starting position is zero or less, then return from the start of the string 440-- adjusting the length to be consistent with the "negative start" per SQL. 441SELECT substr(f1, -1, 5) from toasttest; 442 443-- If the length is less than zero, an ERROR is thrown. 444SELECT substr(f1, 5, -1) from toasttest; 445 446-- If no third argument (length) is provided, the length to the end of the 447-- string is assumed. 448SELECT substr(f1, 99995) from toasttest; 449 450-- If start plus length is > string length, the result is truncated to 451-- string length 452SELECT substr(f1, 99995, 10) from toasttest; 453 454TRUNCATE TABLE toasttest; 455INSERT INTO toasttest values (repeat('1234567890',300)); 456INSERT INTO toasttest values (repeat('1234567890',300)); 457INSERT INTO toasttest values (repeat('1234567890',300)); 458INSERT INTO toasttest values (repeat('1234567890',300)); 459-- expect >0 blocks 460SELECT pg_relation_size(reltoastrelid) = 0 AS is_empty 461 FROM pg_class where relname = 'toasttest'; 462 463TRUNCATE TABLE toasttest; 464ALTER TABLE toasttest set (toast_tuple_target = 4080); 465INSERT INTO toasttest values (repeat('1234567890',300)); 466INSERT INTO toasttest values (repeat('1234567890',300)); 467INSERT INTO toasttest values (repeat('1234567890',300)); 468INSERT INTO toasttest values (repeat('1234567890',300)); 469-- expect 0 blocks 470SELECT pg_relation_size(reltoastrelid) = 0 AS is_empty 471 FROM pg_class where relname = 'toasttest'; 472 473DROP TABLE toasttest; 474 475-- 476-- test substr with toasted bytea values 477-- 478CREATE TABLE toasttest(f1 bytea); 479 480insert into toasttest values(decode(repeat('1234567890',10000),'escape')); 481insert into toasttest values(decode(repeat('1234567890',10000),'escape')); 482 483-- 484-- Ensure that some values are uncompressed, to test the faster substring 485-- operation used in that case 486-- 487alter table toasttest alter column f1 set storage external; 488insert into toasttest values(decode(repeat('1234567890',10000),'escape')); 489insert into toasttest values(decode(repeat('1234567890',10000),'escape')); 490 491-- If the starting position is zero or less, then return from the start of the string 492-- adjusting the length to be consistent with the "negative start" per SQL. 493SELECT substr(f1, -1, 5) from toasttest; 494 495-- If the length is less than zero, an ERROR is thrown. 496SELECT substr(f1, 5, -1) from toasttest; 497 498-- If no third argument (length) is provided, the length to the end of the 499-- string is assumed. 500SELECT substr(f1, 99995) from toasttest; 501 502-- If start plus length is > string length, the result is truncated to 503-- string length 504SELECT substr(f1, 99995, 10) from toasttest; 505 506DROP TABLE toasttest; 507 508-- test internally compressing datums 509 510-- this tests compressing a datum to a very small size which exercises a 511-- corner case in packed-varlena handling: even though small, the compressed 512-- datum must be given a 4-byte header because there are no bits to indicate 513-- compression in a 1-byte header 514 515CREATE TABLE toasttest (c char(4096)); 516INSERT INTO toasttest VALUES('x'); 517SELECT length(c), c::text FROM toasttest; 518SELECT c FROM toasttest; 519DROP TABLE toasttest; 520 521-- 522-- test length 523-- 524 525SELECT length('abcdef') AS "length_6"; 526 527-- 528-- test strpos 529-- 530 531SELECT strpos('abcdef', 'cd') AS "pos_3"; 532 533SELECT strpos('abcdef', 'xy') AS "pos_0"; 534 535SELECT strpos('abcdef', '') AS "pos_1"; 536 537SELECT strpos('', 'xy') AS "pos_0"; 538 539SELECT strpos('', '') AS "pos_1"; 540 541-- 542-- test replace 543-- 544SELECT replace('abcdef', 'de', '45') AS "abc45f"; 545 546SELECT replace('yabadabadoo', 'ba', '123') AS "ya123da123doo"; 547 548SELECT replace('yabadoo', 'bad', '') AS "yaoo"; 549 550-- 551-- test split_part 552-- 553select split_part('','@',1) AS "empty string"; 554 555select split_part('','@',-1) AS "empty string"; 556 557select split_part('joeuser@mydatabase','',1) AS "joeuser@mydatabase"; 558 559select split_part('joeuser@mydatabase','',2) AS "empty string"; 560 561select split_part('joeuser@mydatabase','',-1) AS "joeuser@mydatabase"; 562 563select split_part('joeuser@mydatabase','',-2) AS "empty string"; 564 565select split_part('joeuser@mydatabase','@',0) AS "an error"; 566 567select split_part('joeuser@mydatabase','@@',1) AS "joeuser@mydatabase"; 568 569select split_part('joeuser@mydatabase','@@',2) AS "empty string"; 570 571select split_part('joeuser@mydatabase','@',1) AS "joeuser"; 572 573select split_part('joeuser@mydatabase','@',2) AS "mydatabase"; 574 575select split_part('joeuser@mydatabase','@',3) AS "empty string"; 576 577select split_part('@joeuser@mydatabase@','@',2) AS "joeuser"; 578 579select split_part('joeuser@mydatabase','@',-1) AS "mydatabase"; 580 581select split_part('joeuser@mydatabase','@',-2) AS "joeuser"; 582 583select split_part('joeuser@mydatabase','@',-3) AS "empty string"; 584 585select split_part('@joeuser@mydatabase@','@',-2) AS "mydatabase"; 586 587-- 588-- test to_hex 589-- 590select to_hex(256*256*256 - 1) AS "ffffff"; 591 592select to_hex(256::bigint*256::bigint*256::bigint*256::bigint - 1) AS "ffffffff"; 593 594-- 595-- MD5 test suite - from IETF RFC 1321 596-- (see: ftp://ftp.rfc-editor.org/in-notes/rfc1321.txt) 597-- 598select md5('') = 'd41d8cd98f00b204e9800998ecf8427e' AS "TRUE"; 599 600select md5('a') = '0cc175b9c0f1b6a831c399e269772661' AS "TRUE"; 601 602select md5('abc') = '900150983cd24fb0d6963f7d28e17f72' AS "TRUE"; 603 604select md5('message digest') = 'f96b697d7cb7938d525a2f31aaf161d0' AS "TRUE"; 605 606select md5('abcdefghijklmnopqrstuvwxyz') = 'c3fcd3d76192e4007dfb496cca67e13b' AS "TRUE"; 607 608select md5('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789') = 'd174ab98d277d9f5a5611c2c9f419d9f' AS "TRUE"; 609 610select md5('12345678901234567890123456789012345678901234567890123456789012345678901234567890') = '57edf4a22be3c955ac49da2e2107b67a' AS "TRUE"; 611 612select md5(''::bytea) = 'd41d8cd98f00b204e9800998ecf8427e' AS "TRUE"; 613 614select md5('a'::bytea) = '0cc175b9c0f1b6a831c399e269772661' AS "TRUE"; 615 616select md5('abc'::bytea) = '900150983cd24fb0d6963f7d28e17f72' AS "TRUE"; 617 618select md5('message digest'::bytea) = 'f96b697d7cb7938d525a2f31aaf161d0' AS "TRUE"; 619 620select md5('abcdefghijklmnopqrstuvwxyz'::bytea) = 'c3fcd3d76192e4007dfb496cca67e13b' AS "TRUE"; 621 622select md5('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'::bytea) = 'd174ab98d277d9f5a5611c2c9f419d9f' AS "TRUE"; 623 624select md5('12345678901234567890123456789012345678901234567890123456789012345678901234567890'::bytea) = '57edf4a22be3c955ac49da2e2107b67a' AS "TRUE"; 625 626-- 627-- SHA-2 628-- 629SET bytea_output TO hex; 630 631SELECT sha224(''); 632SELECT sha224('The quick brown fox jumps over the lazy dog.'); 633 634SELECT sha256(''); 635SELECT sha256('The quick brown fox jumps over the lazy dog.'); 636 637SELECT sha384(''); 638SELECT sha384('The quick brown fox jumps over the lazy dog.'); 639 640SELECT sha512(''); 641SELECT sha512('The quick brown fox jumps over the lazy dog.'); 642 643-- 644-- encode/decode 645-- 646SELECT encode('\x1234567890abcdef00', 'hex'); 647SELECT decode('1234567890abcdef00', 'hex'); 648SELECT encode(('\x' || repeat('1234567890abcdef0001', 7))::bytea, 'base64'); 649SELECT decode(encode(('\x' || repeat('1234567890abcdef0001', 7))::bytea, 650 'base64'), 'base64'); 651SELECT encode('\x1234567890abcdef00', 'escape'); 652SELECT decode(encode('\x1234567890abcdef00', 'escape'), 'escape'); 653 654-- 655-- get_bit/set_bit etc 656-- 657SELECT get_bit('\x1234567890abcdef00'::bytea, 43); 658SELECT get_bit('\x1234567890abcdef00'::bytea, 99); -- error 659SELECT set_bit('\x1234567890abcdef00'::bytea, 43, 0); 660SELECT set_bit('\x1234567890abcdef00'::bytea, 99, 0); -- error 661SELECT get_byte('\x1234567890abcdef00'::bytea, 3); 662SELECT get_byte('\x1234567890abcdef00'::bytea, 99); -- error 663SELECT set_byte('\x1234567890abcdef00'::bytea, 7, 11); 664SELECT set_byte('\x1234567890abcdef00'::bytea, 99, 11); -- error 665 666-- 667-- test behavior of escape_string_warning and standard_conforming_strings options 668-- 669set escape_string_warning = off; 670set standard_conforming_strings = off; 671 672show escape_string_warning; 673show standard_conforming_strings; 674 675set escape_string_warning = on; 676set standard_conforming_strings = on; 677 678show escape_string_warning; 679show standard_conforming_strings; 680 681select 'a\bcd' as f1, 'a\b''cd' as f2, 'a\b''''cd' as f3, 'abcd\' as f4, 'ab\''cd' as f5, '\\' as f6; 682 683set standard_conforming_strings = off; 684 685select 'a\\bcd' as f1, 'a\\b\'cd' as f2, 'a\\b\'''cd' as f3, 'abcd\\' as f4, 'ab\\\'cd' as f5, '\\\\' as f6; 686 687set escape_string_warning = off; 688set standard_conforming_strings = on; 689 690select 'a\bcd' as f1, 'a\b''cd' as f2, 'a\b''''cd' as f3, 'abcd\' as f4, 'ab\''cd' as f5, '\\' as f6; 691 692set standard_conforming_strings = off; 693 694select 'a\\bcd' as f1, 'a\\b\'cd' as f2, 'a\\b\'''cd' as f3, 'abcd\\' as f4, 'ab\\\'cd' as f5, '\\\\' as f6; 695 696reset standard_conforming_strings; 697 698 699-- 700-- Additional string functions 701-- 702SET bytea_output TO escape; 703 704SELECT initcap('hi THOMAS'); 705 706SELECT lpad('hi', 5, 'xy'); 707SELECT lpad('hi', 5); 708SELECT lpad('hi', -5, 'xy'); 709SELECT lpad('hello', 2); 710SELECT lpad('hi', 5, ''); 711 712SELECT rpad('hi', 5, 'xy'); 713SELECT rpad('hi', 5); 714SELECT rpad('hi', -5, 'xy'); 715SELECT rpad('hello', 2); 716SELECT rpad('hi', 5, ''); 717 718SELECT ltrim('zzzytrim', 'xyz'); 719 720SELECT translate('', '14', 'ax'); 721SELECT translate('12345', '14', 'ax'); 722 723SELECT ascii('x'); 724SELECT ascii(''); 725 726SELECT chr(65); 727SELECT chr(0); 728 729SELECT repeat('Pg', 4); 730SELECT repeat('Pg', -4); 731 732SELECT SUBSTRING('1234567890'::bytea FROM 3) "34567890"; 733SELECT SUBSTRING('1234567890'::bytea FROM 4 FOR 3) AS "456"; 734SELECT SUBSTRING('string'::bytea FROM 2 FOR 2147483646) AS "tring"; 735SELECT SUBSTRING('string'::bytea FROM -10 FOR 2147483646) AS "string"; 736SELECT SUBSTRING('string'::bytea FROM -10 FOR -2147483646) AS "error"; 737 738SELECT trim(E'\\000'::bytea from E'\\000Tom\\000'::bytea); 739SELECT trim(leading E'\\000'::bytea from E'\\000Tom\\000'::bytea); 740SELECT trim(trailing E'\\000'::bytea from E'\\000Tom\\000'::bytea); 741SELECT btrim(E'\\000trim\\000'::bytea, E'\\000'::bytea); 742SELECT btrim(''::bytea, E'\\000'::bytea); 743SELECT btrim(E'\\000trim\\000'::bytea, ''::bytea); 744SELECT encode(overlay(E'Th\\000omas'::bytea placing E'Th\\001omas'::bytea from 2),'escape'); 745SELECT encode(overlay(E'Th\\000omas'::bytea placing E'\\002\\003'::bytea from 8),'escape'); 746SELECT encode(overlay(E'Th\\000omas'::bytea placing E'\\002\\003'::bytea from 5 for 3),'escape'); 747 748SELECT bit_count('\x1234567890'::bytea); 749 750SELECT unistr('\0064at\+0000610'); 751SELECT unistr('d\u0061t\U000000610'); 752SELECT unistr('a\\b'); 753-- errors: 754SELECT unistr('wrong: \db99'); 755SELECT unistr('wrong: \db99\0061'); 756SELECT unistr('wrong: \+00db99\+000061'); 757SELECT unistr('wrong: \+2FFFFF'); 758SELECT unistr('wrong: \udb99\u0061'); 759SELECT unistr('wrong: \U0000db99\U00000061'); 760SELECT unistr('wrong: \U002FFFFF'); 761SELECT unistr('wrong: \xyz'); 762