| blob | 0f95b9400b697dc69fa11a7ca2cbf7402cf8b114 |
1 --
2 -- STRINGS
3 -- Test various data entry syntaxes.
4 --
5 -- SQL string continuation syntax
6 -- E021-03 character string literals
7 SELECT '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)
16 -- illegal string continuation syntax
17 SELECT 'first line'
18 ' - next line' /* this comment is not allowed here */
19 ' - third line'
20 AS "Illegal comment within continuation";
21 ERROR: syntax error at or near "' - third line'"
22 LINE 3: ' - third line'
23 ^
24 -- Unicode escapes
25 SET standard_conforming_strings TO on;
26 SELECT U&'d\0061t\+000061' AS U&"d\0061t\+000061";
27 data
28 ------
29 data
30 (1 row)
32 SELECT U&'d!0061t\+000061' UESCAPE '!' AS U&"d*0061t\+000061" UESCAPE '*';
33 dat\+000061
34 -------------
35 dat\+000061
36 (1 row)
38 SELECT U&'a\\b' AS "a\b";
39 a\b
40 -----
41 a\b
42 (1 row)
44 SELECT U&' \' UESCAPE '!' AS "tricky";
45 tricky
46 --------
47 \
48 (1 row)
50 SELECT 'tricky' AS U&"\" UESCAPE '!';
51 \
52 --------
53 tricky
54 (1 row)
56 SELECT U&'wrong: \061';
57 ERROR: invalid Unicode escape
58 LINE 1: SELECT U&'wrong: \061';
59 ^
60 HINT: Unicode escapes must be \XXXX or \+XXXXXX.
61 SELECT U&'wrong: \+0061';
62 ERROR: invalid Unicode escape
63 LINE 1: SELECT U&'wrong: \+0061';
64 ^
65 HINT: Unicode escapes must be \XXXX or \+XXXXXX.
66 SELECT U&'wrong: +0061' UESCAPE +;
67 ERROR: UESCAPE must be followed by a simple string literal at or near "+"
68 LINE 1: SELECT U&'wrong: +0061' UESCAPE +;
69 ^
70 SELECT U&'wrong: +0061' UESCAPE '+';
71 ERROR: invalid Unicode escape character at or near "'+'"
72 LINE 1: SELECT U&'wrong: +0061' UESCAPE '+';
73 ^
74 SELECT U&'wrong: \db99';
75 ERROR: invalid Unicode surrogate pair
76 LINE 1: SELECT U&'wrong: \db99';
77 ^
78 SELECT U&'wrong: \db99xy';
79 ERROR: invalid Unicode surrogate pair
80 LINE 1: SELECT U&'wrong: \db99xy';
81 ^
82 SELECT U&'wrong: \db99\\';
83 ERROR: invalid Unicode surrogate pair
84 LINE 1: SELECT U&'wrong: \db99\\';
85 ^
86 SELECT U&'wrong: \db99\0061';
87 ERROR: invalid Unicode surrogate pair
88 LINE 1: SELECT U&'wrong: \db99\0061';
89 ^
90 SELECT U&'wrong: \+00db99\+000061';
91 ERROR: invalid Unicode surrogate pair
92 LINE 1: SELECT U&'wrong: \+00db99\+000061';
93 ^
94 SELECT U&'wrong: \+2FFFFF';
95 ERROR: invalid Unicode escape value
96 LINE 1: SELECT U&'wrong: \+2FFFFF';
97 ^
98 -- while we're here, check the same cases in E-style literals
99 SELECT E'd\u0061t\U00000061' AS "data";
100 data
101 ------
102 data
103 (1 row)
105 SELECT E'a\\b' AS "a\b";
106 a\b
107 -----
108 a\b
109 (1 row)
111 SELECT E'wrong: \u061';
112 ERROR: invalid Unicode escape
113 LINE 1: SELECT E'wrong: \u061';
114 ^
115 HINT: Unicode escapes must be \uXXXX or \UXXXXXXXX.
116 SELECT E'wrong: \U0061';
117 ERROR: invalid Unicode escape
118 LINE 1: SELECT E'wrong: \U0061';
119 ^
120 HINT: Unicode escapes must be \uXXXX or \UXXXXXXXX.
121 SELECT E'wrong: \udb99';
122 ERROR: invalid Unicode surrogate pair at or near "'"
123 LINE 1: SELECT E'wrong: \udb99';
124 ^
125 SELECT E'wrong: \udb99xy';
126 ERROR: invalid Unicode surrogate pair at or near "x"
127 LINE 1: SELECT E'wrong: \udb99xy';
128 ^
129 SELECT E'wrong: \udb99\\';
130 ERROR: invalid Unicode surrogate pair at or near "\"
131 LINE 1: SELECT E'wrong: \udb99\\';
132 ^
133 SELECT E'wrong: \udb99\u0061';
134 ERROR: invalid Unicode surrogate pair at or near "\u0061"
135 LINE 1: SELECT E'wrong: \udb99\u0061';
136 ^
137 SELECT E'wrong: \U0000db99\U00000061';
138 ERROR: invalid Unicode surrogate pair at or near "\U00000061"
139 LINE 1: SELECT E'wrong: \U0000db99\U00000061';
140 ^
141 SELECT E'wrong: \U002FFFFF';
142 ERROR: invalid Unicode escape value at or near "\U002FFFFF"
143 LINE 1: SELECT E'wrong: \U002FFFFF';
144 ^
145 SET standard_conforming_strings TO off;
146 SELECT U&'d\0061t\+000061' AS U&"d\0061t\+000061";
147 ERROR: unsafe use of string constant with Unicode escapes
148 LINE 1: SELECT U&'d\0061t\+000061' AS U&"d\0061t\+000061";
149 ^
150 DETAIL: String constants with Unicode escapes cannot be used when standard_conforming_strings is off.
151 SELECT U&'d!0061t\+000061' UESCAPE '!' AS U&"d*0061t\+000061" UESCAPE '*';
152 ERROR: unsafe use of string constant with Unicode escapes
153 LINE 1: SELECT U&'d!0061t\+000061' UESCAPE '!' AS U&"d*0061t\+000061...
154 ^
155 DETAIL: String constants with Unicode escapes cannot be used when standard_conforming_strings is off.
156 SELECT U&' \' UESCAPE '!' AS "tricky";
157 ERROR: unsafe use of string constant with Unicode escapes
158 LINE 1: SELECT U&' \' UESCAPE '!' AS "tricky";
159 ^
160 DETAIL: String constants with Unicode escapes cannot be used when standard_conforming_strings is off.
161 SELECT 'tricky' AS U&"\" UESCAPE '!';
162 \
163 --------
164 tricky
165 (1 row)
167 SELECT U&'wrong: \061';
168 ERROR: unsafe use of string constant with Unicode escapes
169 LINE 1: SELECT U&'wrong: \061';
170 ^
171 DETAIL: String constants with Unicode escapes cannot be used when standard_conforming_strings is off.
172 SELECT U&'wrong: \+0061';
173 ERROR: unsafe use of string constant with Unicode escapes
174 LINE 1: SELECT U&'wrong: \+0061';
175 ^
176 DETAIL: String constants with Unicode escapes cannot be used when standard_conforming_strings is off.
177 SELECT U&'wrong: +0061' UESCAPE '+';
178 ERROR: unsafe use of string constant with Unicode escapes
179 LINE 1: SELECT U&'wrong: +0061' UESCAPE '+';
180 ^
181 DETAIL: String constants with Unicode escapes cannot be used when standard_conforming_strings is off.
182 RESET standard_conforming_strings;
183 -- bytea
184 SET bytea_output TO hex;
185 SELECT E'\\xDeAdBeEf'::bytea;
186 bytea
187 ------------
188 \xdeadbeef
189 (1 row)
191 SELECT E'\\x De Ad Be Ef '::bytea;
192 bytea
193 ------------
194 \xdeadbeef
195 (1 row)
197 SELECT E'\\xDeAdBeE'::bytea;
198 ERROR: invalid hexadecimal data: odd number of digits
199 LINE 1: SELECT E'\\xDeAdBeE'::bytea;
200 ^
201 SELECT E'\\xDeAdBeEx'::bytea;
202 ERROR: invalid hexadecimal digit: "x"
203 LINE 1: SELECT E'\\xDeAdBeEx'::bytea;
204 ^
205 SELECT E'\\xDe00BeEf'::bytea;
206 bytea
207 ------------
208 \xde00beef
209 (1 row)
211 SELECT E'DeAdBeEf'::bytea;
212 bytea
213 --------------------
214 \x4465416442654566
215 (1 row)
217 SELECT E'De\\000dBeEf'::bytea;
218 bytea
219 --------------------
220 \x4465006442654566
221 (1 row)
223 SELECT E'De\123dBeEf'::bytea;
224 bytea
225 --------------------
226 \x4465536442654566
227 (1 row)
229 SELECT E'De\\123dBeEf'::bytea;
230 bytea
231 --------------------
232 \x4465536442654566
233 (1 row)
235 SELECT E'De\\678dBeEf'::bytea;
236 ERROR: invalid input syntax for type bytea
237 LINE 1: SELECT E'De\\678dBeEf'::bytea;
238 ^
239 SET bytea_output TO escape;
240 SELECT E'\\xDeAdBeEf'::bytea;
241 bytea
242 ------------------
243 \336\255\276\357
244 (1 row)
246 SELECT E'\\x De Ad Be Ef '::bytea;
247 bytea
248 ------------------
249 \336\255\276\357
250 (1 row)
252 SELECT E'\\xDe00BeEf'::bytea;
253 bytea
254 ------------------
255 \336\000\276\357
256 (1 row)
258 SELECT E'DeAdBeEf'::bytea;
259 bytea
260 ----------
261 DeAdBeEf
262 (1 row)
264 SELECT E'De\\000dBeEf'::bytea;
265 bytea
266 -------------
267 De\000dBeEf
268 (1 row)
270 SELECT E'De\\123dBeEf'::bytea;
271 bytea
272 ----------
273 DeSdBeEf
274 (1 row)
276 --
277 -- test conversions between various string types
278 -- E021-10 implicit casting among the character data types
279 --
280 SELECT CAST(f1 AS text) AS "text(char)" FROM CHAR_TBL;
281 text(char)
282 ------------
283 a
284 ab
285 abcd
286 abcd
287 (4 rows)
289 SELECT CAST(f1 AS text) AS "text(varchar)" FROM VARCHAR_TBL;
290 text(varchar)
291 ---------------
292 a
293 ab
294 abcd
295 abcd
296 (4 rows)
298 SELECT CAST(name 'namefield' AS text) AS "text(name)";
299 text(name)
300 ------------
301 namefield
302 (1 row)
304 -- since this is an explicit cast, it should truncate w/o error:
305 SELECT CAST(f1 AS char(10)) AS "char(text)" FROM TEXT_TBL;
306 char(text)
307 ------------
308 doh!
309 hi de ho n
310 (2 rows)
312 -- note: implicit-cast case is tested in char.sql
313 SELECT CAST(f1 AS char(20)) AS "char(text)" FROM TEXT_TBL;
314 char(text)
315 ----------------------
316 doh!
317 hi de ho neighbor
318 (2 rows)
320 SELECT CAST(f1 AS char(10)) AS "char(varchar)" FROM VARCHAR_TBL;
321 char(varchar)
322 ---------------
323 a
324 ab
325 abcd
326 abcd
327 (4 rows)
329 SELECT CAST(name 'namefield' AS char(10)) AS "char(name)";
330 char(name)
331 ------------
332 namefield
333 (1 row)
335 SELECT CAST(f1 AS varchar) AS "varchar(text)" FROM TEXT_TBL;
336 varchar(text)
337 -------------------
338 doh!
339 hi de ho neighbor
340 (2 rows)
342 SELECT CAST(f1 AS varchar) AS "varchar(char)" FROM CHAR_TBL;
343 varchar(char)
344 ---------------
345 a
346 ab
347 abcd
348 abcd
349 (4 rows)
351 SELECT CAST(name 'namefield' AS varchar) AS "varchar(name)";
352 varchar(name)
353 ---------------
354 namefield
355 (1 row)
357 --
358 -- test SQL string functions
359 -- E### and T### are feature reference numbers from SQL99
360 --
361 -- E021-09 trim function
362 SELECT TRIM(BOTH FROM ' bunch o blanks ') = 'bunch o blanks' AS "bunch o blanks";
363 bunch o blanks
364 ----------------
365 t
366 (1 row)
368 SELECT TRIM(LEADING FROM ' bunch o blanks ') = 'bunch o blanks ' AS "bunch o blanks ";
369 bunch o blanks
370 ------------------
371 t
372 (1 row)
374 SELECT TRIM(TRAILING FROM ' bunch o blanks ') = ' bunch o blanks' AS " bunch o blanks";
375 bunch o blanks
376 ------------------
377 t
378 (1 row)
380 SELECT TRIM(BOTH 'x' FROM 'xxxxxsome Xsxxxxx') = 'some Xs' AS "some Xs";
381 some Xs
382 ---------
383 t
384 (1 row)
386 -- E021-06 substring expression
387 SELECT SUBSTRING('1234567890' FROM 3) = '34567890' AS "34567890";
388 34567890
389 ----------
390 t
391 (1 row)
393 SELECT SUBSTRING('1234567890' FROM 4 FOR 3) = '456' AS "456";
394 456
395 -----
396 t
397 (1 row)
399 -- test overflow cases
400 SELECT SUBSTRING('string' FROM 2 FOR 2147483646) AS "tring";
401 tring
402 -------
403 tring
404 (1 row)
406 SELECT SUBSTRING('string' FROM -10 FOR 2147483646) AS "string";
407 string
408 --------
409 string
410 (1 row)
412 SELECT SUBSTRING('string' FROM -10 FOR -2147483646) AS "error";
413 ERROR: negative substring length not allowed
414 -- T581 regular expression substring (with SQL's bizarre regexp syntax)
415 SELECT SUBSTRING('abcdefg' SIMILAR 'a#"(b_d)#"%' ESCAPE '#') AS "bcd";
416 bcd
417 -----
418 bcd
419 (1 row)
421 -- obsolete SQL99 syntax
422 SELECT SUBSTRING('abcdefg' FROM 'a#"(b_d)#"%' FOR '#') AS "bcd";
423 bcd
424 -----
425 bcd
426 (1 row)
428 -- No match should return NULL
429 SELECT SUBSTRING('abcdefg' SIMILAR '#"(b_d)#"%' ESCAPE '#') IS NULL AS "True";
430 True
431 ------
432 t
433 (1 row)
435 -- Null inputs should return NULL
436 SELECT SUBSTRING('abcdefg' SIMILAR '%' ESCAPE NULL) IS NULL AS "True";
437 True
438 ------
439 t
440 (1 row)
442 SELECT SUBSTRING(NULL SIMILAR '%' ESCAPE '#') IS NULL AS "True";
443 True
444 ------
445 t
446 (1 row)
448 SELECT SUBSTRING('abcdefg' SIMILAR NULL ESCAPE '#') IS NULL AS "True";
449 True
450 ------
451 t
452 (1 row)
454 -- The first and last parts should act non-greedy
455 SELECT SUBSTRING('abcdefg' SIMILAR 'a#"%#"g' ESCAPE '#') AS "bcdef";
456 bcdef
457 -------
458 bcdef
459 (1 row)
461 SELECT SUBSTRING('abcdefg' SIMILAR 'a*#"%#"g*' ESCAPE '#') AS "abcdefg";
462 abcdefg
463 ---------
464 abcdefg
465 (1 row)
467 -- Vertical bar in any part affects only that part
468 SELECT SUBSTRING('abcdefg' SIMILAR 'a|b#"%#"g' ESCAPE '#') AS "bcdef";
469 bcdef
470 -------
471 bcdef
472 (1 row)
474 SELECT SUBSTRING('abcdefg' SIMILAR 'a#"%#"x|g' ESCAPE '#') AS "bcdef";
475 bcdef
476 -------
477 bcdef
478 (1 row)
480 SELECT SUBSTRING('abcdefg' SIMILAR 'a#"%|ab#"g' ESCAPE '#') AS "bcdef";
481 bcdef
482 -------
483 bcdef
484 (1 row)
486 -- Can't have more than two part separators
487 SELECT SUBSTRING('abcdefg' SIMILAR 'a*#"%#"g*#"x' ESCAPE '#') AS "error";
488 ERROR: SQL regular expression may not contain more than two escape-double-quote separators
489 CONTEXT: SQL function "substring" statement 1
490 -- Postgres extension: with 0 or 1 separator, assume parts 1 and 3 are empty
491 SELECT SUBSTRING('abcdefg' SIMILAR 'a#"%g' ESCAPE '#') AS "bcdefg";
492 bcdefg
493 --------
494 bcdefg
495 (1 row)
497 SELECT SUBSTRING('abcdefg' SIMILAR 'a%g' ESCAPE '#') AS "abcdefg";
498 abcdefg
499 ---------
500 abcdefg
501 (1 row)
503 -- substring() with just two arguments is not allowed by SQL spec;
504 -- we accept it, but we interpret the pattern as a POSIX regexp not SQL
505 SELECT SUBSTRING('abcdefg' FROM 'c.e') AS "cde";
506 cde
507 -----
508 cde
509 (1 row)
511 -- With a parenthesized subexpression, return only what matches the subexpr
512 SELECT SUBSTRING('abcdefg' FROM 'b(.*)f') AS "cde";
513 cde
514 -----
515 cde
516 (1 row)
518 -- Check case where we have a match, but not a subexpression match
519 SELECT SUBSTRING('foo' FROM 'foo(bar)?') IS NULL AS t;
520 t
521 ---
522 t
523 (1 row)
525 -- Check behavior of SIMILAR TO, which uses largely the same regexp variant
526 SELECT 'abcdefg' SIMILAR TO '_bcd%' AS true;
527 true
528 ------
529 t
530 (1 row)
532 SELECT 'abcdefg' SIMILAR TO 'bcd%' AS false;
533 false
534 -------
535 f
536 (1 row)
538 SELECT 'abcdefg' SIMILAR TO '_bcd#%' ESCAPE '#' AS false;
539 false
540 -------
541 f
542 (1 row)
544 SELECT 'abcd%' SIMILAR TO '_bcd#%' ESCAPE '#' AS true;
545 true
546 ------
547 t
548 (1 row)
550 -- Postgres uses '\' as the default escape character, which is not per spec
551 SELECT 'abcdefg' SIMILAR TO '_bcd\%' AS false;
552 false
553 -------
554 f
555 (1 row)
557 -- and an empty string to mean "no escape", which is also not per spec
558 SELECT 'abcd\efg' SIMILAR TO '_bcd\%' ESCAPE '' AS true;
559 true
560 ------
561 t
562 (1 row)
564 -- these behaviors are per spec, though:
565 SELECT 'abcdefg' SIMILAR TO '_bcd%' ESCAPE NULL AS null;
566 null
567 ------
569 (1 row)
571 SELECT 'abcdefg' SIMILAR TO '_bcd#%' ESCAPE '##' AS error;
572 ERROR: invalid escape string
573 HINT: Escape string must be empty or one character.
574 -- Test backslash escapes in regexp_replace's replacement string
575 SELECT regexp_replace('1112223333', E'(\\d{3})(\\d{3})(\\d{4})', E'(\\1) \\2-\\3');
576 regexp_replace
577 ----------------
578 (111) 222-3333
579 (1 row)
581 SELECT regexp_replace('foobarrbazz', E'(.)\\1', E'X\\&Y', 'g');
582 regexp_replace
583 -------------------
584 fXooYbaXrrYbaXzzY
585 (1 row)
587 SELECT regexp_replace('foobarrbazz', E'(.)\\1', E'X\\\\Y', 'g');
588 regexp_replace
589 ----------------
590 fX\YbaX\YbaX\Y
591 (1 row)
593 -- not an error, though perhaps it should be:
594 SELECT regexp_replace('foobarrbazz', E'(.)\\1', E'X\\Y\\1Z\\');
595 regexp_replace
596 -----------------
597 fX\YoZ\barrbazz
598 (1 row)
600 SELECT regexp_replace('AAA BBB CCC ', E'\\s+', ' ', 'g');
601 regexp_replace
602 ----------------
603 AAA BBB CCC
604 (1 row)
606 SELECT regexp_replace('AAA', '^|$', 'Z', 'g');
607 regexp_replace
608 ----------------
609 ZAAAZ
610 (1 row)
612 SELECT regexp_replace('AAA aaa', 'A+', 'Z', 'gi');
613 regexp_replace
614 ----------------
615 Z Z
616 (1 row)
618 -- invalid regexp option
619 SELECT regexp_replace('AAA aaa', 'A+', 'Z', 'z');
620 ERROR: invalid regular expression option: "z"
621 -- extended regexp_replace tests
622 SELECT regexp_replace('A PostgreSQL function', 'A|e|i|o|u', 'X', 1);
623 regexp_replace
624 -----------------------
625 X PostgreSQL function
626 (1 row)
628 SELECT regexp_replace('A PostgreSQL function', 'A|e|i|o|u', 'X', 1, 2);
629 regexp_replace
630 -----------------------
631 A PXstgreSQL function
632 (1 row)
634 SELECT regexp_replace('A PostgreSQL function', 'a|e|i|o|u', 'X', 1, 0, 'i');
635 regexp_replace
636 -----------------------
637 X PXstgrXSQL fXnctXXn
638 (1 row)
640 SELECT regexp_replace('A PostgreSQL function', 'a|e|i|o|u', 'X', 1, 1, 'i');
641 regexp_replace
642 -----------------------
643 X PostgreSQL function
644 (1 row)
646 SELECT regexp_replace('A PostgreSQL function', 'a|e|i|o|u', 'X', 1, 2, 'i');
647 regexp_replace
648 -----------------------
649 A PXstgreSQL function
650 (1 row)
652 SELECT regexp_replace('A PostgreSQL function', 'a|e|i|o|u', 'X', 1, 3, 'i');
653 regexp_replace
654 -----------------------
655 A PostgrXSQL function
656 (1 row)
658 SELECT regexp_replace('A PostgreSQL function', 'a|e|i|o|u', 'X', 1, 9, 'i');
659 regexp_replace
660 -----------------------
661 A PostgreSQL function
662 (1 row)
664 SELECT regexp_replace('A PostgreSQL function', 'A|e|i|o|u', 'X', 7, 0, 'i');
665 regexp_replace
666 -----------------------
667 A PostgrXSQL fXnctXXn
668 (1 row)
670 -- 'g' flag should be ignored when N is specified
671 SELECT regexp_replace('A PostgreSQL function', 'a|e|i|o|u', 'X', 1, 1, 'g');
672 regexp_replace
673 -----------------------
674 A PXstgreSQL function
675 (1 row)
677 -- errors
678 SELECT regexp_replace('A PostgreSQL function', 'a|e|i|o|u', 'X', -1, 0, 'i');
679 ERROR: invalid value for parameter "start": -1
680 SELECT regexp_replace('A PostgreSQL function', 'a|e|i|o|u', 'X', 1, -1, 'i');
681 ERROR: invalid value for parameter "n": -1
682 -- erroneous invocation of non-extended form
683 SELECT regexp_replace('A PostgreSQL function', 'a|e|i|o|u', 'X', '1');
684 ERROR: invalid regular expression option: "1"
685 HINT: If you meant to use regexp_replace() with a start parameter, cast the fourth argument to integer explicitly.
686 -- regexp_count tests
687 SELECT regexp_count('123123123123123', '(12)3');
688 regexp_count
689 --------------
690 5
691 (1 row)
693 SELECT regexp_count('123123123123', '123', 1);
694 regexp_count
695 --------------
696 4
697 (1 row)
699 SELECT regexp_count('123123123123', '123', 3);
700 regexp_count
701 --------------
702 3
703 (1 row)
705 SELECT regexp_count('123123123123', '123', 33);
706 regexp_count
707 --------------
708 0
709 (1 row)
711 SELECT regexp_count('ABCABCABCABC', 'Abc', 1, '');
712 regexp_count
713 --------------
714 0
715 (1 row)
717 SELECT regexp_count('ABCABCABCABC', 'Abc', 1, 'i');
718 regexp_count
719 --------------
720 4
721 (1 row)
723 -- errors
724 SELECT regexp_count('123123123123', '123', 0);
725 ERROR: invalid value for parameter "start": 0
726 SELECT regexp_count('123123123123', '123', -3);
727 ERROR: invalid value for parameter "start": -3
728 -- regexp_like tests
729 SELECT regexp_like('Steven', '^Ste(v|ph)en$');
730 regexp_like
731 -------------
732 t
733 (1 row)
735 SELECT regexp_like('a'||CHR(10)||'d', 'a.d', 'n');
736 regexp_like
737 -------------
738 f
739 (1 row)
741 SELECT regexp_like('a'||CHR(10)||'d', 'a.d', 's');
742 regexp_like
743 -------------
744 t
745 (1 row)
747 SELECT regexp_like('abc', ' a . c ', 'x');
748 regexp_like
749 -------------
750 t
751 (1 row)
753 SELECT regexp_like('abc', 'a.c', 'g'); -- error
754 ERROR: regexp_like() does not support the "global" option
755 -- regexp_instr tests
756 SELECT regexp_instr('abcdefghi', 'd.f');
757 regexp_instr
758 --------------
759 4
760 (1 row)
762 SELECT regexp_instr('abcdefghi', 'd.q');
763 regexp_instr
764 --------------
765 0
766 (1 row)
768 SELECT regexp_instr('abcabcabc', 'a.c');
769 regexp_instr
770 --------------
771 1
772 (1 row)
774 SELECT regexp_instr('abcabcabc', 'a.c', 2);
775 regexp_instr
776 --------------
777 4
778 (1 row)
780 SELECT regexp_instr('abcabcabc', 'a.c', 1, 3);
781 regexp_instr
782 --------------
783 7
784 (1 row)
786 SELECT regexp_instr('abcabcabc', 'a.c', 1, 4);
787 regexp_instr
788 --------------
789 0
790 (1 row)
792 SELECT regexp_instr('abcabcabc', 'A.C', 1, 2, 0, 'i');
793 regexp_instr
794 --------------
795 4
796 (1 row)
798 SELECT regexp_instr('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 0);
799 regexp_instr
800 --------------
801 1
802 (1 row)
804 SELECT regexp_instr('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 1);
805 regexp_instr
806 --------------
807 1
808 (1 row)
810 SELECT regexp_instr('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 2);
811 regexp_instr
812 --------------
813 4
814 (1 row)
816 SELECT regexp_instr('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 3);
817 regexp_instr
818 --------------
819 5
820 (1 row)
822 SELECT regexp_instr('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 4);
823 regexp_instr
824 --------------
825 7
826 (1 row)
828 SELECT regexp_instr('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 5);
829 regexp_instr
830 --------------
831 0
832 (1 row)
834 SELECT regexp_instr('1234567890', '(123)(4(56)(78))', 1, 1, 1, 'i', 0);
835 regexp_instr
836 --------------
837 9
838 (1 row)
840 SELECT regexp_instr('1234567890', '(123)(4(56)(78))', 1, 1, 1, 'i', 1);
841 regexp_instr
842 --------------
843 4
844 (1 row)
846 SELECT regexp_instr('1234567890', '(123)(4(56)(78))', 1, 1, 1, 'i', 2);
847 regexp_instr
848 --------------
849 9
850 (1 row)
852 SELECT regexp_instr('1234567890', '(123)(4(56)(78))', 1, 1, 1, 'i', 3);
853 regexp_instr
854 --------------
855 7
856 (1 row)
858 SELECT regexp_instr('1234567890', '(123)(4(56)(78))', 1, 1, 1, 'i', 4);
859 regexp_instr
860 --------------
861 9
862 (1 row)
864 SELECT regexp_instr('1234567890', '(123)(4(56)(78))', 1, 1, 1, 'i', 5);
865 regexp_instr
866 --------------
867 0
868 (1 row)
870 -- Check case where we have a match, but not a subexpression match
871 SELECT regexp_instr('foo', 'foo(bar)?', 1, 1, 0, '', 1);
872 regexp_instr
873 --------------
874 0
875 (1 row)
877 -- errors
878 SELECT regexp_instr('abcabcabc', 'a.c', 0, 1);
879 ERROR: invalid value for parameter "start": 0
880 SELECT regexp_instr('abcabcabc', 'a.c', 1, 0);
881 ERROR: invalid value for parameter "n": 0
882 SELECT regexp_instr('abcabcabc', 'a.c', 1, 1, -1);
883 ERROR: invalid value for parameter "endoption": -1
884 SELECT regexp_instr('abcabcabc', 'a.c', 1, 1, 2);
885 ERROR: invalid value for parameter "endoption": 2
886 SELECT regexp_instr('abcabcabc', 'a.c', 1, 1, 0, 'g');
887 ERROR: regexp_instr() does not support the "global" option
888 SELECT regexp_instr('abcabcabc', 'a.c', 1, 1, 0, '', -1);
889 ERROR: invalid value for parameter "subexpr": -1
890 -- regexp_substr tests
891 SELECT regexp_substr('abcdefghi', 'd.f');
892 regexp_substr
893 ---------------
894 def
895 (1 row)
897 SELECT regexp_substr('abcdefghi', 'd.q') IS NULL AS t;
898 t
899 ---
900 t
901 (1 row)
903 SELECT regexp_substr('abcabcabc', 'a.c');
904 regexp_substr
905 ---------------
906 abc
907 (1 row)
909 SELECT regexp_substr('abcabcabc', 'a.c', 2);
910 regexp_substr
911 ---------------
912 abc
913 (1 row)
915 SELECT regexp_substr('abcabcabc', 'a.c', 1, 3);
916 regexp_substr
917 ---------------
918 abc
919 (1 row)
921 SELECT regexp_substr('abcabcabc', 'a.c', 1, 4) IS NULL AS t;
922 t
923 ---
924 t
925 (1 row)
927 SELECT regexp_substr('abcabcabc', 'A.C', 1, 2, 'i');
928 regexp_substr
929 ---------------
930 abc
931 (1 row)
933 SELECT regexp_substr('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 0);
934 regexp_substr
935 ---------------
936 12345678
937 (1 row)
939 SELECT regexp_substr('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 1);
940 regexp_substr
941 ---------------
942 123
943 (1 row)
945 SELECT regexp_substr('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 2);
946 regexp_substr
947 ---------------
948 45678
949 (1 row)
951 SELECT regexp_substr('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 3);
952 regexp_substr
953 ---------------
954 56
955 (1 row)
957 SELECT regexp_substr('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 4);
958 regexp_substr
959 ---------------
960 78
961 (1 row)
963 SELECT regexp_substr('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 5) IS NULL AS t;
964 t
965 ---
966 t
967 (1 row)
969 -- Check case where we have a match, but not a subexpression match
970 SELECT regexp_substr('foo', 'foo(bar)?', 1, 1, '', 1) IS NULL AS t;
971 t
972 ---
973 t
974 (1 row)
976 -- errors
977 SELECT regexp_substr('abcabcabc', 'a.c', 0, 1);
978 ERROR: invalid value for parameter "start": 0
979 SELECT regexp_substr('abcabcabc', 'a.c', 1, 0);
980 ERROR: invalid value for parameter "n": 0
981 SELECT regexp_substr('abcabcabc', 'a.c', 1, 1, 'g');
982 ERROR: regexp_substr() does not support the "global" option
983 SELECT regexp_substr('abcabcabc', 'a.c', 1, 1, '', -1);
984 ERROR: invalid value for parameter "subexpr": -1
985 -- set so we can tell NULL from empty string
986 \pset null '\\N'
987 -- return all matches from regexp
988 SELECT regexp_matches('foobarbequebaz', $re$(bar)(beque)$re$);
989 regexp_matches
990 ----------------
991 {bar,beque}
992 (1 row)
994 -- test case insensitive
995 SELECT regexp_matches('foObARbEqUEbAz', $re$(bar)(beque)$re$, 'i');
996 regexp_matches
997 ----------------
998 {bAR,bEqUE}
999 (1 row)
1001 -- global option - more than one match
1002 SELECT regexp_matches('foobarbequebazilbarfbonk', $re$(b[^b]+)(b[^b]+)$re$, 'g');
1003 regexp_matches
1004 ----------------
1005 {bar,beque}
1006 {bazil,barf}
1007 (2 rows)
1009 -- empty capture group (matched empty string)
1010 SELECT regexp_matches('foobarbequebaz', $re$(bar)(.*)(beque)$re$);
1011 regexp_matches
1012 ----------------
1013 {bar,"",beque}
1014 (1 row)
1016 -- no match
1017 SELECT regexp_matches('foobarbequebaz', $re$(bar)(.+)(beque)$re$);
1018 regexp_matches
1019 ----------------
1020 (0 rows)
1022 -- optional capture group did not match, null entry in array
1023 SELECT regexp_matches('foobarbequebaz', $re$(bar)(.+)?(beque)$re$);
1024 regexp_matches
1025 ------------------
1026 {bar,NULL,beque}
1027 (1 row)
1029 -- no capture groups
1030 SELECT regexp_matches('foobarbequebaz', $re$barbeque$re$);
1031 regexp_matches
1032 ----------------
1033 {barbeque}
1034 (1 row)
1036 -- start/end-of-line matches are of zero length
1037 SELECT regexp_matches('foo' || chr(10) || 'bar' || chr(10) || 'bequq' || chr(10) || 'baz', '^', 'mg');
1038 regexp_matches
1039 ----------------
1040 {""}
1041 {""}
1042 {""}
1043 {""}
1044 (4 rows)
1046 SELECT regexp_matches('foo' || chr(10) || 'bar' || chr(10) || 'bequq' || chr(10) || 'baz', '$', 'mg');
1047 regexp_matches
1048 ----------------
1049 {""}
1050 {""}
1051 {""}
1052 {""}
1053 (4 rows)
1055 SELECT regexp_matches('1' || chr(10) || '2' || chr(10) || '3' || chr(10) || '4' || chr(10), '^.?', 'mg');
1056 regexp_matches
1057 ----------------
1058 {1}
1059 {2}
1060 {3}
1061 {4}
1062 {""}
1063 (5 rows)
1065 SELECT regexp_matches(chr(10) || '1' || chr(10) || '2' || chr(10) || '3' || chr(10) || '4' || chr(10), '.?$', 'mg');
1066 regexp_matches
1067 ----------------
1068 {""}
1069 {1}
1070 {""}
1071 {2}
1072 {""}
1073 {3}
1074 {""}
1075 {4}
1076 {""}
1077 {""}
1078 (10 rows)
1080 SELECT regexp_matches(chr(10) || '1' || chr(10) || '2' || chr(10) || '3' || chr(10) || '4', '.?$', 'mg');
1081 regexp_matches
1082 ----------------
1083 {""}
1084 {1}
1085 {""}
1086 {2}
1087 {""}
1088 {3}
1089 {""}
1090 {4}
1091 {""}
1092 (9 rows)
1094 -- give me errors
1095 SELECT regexp_matches('foobarbequebaz', $re$(bar)(beque)$re$, 'gz');
1096 ERROR: invalid regular expression option: "z"
1097 SELECT regexp_matches('foobarbequebaz', $re$(barbeque$re$);
1098 ERROR: invalid regular expression: parentheses () not balanced
1099 SELECT regexp_matches('foobarbequebaz', $re$(bar)(beque){2,1}$re$);
1100 ERROR: invalid regular expression: invalid repetition count(s)
1101 -- split string on regexp
1102 SELECT foo, length(foo) FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', $re$\s+$re$) AS foo;
1103 foo | length
1104 -------+--------
1105 the | 3
1106 quick | 5
1107 brown | 5
1108 fox | 3
1109 jumps | 5
1110 over | 4
1111 the | 3
1112 lazy | 4
1113 dog | 3
1114 (9 rows)
1116 SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', $re$\s+$re$);
1117 regexp_split_to_array
1118 -----------------------------------------------
1119 {the,quick,brown,fox,jumps,over,the,lazy,dog}
1120 (1 row)
1122 SELECT foo, length(foo) FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', $re$\s*$re$) AS foo;
1123 foo | length
1124 -----+--------
1125 t | 1
1126 h | 1
1127 e | 1
1128 q | 1
1129 u | 1
1130 i | 1
1131 c | 1
1132 k | 1
1133 b | 1
1134 r | 1
1135 o | 1
1136 w | 1
1137 n | 1
1138 f | 1
1139 o | 1
1140 x | 1
1141 j | 1
1142 u | 1
1143 m | 1
1144 p | 1
1145 s | 1
1146 o | 1
1147 v | 1
1148 e | 1
1149 r | 1
1150 t | 1
1151 h | 1
1152 e | 1
1153 l | 1
1154 a | 1
1155 z | 1
1156 y | 1
1157 d | 1
1158 o | 1
1159 g | 1
1160 (35 rows)
1162 SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', $re$\s*$re$);
1163 regexp_split_to_array
1164 -------------------------------------------------------------------------
1165 {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}
1166 (1 row)
1168 SELECT foo, length(foo) FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', '') AS foo;
1169 foo | length
1170 -----+--------
1171 t | 1
1172 h | 1
1173 e | 1
1174 | 1
1175 q | 1
1176 u | 1
1177 i | 1
1178 c | 1
1179 k | 1
1180 | 1
1181 b | 1
1182 r | 1
1183 o | 1
1184 w | 1
1185 n | 1
1186 | 1
1187 f | 1
1188 o | 1
1189 x | 1
1190 | 1
1191 j | 1
1192 u | 1
1193 m | 1
1194 p | 1
1195 s | 1
1196 | 1
1197 o | 1
1198 v | 1
1199 e | 1
1200 r | 1
1201 | 1
1202 t | 1
1203 h | 1
1204 e | 1
1205 | 1
1206 l | 1
1207 a | 1
1208 z | 1
1209 y | 1
1210 | 1
1211 d | 1
1212 o | 1
1213 g | 1
1214 (43 rows)
1216 SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', '');
1217 regexp_split_to_array
1218 ---------------------------------------------------------------------------------------------------------
1219 {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}
1220 (1 row)
1222 -- case insensitive
1223 SELECT foo, length(foo) FROM regexp_split_to_table('thE QUick bROWn FOx jUMPs ovEr The lazy dOG', 'e', 'i') AS foo;
1224 foo | length
1225 ---------------------------+--------
1226 th | 2
1227 QUick bROWn FOx jUMPs ov | 25
1228 r Th | 4
1229 lazy dOG | 9
1230 (4 rows)
1232 SELECT regexp_split_to_array('thE QUick bROWn FOx jUMPs ovEr The lazy dOG', 'e', 'i');
1233 regexp_split_to_array
1234 -----------------------------------------------------
1235 {th," QUick bROWn FOx jUMPs ov","r Th"," lazy dOG"}
1236 (1 row)
1238 -- no match of pattern
1239 SELECT foo, length(foo) FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', 'nomatch') AS foo;
1240 foo | length
1241 ---------------------------------------------+--------
1242 the quick brown fox jumps over the lazy dog | 43
1243 (1 row)
1245 SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', 'nomatch');
1246 regexp_split_to_array
1247 -------------------------------------------------
1248 {"the quick brown fox jumps over the lazy dog"}
1249 (1 row)
1251 -- some corner cases
1252 SELECT regexp_split_to_array('123456','1');
1253 regexp_split_to_array
1254 -----------------------
1255 {"",23456}
1256 (1 row)
1258 SELECT regexp_split_to_array('123456','6');
1259 regexp_split_to_array
1260 -----------------------
1261 {12345,""}
1262 (1 row)
1264 SELECT regexp_split_to_array('123456','.');
1265 regexp_split_to_array
1266 ------------------------
1267 {"","","","","","",""}
1268 (1 row)
1270 SELECT regexp_split_to_array('123456','');
1271 regexp_split_to_array
1272 -----------------------
1273 {1,2,3,4,5,6}
1274 (1 row)
1276 SELECT regexp_split_to_array('123456','(?:)');
1277 regexp_split_to_array
1278 -----------------------
1279 {1,2,3,4,5,6}
1280 (1 row)
1282 SELECT regexp_split_to_array('1','');
1283 regexp_split_to_array
1284 -----------------------
1285 {1}
1286 (1 row)
1288 -- errors
1289 SELECT foo, length(foo) FROM regexp_split_to_table('thE QUick bROWn FOx jUMPs ovEr The lazy dOG', 'e', 'zippy') AS foo;
1290 ERROR: invalid regular expression option: "z"
1291 SELECT regexp_split_to_array('thE QUick bROWn FOx jUMPs ovEr The lazy dOG', 'e', 'iz');
1292 ERROR: invalid regular expression option: "z"
1293 -- global option meaningless for regexp_split
1294 SELECT foo, length(foo) FROM regexp_split_to_table('thE QUick bROWn FOx jUMPs ovEr The lazy dOG', 'e', 'g') AS foo;
1295 ERROR: regexp_split_to_table() does not support the "global" option
1296 SELECT regexp_split_to_array('thE QUick bROWn FOx jUMPs ovEr The lazy dOG', 'e', 'g');
1297 ERROR: regexp_split_to_array() does not support the "global" option
1298 -- change NULL-display back
1299 \pset null ''
1300 -- E021-11 position expression
1301 SELECT POSITION('4' IN '1234567890') = '4' AS "4";
1302 4
1303 ---
1304 t
1305 (1 row)
1307 SELECT POSITION('5' IN '1234567890') = '5' AS "5";
1308 5
1309 ---
1310 t
1311 (1 row)
1313 -- T312 character overlay function
1314 SELECT OVERLAY('abcdef' PLACING '45' FROM 4) AS "abc45f";
1315 abc45f
1316 --------
1317 abc45f
1318 (1 row)
1320 SELECT OVERLAY('yabadoo' PLACING 'daba' FROM 5) AS "yabadaba";
1321 yabadaba
1322 ----------
1323 yabadaba
1324 (1 row)
1326 SELECT OVERLAY('yabadoo' PLACING 'daba' FROM 5 FOR 0) AS "yabadabadoo";
1327 yabadabadoo
1328 -------------
1329 yabadabadoo
1330 (1 row)
1332 SELECT OVERLAY('babosa' PLACING 'ubb' FROM 2 FOR 4) AS "bubba";
1333 bubba
1334 -------
1335 bubba
1336 (1 row)
1338 --
1339 -- test LIKE
1340 -- Be sure to form every test as a LIKE/NOT LIKE pair.
1341 --
1342 -- simplest examples
1343 -- E061-04 like predicate
1344 SELECT 'hawkeye' LIKE 'h%' AS "true";
1345 true
1346 ------
1347 t
1348 (1 row)
1350 SELECT 'hawkeye' NOT LIKE 'h%' AS "false";
1351 false
1352 -------
1353 f
1354 (1 row)
1356 SELECT 'hawkeye' LIKE 'H%' AS "false";
1357 false
1358 -------
1359 f
1360 (1 row)
1362 SELECT 'hawkeye' NOT LIKE 'H%' AS "true";
1363 true
1364 ------
1365 t
1366 (1 row)
1368 SELECT 'hawkeye' LIKE 'indio%' AS "false";
1369 false
1370 -------
1371 f
1372 (1 row)
1374 SELECT 'hawkeye' NOT LIKE 'indio%' AS "true";
1375 true
1376 ------
1377 t
1378 (1 row)
1380 SELECT 'hawkeye' LIKE 'h%eye' AS "true";
1381 true
1382 ------
1383 t
1384 (1 row)
1386 SELECT 'hawkeye' NOT LIKE 'h%eye' AS "false";
1387 false
1388 -------
1389 f
1390 (1 row)
1392 SELECT 'indio' LIKE '_ndio' AS "true";
1393 true
1394 ------
1395 t
1396 (1 row)
1398 SELECT 'indio' NOT LIKE '_ndio' AS "false";
1399 false
1400 -------
1401 f
1402 (1 row)
1404 SELECT 'indio' LIKE 'in__o' AS "true";
1405 true
1406 ------
1407 t
1408 (1 row)
1410 SELECT 'indio' NOT LIKE 'in__o' AS "false";
1411 false
1412 -------
1413 f
1414 (1 row)
1416 SELECT 'indio' LIKE 'in_o' AS "false";
1417 false
1418 -------
1419 f
1420 (1 row)
1422 SELECT 'indio' NOT LIKE 'in_o' AS "true";
1423 true
1424 ------
1425 t
1426 (1 row)
1428 SELECT 'abc'::name LIKE '_b_' AS "true";
1429 true
1430 ------
1431 t
1432 (1 row)
1434 SELECT 'abc'::name NOT LIKE '_b_' AS "false";
1435 false
1436 -------
1437 f
1438 (1 row)
1440 SELECT 'abc'::bytea LIKE '_b_'::bytea AS "true";
1441 true
1442 ------
1443 t
1444 (1 row)
1446 SELECT 'abc'::bytea NOT LIKE '_b_'::bytea AS "false";
1447 false
1448 -------
1449 f
1450 (1 row)
1452 -- unused escape character
1453 SELECT 'hawkeye' LIKE 'h%' ESCAPE '#' AS "true";
1454 true
1455 ------
1456 t
1457 (1 row)
1459 SELECT 'hawkeye' NOT LIKE 'h%' ESCAPE '#' AS "false";
1460 false
1461 -------
1462 f
1463 (1 row)
1465 SELECT 'indio' LIKE 'ind_o' ESCAPE '$' AS "true";
1466 true
1467 ------
1468 t
1469 (1 row)
1471 SELECT 'indio' NOT LIKE 'ind_o' ESCAPE '$' AS "false";
1472 false
1473 -------
1474 f
1475 (1 row)
1477 -- escape character
1478 -- E061-05 like predicate with escape clause
1479 SELECT 'h%' LIKE 'h#%' ESCAPE '#' AS "true";
1480 true
1481 ------
1482 t
1483 (1 row)
1485 SELECT 'h%' NOT LIKE 'h#%' ESCAPE '#' AS "false";
1486 false
1487 -------
1488 f
1489 (1 row)
1491 SELECT 'h%wkeye' LIKE 'h#%' ESCAPE '#' AS "false";
1492 false
1493 -------
1494 f
1495 (1 row)
1497 SELECT 'h%wkeye' NOT LIKE 'h#%' ESCAPE '#' AS "true";
1498 true
1499 ------
1500 t
1501 (1 row)
1503 SELECT 'h%wkeye' LIKE 'h#%%' ESCAPE '#' AS "true";
1504 true
1505 ------
1506 t
1507 (1 row)
1509 SELECT 'h%wkeye' NOT LIKE 'h#%%' ESCAPE '#' AS "false";
1510 false
1511 -------
1512 f
1513 (1 row)
1515 SELECT 'h%awkeye' LIKE 'h#%a%k%e' ESCAPE '#' AS "true";
1516 true
1517 ------
1518 t
1519 (1 row)
1521 SELECT 'h%awkeye' NOT LIKE 'h#%a%k%e' ESCAPE '#' AS "false";
1522 false
1523 -------
1524 f
1525 (1 row)
1527 SELECT 'indio' LIKE '_ndio' ESCAPE '$' AS "true";
1528 true
1529 ------
1530 t
1531 (1 row)
1533 SELECT 'indio' NOT LIKE '_ndio' ESCAPE '$' AS "false";
1534 false
1535 -------
1536 f
1537 (1 row)
1539 SELECT 'i_dio' LIKE 'i$_d_o' ESCAPE '$' AS "true";
1540 true
1541 ------
1542 t
1543 (1 row)
1545 SELECT 'i_dio' NOT LIKE 'i$_d_o' ESCAPE '$' AS "false";
1546 false
1547 -------
1548 f
1549 (1 row)
1551 SELECT 'i_dio' LIKE 'i$_nd_o' ESCAPE '$' AS "false";
1552 false
1553 -------
1554 f
1555 (1 row)
1557 SELECT 'i_dio' NOT LIKE 'i$_nd_o' ESCAPE '$' AS "true";
1558 true
1559 ------
1560 t
1561 (1 row)
1563 SELECT 'i_dio' LIKE 'i$_d%o' ESCAPE '$' AS "true";
1564 true
1565 ------
1566 t
1567 (1 row)
1569 SELECT 'i_dio' NOT LIKE 'i$_d%o' ESCAPE '$' AS "false";
1570 false
1571 -------
1572 f
1573 (1 row)
1575 SELECT 'a_c'::bytea LIKE 'a$__'::bytea ESCAPE '$'::bytea AS "true";
1576 true
1577 ------
1578 t
1579 (1 row)
1581 SELECT 'a_c'::bytea NOT LIKE 'a$__'::bytea ESCAPE '$'::bytea AS "false";
1582 false
1583 -------
1584 f
1585 (1 row)
1587 -- escape character same as pattern character
1588 SELECT 'maca' LIKE 'm%aca' ESCAPE '%' AS "true";
1589 true
1590 ------
1591 t
1592 (1 row)
1594 SELECT 'maca' NOT LIKE 'm%aca' ESCAPE '%' AS "false";
1595 false
1596 -------
1597 f
1598 (1 row)
1600 SELECT 'ma%a' LIKE 'm%a%%a' ESCAPE '%' AS "true";
1601 true
1602 ------
1603 t
1604 (1 row)
1606 SELECT 'ma%a' NOT LIKE 'm%a%%a' ESCAPE '%' AS "false";
1607 false
1608 -------
1609 f
1610 (1 row)
1612 SELECT 'bear' LIKE 'b_ear' ESCAPE '_' AS "true";
1613 true
1614 ------
1615 t
1616 (1 row)
1618 SELECT 'bear' NOT LIKE 'b_ear' ESCAPE '_' AS "false";
1619 false
1620 -------
1621 f
1622 (1 row)
1624 SELECT 'be_r' LIKE 'b_e__r' ESCAPE '_' AS "true";
1625 true
1626 ------
1627 t
1628 (1 row)
1630 SELECT 'be_r' NOT LIKE 'b_e__r' ESCAPE '_' AS "false";
1631 false
1632 -------
1633 f
1634 (1 row)
1636 SELECT 'be_r' LIKE '__e__r' ESCAPE '_' AS "false";
1637 false
1638 -------
1639 f
1640 (1 row)
1642 SELECT 'be_r' NOT LIKE '__e__r' ESCAPE '_' AS "true";
1643 true
1644 ------
1645 t
1646 (1 row)
1648 --
1649 -- test ILIKE (case-insensitive LIKE)
1650 -- Be sure to form every test as an ILIKE/NOT ILIKE pair.
1651 --
1652 SELECT 'hawkeye' ILIKE 'h%' AS "true";
1653 true
1654 ------
1655 t
1656 (1 row)
1658 SELECT 'hawkeye' NOT ILIKE 'h%' AS "false";
1659 false
1660 -------
1661 f
1662 (1 row)
1664 SELECT 'hawkeye' ILIKE 'H%' AS "true";
1665 true
1666 ------
1667 t
1668 (1 row)
1670 SELECT 'hawkeye' NOT ILIKE 'H%' AS "false";
1671 false
1672 -------
1673 f
1674 (1 row)
1676 SELECT 'hawkeye' ILIKE 'H%Eye' AS "true";
1677 true
1678 ------
1679 t
1680 (1 row)
1682 SELECT 'hawkeye' NOT ILIKE 'H%Eye' AS "false";
1683 false
1684 -------
1685 f
1686 (1 row)
1688 SELECT 'Hawkeye' ILIKE 'h%' AS "true";
1689 true
1690 ------
1691 t
1692 (1 row)
1694 SELECT 'Hawkeye' NOT ILIKE 'h%' AS "false";
1695 false
1696 -------
1697 f
1698 (1 row)
1700 SELECT 'ABC'::name ILIKE '_b_' AS "true";
1701 true
1702 ------
1703 t
1704 (1 row)
1706 SELECT 'ABC'::name NOT ILIKE '_b_' AS "false";
1707 false
1708 -------
1709 f
1710 (1 row)
1712 --
1713 -- test %/_ combination cases, cf bugs #4821 and #5478
1714 --
1715 SELECT 'foo' LIKE '_%' as t, 'f' LIKE '_%' as t, '' LIKE '_%' as f;
1716 t | t | f
1717 ---+---+---
1718 t | t | f
1719 (1 row)
1721 SELECT 'foo' LIKE '%_' as t, 'f' LIKE '%_' as t, '' LIKE '%_' as f;
1722 t | t | f
1723 ---+---+---
1724 t | t | f
1725 (1 row)
1727 SELECT 'foo' LIKE '__%' as t, 'foo' LIKE '___%' as t, 'foo' LIKE '____%' as f;
1728 t | t | f
1729 ---+---+---
1730 t | t | f
1731 (1 row)
1733 SELECT 'foo' LIKE '%__' as t, 'foo' LIKE '%___' as t, 'foo' LIKE '%____' as f;
1734 t | t | f
1735 ---+---+---
1736 t | t | f
1737 (1 row)
1739 SELECT 'jack' LIKE '%____%' AS t;
1740 t
1741 ---
1742 t
1743 (1 row)
1745 --
1746 -- basic tests of LIKE with indexes
1747 --
1748 CREATE TABLE texttest (a text PRIMARY KEY, b int);
1749 SELECT * FROM texttest WHERE a LIKE '%1%';
1750 a | b
1751 ---+---
1752 (0 rows)
1754 CREATE TABLE byteatest (a bytea PRIMARY KEY, b int);
1755 SELECT * FROM byteatest WHERE a LIKE '%1%';
1756 a | b
1757 ---+---
1758 (0 rows)
1760 DROP TABLE texttest, byteatest;
1761 --
1762 -- test implicit type conversion
1763 --
1764 -- E021-07 character concatenation
1765 SELECT 'unknown' || ' and unknown' AS "Concat unknown types";
1766 Concat unknown types
1767 ----------------------
1768 unknown and unknown
1769 (1 row)
1771 SELECT text 'text' || ' and unknown' AS "Concat text to unknown type";
1772 Concat text to unknown type
1773 -----------------------------
1774 text and unknown
1775 (1 row)
1777 SELECT char(20) 'characters' || ' and text' AS "Concat char to unknown type";
1778 Concat char to unknown type
1779 -----------------------------
1780 characters and text
1781 (1 row)
1783 SELECT text 'text' || char(20) ' and characters' AS "Concat text to char";
1784 Concat text to char
1785 ---------------------
1786 text and characters
1787 (1 row)
1789 SELECT text 'text' || varchar ' and varchar' AS "Concat text to varchar";
1790 Concat text to varchar
1791 ------------------------
1792 text and varchar
1793 (1 row)
1795 --
1796 -- test substr with toasted text values
1797 --
1798 CREATE TABLE toasttest(f1 text);
1799 insert into toasttest values(repeat('1234567890',10000));
1800 insert into toasttest values(repeat('1234567890',10000));
1801 --
1802 -- Ensure that some values are uncompressed, to test the faster substring
1803 -- operation used in that case
1804 --
1805 alter table toasttest alter column f1 set storage external;
1806 insert into toasttest values(repeat('1234567890',10000));
1807 insert into toasttest values(repeat('1234567890',10000));
1808 -- If the starting position is zero or less, then return from the start of the string
1809 -- adjusting the length to be consistent with the "negative start" per SQL.
1810 SELECT substr(f1, -1, 5) from toasttest;
1811 substr
1812 --------
1813 123
1814 123
1815 123
1816 123
1817 (4 rows)
1819 -- If the length is less than zero, an ERROR is thrown.
1820 SELECT substr(f1, 5, -1) from toasttest;
1821 ERROR: negative substring length not allowed
1822 -- If no third argument (length) is provided, the length to the end of the
1823 -- string is assumed.
1824 SELECT substr(f1, 99995) from toasttest;
1825 substr
1826 --------
1827 567890
1828 567890
1829 567890
1830 567890
1831 (4 rows)
1833 -- If start plus length is > string length, the result is truncated to
1834 -- string length
1835 SELECT substr(f1, 99995, 10) from toasttest;
1836 substr
1837 --------
1838 567890
1839 567890
1840 567890
1841 567890
1842 (4 rows)
1844 TRUNCATE TABLE toasttest;
1845 INSERT INTO toasttest values (repeat('1234567890',300));
1846 INSERT INTO toasttest values (repeat('1234567890',300));
1847 INSERT INTO toasttest values (repeat('1234567890',300));
1848 INSERT INTO toasttest values (repeat('1234567890',300));
1849 -- expect >0 blocks
1850 SELECT pg_relation_size(reltoastrelid) = 0 AS is_empty
1851 FROM pg_class where relname = 'toasttest';
1852 is_empty
1853 ----------
1854 f
1855 (1 row)
1857 TRUNCATE TABLE toasttest;
1858 ALTER TABLE toasttest set (toast_tuple_target = 4080);
1859 INSERT INTO toasttest values (repeat('1234567890',300));
1860 INSERT INTO toasttest values (repeat('1234567890',300));
1861 INSERT INTO toasttest values (repeat('1234567890',300));
1862 INSERT INTO toasttest values (repeat('1234567890',300));
1863 -- expect 0 blocks
1864 SELECT pg_relation_size(reltoastrelid) = 0 AS is_empty
1865 FROM pg_class where relname = 'toasttest';
1866 is_empty
1867 ----------
1868 t
1869 (1 row)
1871 DROP TABLE toasttest;
1872 --
1873 -- test substr with toasted bytea values
1874 --
1875 CREATE TABLE toasttest(f1 bytea);
1876 insert into toasttest values(decode(repeat('1234567890',10000),'escape'));
1877 insert into toasttest values(decode(repeat('1234567890',10000),'escape'));
1878 --
1879 -- Ensure that some values are uncompressed, to test the faster substring
1880 -- operation used in that case
1881 --
1882 alter table toasttest alter column f1 set storage external;
1883 insert into toasttest values(decode(repeat('1234567890',10000),'escape'));
1884 insert into toasttest values(decode(repeat('1234567890',10000),'escape'));
1885 -- If the starting position is zero or less, then return from the start of the string
1886 -- adjusting the length to be consistent with the "negative start" per SQL.
1887 SELECT substr(f1, -1, 5) from toasttest;
1888 substr
1889 --------
1890 123
1891 123
1892 123
1893 123
1894 (4 rows)
1896 -- If the length is less than zero, an ERROR is thrown.
1897 SELECT substr(f1, 5, -1) from toasttest;
1898 ERROR: negative substring length not allowed
1899 -- If no third argument (length) is provided, the length to the end of the
1900 -- string is assumed.
1901 SELECT substr(f1, 99995) from toasttest;
1902 substr
1903 --------
1904 567890
1905 567890
1906 567890
1907 567890
1908 (4 rows)
1910 -- If start plus length is > string length, the result is truncated to
1911 -- string length
1912 SELECT substr(f1, 99995, 10) from toasttest;
1913 substr
1914 --------
1915 567890
1916 567890
1917 567890
1918 567890
1919 (4 rows)
1921 DROP TABLE toasttest;
1922 -- test internally compressing datums
1923 -- this tests compressing a datum to a very small size which exercises a
1924 -- corner case in packed-varlena handling: even though small, the compressed
1925 -- datum must be given a 4-byte header because there are no bits to indicate
1926 -- compression in a 1-byte header
1927 CREATE TABLE toasttest (c char(4096));
1928 INSERT INTO toasttest VALUES('x');
1929 SELECT length(c), c::text FROM toasttest;
1930 length | c
1931 --------+---
1932 1 | x
1933 (1 row)
1935 SELECT c FROM toasttest;
1936 c
1937 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1938 x
1939 (1 row)
1941 DROP TABLE toasttest;
1942 --
1943 -- test length
1944 --
1945 SELECT length('abcdef') AS "length_6";
1946 length_6
1947 ----------
1948 6
1949 (1 row)
1951 --
1952 -- test strpos
1953 --
1954 SELECT strpos('abcdef', 'cd') AS "pos_3";
1955 pos_3
1956 -------
1957 3
1958 (1 row)
1960 SELECT strpos('abcdef', 'xy') AS "pos_0";
1961 pos_0
1962 -------
1963 0
1964 (1 row)
1966 SELECT strpos('abcdef', '') AS "pos_1";
1967 pos_1
1968 -------
1969 1
1970 (1 row)
1972 SELECT strpos('', 'xy') AS "pos_0";
1973 pos_0
1974 -------
1975 0
1976 (1 row)
1978 SELECT strpos('', '') AS "pos_1";
1979 pos_1
1980 -------
1981 1
1982 (1 row)
1984 --
1985 -- test replace
1986 --
1987 SELECT replace('abcdef', 'de', '45') AS "abc45f";
1988 abc45f
1989 --------
1990 abc45f
1991 (1 row)
1993 SELECT replace('yabadabadoo', 'ba', '123') AS "ya123da123doo";
1994 ya123da123doo
1995 ---------------
1996 ya123da123doo
1997 (1 row)
1999 SELECT replace('yabadoo', 'bad', '') AS "yaoo";
2000 yaoo
2001 ------
2002 yaoo
2003 (1 row)
2005 --
2006 -- test split_part
2007 --
2008 select split_part('','@',1) AS "empty string";
2009 empty string
2010 --------------
2012 (1 row)
2014 select split_part('','@',-1) AS "empty string";
2015 empty string
2016 --------------
2018 (1 row)
2020 select split_part('joeuser@mydatabase','',1) AS "joeuser@mydatabase";
2021 joeuser@mydatabase
2022 --------------------
2023 joeuser@mydatabase
2024 (1 row)
2026 select split_part('joeuser@mydatabase','',2) AS "empty string";
2027 empty string
2028 --------------
2030 (1 row)
2032 select split_part('joeuser@mydatabase','',-1) AS "joeuser@mydatabase";
2033 joeuser@mydatabase
2034 --------------------
2035 joeuser@mydatabase
2036 (1 row)
2038 select split_part('joeuser@mydatabase','',-2) AS "empty string";
2039 empty string
2040 --------------
2042 (1 row)
2044 select split_part('joeuser@mydatabase','@',0) AS "an error";
2045 ERROR: field position must not be zero
2046 select split_part('joeuser@mydatabase','@@',1) AS "joeuser@mydatabase";
2047 joeuser@mydatabase
2048 --------------------
2049 joeuser@mydatabase
2050 (1 row)
2052 select split_part('joeuser@mydatabase','@@',2) AS "empty string";
2053 empty string
2054 --------------
2056 (1 row)
2058 select split_part('joeuser@mydatabase','@',1) AS "joeuser";
2059 joeuser
2060 ---------
2061 joeuser
2062 (1 row)
2064 select split_part('joeuser@mydatabase','@',2) AS "mydatabase";
2065 mydatabase
2066 ------------
2067 mydatabase
2068 (1 row)
2070 select split_part('joeuser@mydatabase','@',3) AS "empty string";
2071 empty string
2072 --------------
2074 (1 row)
2076 select split_part('@joeuser@mydatabase@','@',2) AS "joeuser";
2077 joeuser
2078 ---------
2079 joeuser
2080 (1 row)
2082 select split_part('joeuser@mydatabase','@',-1) AS "mydatabase";
2083 mydatabase
2084 ------------
2085 mydatabase
2086 (1 row)
2088 select split_part('joeuser@mydatabase','@',-2) AS "joeuser";
2089 joeuser
2090 ---------
2091 joeuser
2092 (1 row)
2094 select split_part('joeuser@mydatabase','@',-3) AS "empty string";
2095 empty string
2096 --------------
2098 (1 row)
2100 select split_part('@joeuser@mydatabase@','@',-2) AS "mydatabase";
2101 mydatabase
2102 ------------
2103 mydatabase
2104 (1 row)
2106 --
2107 -- test to_hex
2108 --
2109 select to_hex(256*256*256 - 1) AS "ffffff";
2110 ffffff
2111 --------
2112 ffffff
2113 (1 row)
2115 select to_hex(256::bigint*256::bigint*256::bigint*256::bigint - 1) AS "ffffffff";
2116 ffffffff
2117 ----------
2118 ffffffff
2119 (1 row)
2121 --
2122 -- MD5 test suite - from IETF RFC 1321
2123 -- (see: ftp://ftp.rfc-editor.org/in-notes/rfc1321.txt)
2124 --
2125 select md5('') = 'd41d8cd98f00b204e9800998ecf8427e' AS "TRUE";
2126 TRUE
2127 ------
2128 t
2129 (1 row)
2131 select md5('a') = '0cc175b9c0f1b6a831c399e269772661' AS "TRUE";
2132 TRUE
2133 ------
2134 t
2135 (1 row)
2137 select md5('abc') = '900150983cd24fb0d6963f7d28e17f72' AS "TRUE";
2138 TRUE
2139 ------
2140 t
2141 (1 row)
2143 select md5('message digest') = 'f96b697d7cb7938d525a2f31aaf161d0' AS "TRUE";
2144 TRUE
2145 ------
2146 t
2147 (1 row)
2149 select md5('abcdefghijklmnopqrstuvwxyz') = 'c3fcd3d76192e4007dfb496cca67e13b' AS "TRUE";
2150 TRUE
2151 ------
2152 t
2153 (1 row)
2155 select md5('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789') = 'd174ab98d277d9f5a5611c2c9f419d9f' AS "TRUE";
2156 TRUE
2157 ------
2158 t
2159 (1 row)
2161 select md5('12345678901234567890123456789012345678901234567890123456789012345678901234567890') = '57edf4a22be3c955ac49da2e2107b67a' AS "TRUE";
2162 TRUE
2163 ------
2164 t
2165 (1 row)
2167 select md5(''::bytea) = 'd41d8cd98f00b204e9800998ecf8427e' AS "TRUE";
2168 TRUE
2169 ------
2170 t
2171 (1 row)
2173 select md5('a'::bytea) = '0cc175b9c0f1b6a831c399e269772661' AS "TRUE";
2174 TRUE
2175 ------
2176 t
2177 (1 row)
2179 select md5('abc'::bytea) = '900150983cd24fb0d6963f7d28e17f72' AS "TRUE";
2180 TRUE
2181 ------
2182 t
2183 (1 row)
2185 select md5('message digest'::bytea) = 'f96b697d7cb7938d525a2f31aaf161d0' AS "TRUE";
2186 TRUE
2187 ------
2188 t
2189 (1 row)
2191 select md5('abcdefghijklmnopqrstuvwxyz'::bytea) = 'c3fcd3d76192e4007dfb496cca67e13b' AS "TRUE";
2192 TRUE
2193 ------
2194 t
2195 (1 row)
2197 select md5('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'::bytea) = 'd174ab98d277d9f5a5611c2c9f419d9f' AS "TRUE";
2198 TRUE
2199 ------
2200 t
2201 (1 row)
2203 select md5('12345678901234567890123456789012345678901234567890123456789012345678901234567890'::bytea) = '57edf4a22be3c955ac49da2e2107b67a' AS "TRUE";
2204 TRUE
2205 ------
2206 t
2207 (1 row)
2209 --
2210 -- SHA-2
2211 --
2212 SET bytea_output TO hex;
2213 SELECT sha224('');
2214 sha224
2215 ------------------------------------------------------------
2216 \xd14a028c2a3a2bc9476102bb288234c415a2b01f828ea62ac5b3e42f
2217 (1 row)
2219 SELECT sha224('The quick brown fox jumps over the lazy dog.');
2220 sha224
2221 ------------------------------------------------------------
2222 \x619cba8e8e05826e9b8c519c0a5c68f4fb653e8a3d8aa04bb2c8cd4c
2223 (1 row)
2225 SELECT sha256('');
2226 sha256
2227 --------------------------------------------------------------------
2228 \xe3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855
2229 (1 row)
2231 SELECT sha256('The quick brown fox jumps over the lazy dog.');
2232 sha256
2233 --------------------------------------------------------------------
2234 \xef537f25c895bfa782526529a9b63d97aa631564d5d789c2b765448c8635fb6c
2235 (1 row)
2237 SELECT sha384('');
2238 sha384
2239 ----------------------------------------------------------------------------------------------------
2240 \x38b060a751ac96384cd9327eb1b1e36a21fdb71114be07434c0cc7bf63f6e1da274edebfe76f65fbd51ad2f14898b95b
2241 (1 row)
2243 SELECT sha384('The quick brown fox jumps over the lazy dog.');
2244 sha384
2245 ----------------------------------------------------------------------------------------------------
2246 \xed892481d8272ca6df370bf706e4d7bc1b5739fa2177aae6c50e946678718fc67a7af2819a021c2fc34e91bdb63409d7
2247 (1 row)
2249 SELECT sha512('');
2250 sha512
2251 ------------------------------------------------------------------------------------------------------------------------------------
2252 \xcf83e1357eefb8bdf1542850d66d8007d620e4050b5715dc83f4a921d36ce9ce47d0d13c5d85f2b0ff8318d2877eec2f63b931bd47417a81a538327af927da3e
2253 (1 row)
2255 SELECT sha512('The quick brown fox jumps over the lazy dog.');
2256 sha512
2257 ------------------------------------------------------------------------------------------------------------------------------------
2258 \x91ea1245f20d46ae9a037a989f54f1f790f0a47607eeb8a14d12890cea77a1bbc6c7ed9cf205e67b7f2b8fd4c7dfd3a7a8617e45f3c463d481c7e586c39ac1ed
2259 (1 row)
2261 --
2262 -- encode/decode
2263 --
2264 SELECT encode('\x1234567890abcdef00', 'hex');
2265 encode
2266 --------------------
2267 1234567890abcdef00
2268 (1 row)
2270 SELECT decode('1234567890abcdef00', 'hex');
2271 decode
2272 ----------------------
2273 \x1234567890abcdef00
2274 (1 row)
2276 SELECT encode(('\x' || repeat('1234567890abcdef0001', 7))::bytea, 'base64');
2277 encode
2278 ------------------------------------------------------------------------------
2279 EjRWeJCrze8AARI0VniQq83vAAESNFZ4kKvN7wABEjRWeJCrze8AARI0VniQq83vAAESNFZ4kKvN+
2280 7wABEjRWeJCrze8AAQ==
2281 (1 row)
2283 SELECT decode(encode(('\x' || repeat('1234567890abcdef0001', 7))::bytea,
2284 'base64'), 'base64');
2285 decode
2286 ------------------------------------------------------------------------------------------------------------------------------------------------
2287 \x1234567890abcdef00011234567890abcdef00011234567890abcdef00011234567890abcdef00011234567890abcdef00011234567890abcdef00011234567890abcdef0001
2288 (1 row)
2290 SELECT encode('\x1234567890abcdef00', 'escape');
2291 encode
2292 -----------------------------
2293 \x124Vx\220\253\315\357\000
2294 (1 row)
2296 SELECT decode(encode('\x1234567890abcdef00', 'escape'), 'escape');
2297 decode
2298 ----------------------
2299 \x1234567890abcdef00
2300 (1 row)
2302 --
2303 -- get_bit/set_bit etc
2304 --
2305 SELECT get_bit('\x1234567890abcdef00'::bytea, 43);
2306 get_bit
2307 ---------
2308 1
2309 (1 row)
2311 SELECT get_bit('\x1234567890abcdef00'::bytea, 99); -- error
2312 ERROR: index 99 out of valid range, 0..71
2313 SELECT set_bit('\x1234567890abcdef00'::bytea, 43, 0);
2314 set_bit
2315 ----------------------
2316 \x1234567890a3cdef00
2317 (1 row)
2319 SELECT set_bit('\x1234567890abcdef00'::bytea, 99, 0); -- error
2320 ERROR: index 99 out of valid range, 0..71
2321 SELECT get_byte('\x1234567890abcdef00'::bytea, 3);
2322 get_byte
2323 ----------
2324 120
2325 (1 row)
2327 SELECT get_byte('\x1234567890abcdef00'::bytea, 99); -- error
2328 ERROR: index 99 out of valid range, 0..8
2329 SELECT set_byte('\x1234567890abcdef00'::bytea, 7, 11);
2330 set_byte
2331 ----------------------
2332 \x1234567890abcd0b00
2333 (1 row)
2335 SELECT set_byte('\x1234567890abcdef00'::bytea, 99, 11); -- error
2336 ERROR: index 99 out of valid range, 0..8
2337 --
2338 -- test behavior of escape_string_warning and standard_conforming_strings options
2339 --
2340 set escape_string_warning = off;
2341 set standard_conforming_strings = off;
2342 show escape_string_warning;
2343 escape_string_warning
2344 -----------------------
2345 off
2346 (1 row)
2348 show standard_conforming_strings;
2349 standard_conforming_strings
2350 -----------------------------
2351 off
2352 (1 row)
2354 set escape_string_warning = on;
2355 set standard_conforming_strings = on;
2356 show escape_string_warning;
2357 escape_string_warning
2358 -----------------------
2359 on
2360 (1 row)
2362 show standard_conforming_strings;
2363 standard_conforming_strings
2364 -----------------------------
2365 on
2366 (1 row)
2368 select 'a\bcd' as f1, 'a\b''cd' as f2, 'a\b''''cd' as f3, 'abcd\' as f4, 'ab\''cd' as f5, '\\' as f6;
2369 f1 | f2 | f3 | f4 | f5 | f6
2370 -------+--------+---------+-------+--------+----
2371 a\bcd | a\b'cd | a\b''cd | abcd\ | ab\'cd | \\
2372 (1 row)
2374 set standard_conforming_strings = off;
2375 select 'a\\bcd' as f1, 'a\\b\'cd' as f2, 'a\\b\'''cd' as f3, 'abcd\\' as f4, 'ab\\\'cd' as f5, '\\\\' as f6;
2376 WARNING: nonstandard use of \\ in a string literal
2377 LINE 1: select 'a\\bcd' as f1, 'a\\b\'cd' as f2, 'a\\b\'''cd' as f3,...
2378 ^
2379 HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
2380 WARNING: nonstandard use of \\ in a string literal
2381 LINE 1: select 'a\\bcd' as f1, 'a\\b\'cd' as f2, 'a\\b\'''cd' as f3,...
2382 ^
2383 HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
2384 WARNING: nonstandard use of \\ in a string literal
2385 LINE 1: select 'a\\bcd' as f1, 'a\\b\'cd' as f2, 'a\\b\'''cd' as f3,...
2386 ^
2387 HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
2388 WARNING: nonstandard use of \\ in a string literal
2389 LINE 1: ...bcd' as f1, 'a\\b\'cd' as f2, 'a\\b\'''cd' as f3, 'abcd\\' ...
2390 ^
2391 HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
2392 WARNING: nonstandard use of \\ in a string literal
2393 LINE 1: ...'cd' as f2, 'a\\b\'''cd' as f3, 'abcd\\' as f4, 'ab\\\'cd'...
2394 ^
2395 HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
2396 WARNING: nonstandard use of \\ in a string literal
2397 LINE 1: ...'''cd' as f3, 'abcd\\' as f4, 'ab\\\'cd' as f5, '\\\\' as ...
2398 ^
2399 HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
2400 f1 | f2 | f3 | f4 | f5 | f6
2401 -------+--------+---------+-------+--------+----
2402 a\bcd | a\b'cd | a\b''cd | abcd\ | ab\'cd | \\
2403 (1 row)
2405 set escape_string_warning = off;
2406 set standard_conforming_strings = on;
2407 select 'a\bcd' as f1, 'a\b''cd' as f2, 'a\b''''cd' as f3, 'abcd\' as f4, 'ab\''cd' as f5, '\\' as f6;
2408 f1 | f2 | f3 | f4 | f5 | f6
2409 -------+--------+---------+-------+--------+----
2410 a\bcd | a\b'cd | a\b''cd | abcd\ | ab\'cd | \\
2411 (1 row)
2413 set standard_conforming_strings = off;
2414 select 'a\\bcd' as f1, 'a\\b\'cd' as f2, 'a\\b\'''cd' as f3, 'abcd\\' as f4, 'ab\\\'cd' as f5, '\\\\' as f6;
2415 f1 | f2 | f3 | f4 | f5 | f6
2416 -------+--------+---------+-------+--------+----
2417 a\bcd | a\b'cd | a\b''cd | abcd\ | ab\'cd | \\
2418 (1 row)
2420 reset standard_conforming_strings;
2421 --
2422 -- Additional string functions
2423 --
2424 SET bytea_output TO escape;
2425 SELECT initcap('hi THOMAS');
2426 initcap
2427 -----------
2428 Hi Thomas
2429 (1 row)
2431 SELECT lpad('hi', 5, 'xy');
2432 lpad
2433 -------
2434 xyxhi
2435 (1 row)
2437 SELECT lpad('hi', 5);
2438 lpad
2439 -------
2440 hi
2441 (1 row)
2443 SELECT lpad('hi', -5, 'xy');
2444 lpad
2445 ------
2447 (1 row)
2449 SELECT lpad('hello', 2);
2450 lpad
2451 ------
2452 he
2453 (1 row)
2455 SELECT lpad('hi', 5, '');
2456 lpad
2457 ------
2458 hi
2459 (1 row)
2461 SELECT rpad('hi', 5, 'xy');
2462 rpad
2463 -------
2464 hixyx
2465 (1 row)
2467 SELECT rpad('hi', 5);
2468 rpad
2469 -------
2470 hi
2471 (1 row)
2473 SELECT rpad('hi', -5, 'xy');
2474 rpad
2475 ------
2477 (1 row)
2479 SELECT rpad('hello', 2);
2480 rpad
2481 ------
2482 he
2483 (1 row)
2485 SELECT rpad('hi', 5, '');
2486 rpad
2487 ------
2488 hi
2489 (1 row)
2491 SELECT ltrim('zzzytrim', 'xyz');
2492 ltrim
2493 -------
2494 trim
2495 (1 row)
2497 SELECT translate('', '14', 'ax');
2498 translate
2499 -----------
2501 (1 row)
2503 SELECT translate('12345', '14', 'ax');
2504 translate
2505 -----------
2506 a23x5
2507 (1 row)
2509 SELECT ascii('x');
2510 ascii
2511 -------
2512 120
2513 (1 row)
2515 SELECT ascii('');
2516 ascii
2517 -------
2518 0
2519 (1 row)
2521 SELECT chr(65);
2522 chr
2523 -----
2524 A
2525 (1 row)
2527 SELECT chr(0);
2528 ERROR: null character not permitted
2529 SELECT repeat('Pg', 4);
2530 repeat
2531 ----------
2532 PgPgPgPg
2533 (1 row)
2535 SELECT repeat('Pg', -4);
2536 repeat
2537 --------
2539 (1 row)
2541 SELECT SUBSTRING('1234567890'::bytea FROM 3) "34567890";
2542 34567890
2543 ----------
2544 34567890
2545 (1 row)
2547 SELECT SUBSTRING('1234567890'::bytea FROM 4 FOR 3) AS "456";
2548 456
2549 -----
2550 456
2551 (1 row)
2553 SELECT SUBSTRING('string'::bytea FROM 2 FOR 2147483646) AS "tring";
2554 tring
2555 -------
2556 tring
2557 (1 row)
2559 SELECT SUBSTRING('string'::bytea FROM -10 FOR 2147483646) AS "string";
2560 string
2561 --------
2562 string
2563 (1 row)
2565 SELECT SUBSTRING('string'::bytea FROM -10 FOR -2147483646) AS "error";
2566 ERROR: negative substring length not allowed
2567 SELECT trim(E'\\000'::bytea from E'\\000Tom\\000'::bytea);
2568 btrim
2569 -------
2570 Tom
2571 (1 row)
2573 SELECT trim(leading E'\\000'::bytea from E'\\000Tom\\000'::bytea);
2574 ltrim
2575 ---------
2576 Tom\000
2577 (1 row)
2579 SELECT trim(trailing E'\\000'::bytea from E'\\000Tom\\000'::bytea);
2580 rtrim
2581 ---------
2582 \000Tom
2583 (1 row)
2585 SELECT btrim(E'\\000trim\\000'::bytea, E'\\000'::bytea);
2586 btrim
2587 -------
2588 trim
2589 (1 row)
2591 SELECT btrim(''::bytea, E'\\000'::bytea);
2592 btrim
2593 -------
2595 (1 row)
2597 SELECT btrim(E'\\000trim\\000'::bytea, ''::bytea);
2598 btrim
2599 --------------
2600 \000trim\000
2601 (1 row)
2603 SELECT encode(overlay(E'Th\\000omas'::bytea placing E'Th\\001omas'::bytea from 2),'escape');
2604 encode
2605 -------------
2606 TTh\x01omas
2607 (1 row)
2609 SELECT encode(overlay(E'Th\\000omas'::bytea placing E'\\002\\003'::bytea from 8),'escape');
2610 encode
2611 --------------------
2612 Th\000omas\x02\x03
2613 (1 row)
2615 SELECT encode(overlay(E'Th\\000omas'::bytea placing E'\\002\\003'::bytea from 5 for 3),'escape');
2616 encode
2617 -----------------
2618 Th\000o\x02\x03
2619 (1 row)
2621 SELECT bit_count('\x1234567890'::bytea);
2622 bit_count
2623 -----------
2624 15
2625 (1 row)
2627 SELECT unistr('\0064at\+0000610');
2628 unistr
2629 --------
2630 data0
2631 (1 row)
2633 SELECT unistr('d\u0061t\U000000610');
2634 unistr
2635 --------
2636 data0
2637 (1 row)
2639 SELECT unistr('a\\b');
2640 unistr
2641 --------
2642 a\b
2643 (1 row)
2645 -- errors:
2646 SELECT unistr('wrong: \db99');
2647 ERROR: invalid Unicode surrogate pair
2648 SELECT unistr('wrong: \db99\0061');
2649 ERROR: invalid Unicode surrogate pair
2650 SELECT unistr('wrong: \+00db99\+000061');
2651 ERROR: invalid Unicode surrogate pair
2652 SELECT unistr('wrong: \+2FFFFF');
2653 ERROR: invalid Unicode code point: 2FFFFF
2654 SELECT unistr('wrong: \udb99\u0061');
2655 ERROR: invalid Unicode surrogate pair
2656 SELECT unistr('wrong: \U0000db99\U00000061');
2657 ERROR: invalid Unicode surrogate pair
2658 SELECT unistr('wrong: \U002FFFFF');
2659 ERROR: invalid Unicode code point: 2FFFFF
2660 SELECT unistr('wrong: \xyz');
2661 ERROR: invalid Unicode escape
2662 HINT: Unicode escapes must be \XXXX, \+XXXXXX, \uXXXX, or \UXXXXXXXX.