diff -U3 /home/dd/projects/postgres/vanilla/planb/postgres/master/src/test/regress/expected/boolean.out /home/dd/projects/postgres/vanilla/planb/postgres/master/src/test/regress/results/boolean.out --- /home/dd/projects/postgres/vanilla/planb/postgres/master/src/test/regress/expected/boolean.out 2025-07-30 23:13:15.363942854 +0300 +++ /home/dd/projects/postgres/vanilla/planb/postgres/master/src/test/regress/results/boolean.out 2025-09-16 00:27:00.213740257 +0300 @@ -328,7 +328,11 @@ t | f t | f t | f -(12 rows) + f | f + f | f + f | f + f | f +(16 rows) SELECT BOOLTBL1.*, BOOLTBL2.* FROM BOOLTBL1, BOOLTBL2 @@ -347,7 +351,11 @@ t | f t | f t | f -(12 rows) + f | f + f | f + f | f + f | f +(16 rows) SELECT BOOLTBL1.*, BOOLTBL2.* FROM BOOLTBL1, BOOLTBL2 diff -U3 /home/dd/projects/postgres/vanilla/planb/postgres/master/src/test/regress/expected/bit.out /home/dd/projects/postgres/vanilla/planb/postgres/master/src/test/regress/results/bit.out --- /home/dd/projects/postgres/vanilla/planb/postgres/master/src/test/regress/expected/bit.out 2025-07-30 23:13:15.363942854 +0300 +++ /home/dd/projects/postgres/vanilla/planb/postgres/master/src/test/regress/results/bit.out 2025-09-16 00:27:00.683744347 +0300 @@ -61,790 +61,7 @@ SELECT v, b, (v || b) AS concat FROM BIT_TABLE, VARBIT_TABLE ORDER BY 3; - v | b | concat --------------+-------------+------------------------ - | 00000000000 | 00000000000 - 0 | 00000000000 | 000000000000 - 0 | 01010101010 | 001010101010 - 010101 | 00000000000 | 01010100000000000 - | 01010101010 | 01010101010 - 01010101010 | 00000000000 | 0101010101000000000000 - 01010101010 | 01010101010 | 0101010101001010101010 - 010101 | 01010101010 | 01010101010101010 - 01010101010 | 11011000000 | 0101010101011011000000 - 010101 | 11011000000 | 01010111011000000 - 0 | 11011000000 | 011011000000 - | 11011000000 | 11011000000 -(12 rows) - --- Length -SELECT b, length(b) AS lb - FROM BIT_TABLE; - b | lb --------------+---- - 00000000000 | 11 - 11011000000 | 11 - 01010101010 | 11 -(3 rows) - -SELECT v, length(v) AS lv - FROM VARBIT_TABLE; - v | lv --------------+---- - | 0 - 0 | 1 - 010101 | 6 - 01010101010 | 11 -(4 rows) - --- Substring -SELECT b, - SUBSTRING(b FROM 2 FOR 4) AS sub_2_4, - SUBSTRING(b FROM 7 FOR 13) AS sub_7_13, - SUBSTRING(b FROM 6) AS sub_6 - FROM BIT_TABLE; - b | sub_2_4 | sub_7_13 | sub_6 --------------+---------+----------+-------- - 00000000000 | 0000 | 00000 | 000000 - 11011000000 | 1011 | 00000 | 000000 - 01010101010 | 1010 | 01010 | 101010 -(3 rows) - -SELECT v, - SUBSTRING(v FROM 2 FOR 4) AS sub_2_4, - SUBSTRING(v FROM 7 FOR 13) AS sub_7_13, - SUBSTRING(v FROM 6) AS sub_6 - FROM VARBIT_TABLE; - v | sub_2_4 | sub_7_13 | sub_6 --------------+---------+----------+-------- - | | | - 0 | | | - 010101 | 1010 | | 1 - 01010101010 | 1010 | 01010 | 101010 -(4 rows) - --- test overflow cases -SELECT SUBSTRING('01010101'::bit(8) FROM 2 FOR 2147483646) AS "1010101"; - 1010101 ---------- - 1010101 -(1 row) - -SELECT SUBSTRING('01010101'::bit(8) FROM -10 FOR 2147483646) AS "01010101"; - 01010101 ----------- - 01010101 -(1 row) - -SELECT SUBSTRING('01010101'::bit(8) FROM -10 FOR -2147483646) AS "error"; -ERROR: negative substring length not allowed -SELECT SUBSTRING('01010101'::varbit FROM 2 FOR 2147483646) AS "1010101"; - 1010101 ---------- - 1010101 -(1 row) - -SELECT SUBSTRING('01010101'::varbit FROM -10 FOR 2147483646) AS "01010101"; - 01010101 ----------- - 01010101 -(1 row) - -SELECT SUBSTRING('01010101'::varbit FROM -10 FOR -2147483646) AS "error"; -ERROR: negative substring length not allowed ---- Bit operations -DROP TABLE varbit_table; -CREATE TABLE varbit_table (a BIT VARYING(16), b BIT VARYING(16)); -COPY varbit_table FROM stdin; -SELECT a, b, ~a AS "~ a", a & b AS "a & b", - a | b AS "a | b", a # b AS "a # b" FROM varbit_table; - a | b | ~ a | a & b | a | b | a # b -------------------+------------------+------------------+------------------+------------------+------------------ - 00001111 | 00010000 | 11110000 | 00000000 | 00011111 | 00011111 - 00011111 | 00010001 | 11100000 | 00010001 | 00011111 | 00001110 - 00101111 | 00010010 | 11010000 | 00000010 | 00111111 | 00111101 - 00111111 | 00010011 | 11000000 | 00010011 | 00111111 | 00101100 - 10001111 | 00000100 | 01110000 | 00000100 | 10001111 | 10001011 - 0000000000001111 | 0000000000010000 | 1111111111110000 | 0000000000000000 | 0000000000011111 | 0000000000011111 - 0000000100100011 | 1111111111111111 | 1111111011011100 | 0000000100100011 | 1111111111111111 | 1111111011011100 - 0010010001101000 | 0010010001101000 | 1101101110010111 | 0010010001101000 | 0010010001101000 | 0000000000000000 - 1111101001010000 | 0000010110101111 | 0000010110101111 | 0000000000000000 | 1111111111111111 | 1111111111111111 - 0001001000110100 | 1111111111110101 | 1110110111001011 | 0001001000110100 | 1111111111110101 | 1110110111000001 -(10 rows) - -SELECT a,b,a=b AS "a>=b",a>b AS "a>b",a<>b AS "a<>b" FROM varbit_table; - a | b | a=b | a>b | a<>b -------------------+------------------+-----+------+-----+------+-----+------ - 00001111 | 00010000 | t | t | f | f | f | t - 00011111 | 00010001 | f | f | f | t | t | t - 00101111 | 00010010 | f | f | f | t | t | t - 00111111 | 00010011 | f | f | f | t | t | t - 10001111 | 00000100 | f | f | f | t | t | t - 0000000000001111 | 0000000000010000 | t | t | f | f | f | t - 0000000100100011 | 1111111111111111 | t | t | f | f | f | t - 0010010001101000 | 0010010001101000 | f | t | t | t | f | f - 1111101001010000 | 0000010110101111 | f | f | f | t | t | t - 0001001000110100 | 1111111111110101 | t | t | f | f | f | t -(10 rows) - -SELECT a,a<<4 AS "a<<4",b,b>>2 AS "b>>2" FROM varbit_table; - a | a<<4 | b | b>>2 -------------------+------------------+------------------+------------------ - 00001111 | 11110000 | 00010000 | 00000100 - 00011111 | 11110000 | 00010001 | 00000100 - 00101111 | 11110000 | 00010010 | 00000100 - 00111111 | 11110000 | 00010011 | 00000100 - 10001111 | 11110000 | 00000100 | 00000001 - 0000000000001111 | 0000000011110000 | 0000000000010000 | 0000000000000100 - 0000000100100011 | 0001001000110000 | 1111111111111111 | 0011111111111111 - 0010010001101000 | 0100011010000000 | 0010010001101000 | 0000100100011010 - 1111101001010000 | 1010010100000000 | 0000010110101111 | 0000000101101011 - 0001001000110100 | 0010001101000000 | 1111111111110101 | 0011111111111101 -(10 rows) - -DROP TABLE varbit_table; ---- Bit operations -DROP TABLE bit_table; -CREATE TABLE bit_table (a BIT(16), b BIT(16)); -COPY bit_table FROM stdin; -SELECT a,b,~a AS "~ a",a & b AS "a & b", - a|b AS "a | b", a # b AS "a # b" FROM bit_table; - a | b | ~ a | a & b | a | b | a # b -------------------+------------------+------------------+------------------+------------------+------------------ - 0000111100000000 | 0001000000000000 | 1111000011111111 | 0000000000000000 | 0001111100000000 | 0001111100000000 - 0001111100000000 | 0001000100000000 | 1110000011111111 | 0001000100000000 | 0001111100000000 | 0000111000000000 - 0010111100000000 | 0001001000000000 | 1101000011111111 | 0000001000000000 | 0011111100000000 | 0011110100000000 - 0011111100000000 | 0001001100000000 | 1100000011111111 | 0001001100000000 | 0011111100000000 | 0010110000000000 - 1000111100000000 | 0000010000000000 | 0111000011111111 | 0000010000000000 | 1000111100000000 | 1000101100000000 - 0000000000001111 | 0000000000010000 | 1111111111110000 | 0000000000000000 | 0000000000011111 | 0000000000011111 - 0000000100100011 | 1111111111111111 | 1111111011011100 | 0000000100100011 | 1111111111111111 | 1111111011011100 - 0010010001101000 | 0010010001101000 | 1101101110010111 | 0010010001101000 | 0010010001101000 | 0000000000000000 - 1111101001010000 | 0000010110101111 | 0000010110101111 | 0000000000000000 | 1111111111111111 | 1111111111111111 - 0001001000110100 | 1111111111110101 | 1110110111001011 | 0001001000110100 | 1111111111110101 | 1110110111000001 -(10 rows) - -SELECT a,b,a=b AS "a>=b",a>b AS "a>b",a<>b AS "a<>b" FROM bit_table; - a | b | a=b | a>b | a<>b -------------------+------------------+-----+------+-----+------+-----+------ - 0000111100000000 | 0001000000000000 | t | t | f | f | f | t - 0001111100000000 | 0001000100000000 | f | f | f | t | t | t - 0010111100000000 | 0001001000000000 | f | f | f | t | t | t - 0011111100000000 | 0001001100000000 | f | f | f | t | t | t - 1000111100000000 | 0000010000000000 | f | f | f | t | t | t - 0000000000001111 | 0000000000010000 | t | t | f | f | f | t - 0000000100100011 | 1111111111111111 | t | t | f | f | f | t - 0010010001101000 | 0010010001101000 | f | t | t | t | f | f - 1111101001010000 | 0000010110101111 | f | f | f | t | t | t - 0001001000110100 | 1111111111110101 | t | t | f | f | f | t -(10 rows) - -SELECT a,a<<4 AS "a<<4",b,b>>2 AS "b>>2" FROM bit_table; - a | a<<4 | b | b>>2 -------------------+------------------+------------------+------------------ - 0000111100000000 | 1111000000000000 | 0001000000000000 | 0000010000000000 - 0001111100000000 | 1111000000000000 | 0001000100000000 | 0000010001000000 - 0010111100000000 | 1111000000000000 | 0001001000000000 | 0000010010000000 - 0011111100000000 | 1111000000000000 | 0001001100000000 | 0000010011000000 - 1000111100000000 | 1111000000000000 | 0000010000000000 | 0000000100000000 - 0000000000001111 | 0000000011110000 | 0000000000010000 | 0000000000000100 - 0000000100100011 | 0001001000110000 | 1111111111111111 | 0011111111111111 - 0010010001101000 | 0100011010000000 | 0010010001101000 | 0000100100011010 - 1111101001010000 | 1010010100000000 | 0000010110101111 | 0000000101101011 - 0001001000110100 | 0010001101000000 | 1111111111110101 | 0011111111111101 -(10 rows) - -DROP TABLE bit_table; --- The following should fail -select B'001' & B'10'; -ERROR: cannot AND bit strings of different sizes -select B'0111' | B'011'; -ERROR: cannot OR bit strings of different sizes -select B'0010' # B'011101'; -ERROR: cannot XOR bit strings of different sizes --- More position tests, checking all the boundary cases -SELECT POSITION(B'1010' IN B'0000101'); -- 0 - position ----------- - 0 -(1 row) - -SELECT POSITION(B'1010' IN B'00001010'); -- 5 - position ----------- - 5 -(1 row) - -SELECT POSITION(B'1010' IN B'00000101'); -- 0 - position ----------- - 0 -(1 row) - -SELECT POSITION(B'1010' IN B'000001010'); -- 6 - position ----------- - 6 -(1 row) - -SELECT POSITION(B'' IN B'00001010'); -- 1 - position ----------- - 1 -(1 row) - -SELECT POSITION(B'0' IN B''); -- 0 - position ----------- - 0 -(1 row) - -SELECT POSITION(B'' IN B''); -- 0 - position ----------- - 0 -(1 row) - -SELECT POSITION(B'101101' IN B'001011011011011000'); -- 3 - position ----------- - 3 -(1 row) - -SELECT POSITION(B'10110110' IN B'001011011011010'); -- 3 - position ----------- - 3 -(1 row) - -SELECT POSITION(B'1011011011011' IN B'001011011011011'); -- 3 - position ----------- - 3 -(1 row) - -SELECT POSITION(B'1011011011011' IN B'00001011011011011'); -- 5 - position ----------- - 5 -(1 row) - -SELECT POSITION(B'11101011' IN B'11101011'); -- 1 - position ----------- - 1 -(1 row) - -SELECT POSITION(B'11101011' IN B'011101011'); -- 2 - position ----------- - 2 -(1 row) - -SELECT POSITION(B'11101011' IN B'00011101011'); -- 4 - position ----------- - 4 -(1 row) - -SELECT POSITION(B'11101011' IN B'0000011101011'); -- 6 - position ----------- - 6 -(1 row) - -SELECT POSITION(B'111010110' IN B'111010110'); -- 1 - position ----------- - 1 -(1 row) - -SELECT POSITION(B'111010110' IN B'0111010110'); -- 2 - position ----------- - 2 -(1 row) - -SELECT POSITION(B'111010110' IN B'000111010110'); -- 4 - position ----------- - 4 -(1 row) - -SELECT POSITION(B'111010110' IN B'00000111010110'); -- 6 - position ----------- - 6 -(1 row) - -SELECT POSITION(B'111010110' IN B'11101011'); -- 0 - position ----------- - 0 -(1 row) - -SELECT POSITION(B'111010110' IN B'011101011'); -- 0 - position ----------- - 0 -(1 row) - -SELECT POSITION(B'111010110' IN B'00011101011'); -- 0 - position ----------- - 0 -(1 row) - -SELECT POSITION(B'111010110' IN B'0000011101011'); -- 0 - position ----------- - 0 -(1 row) - -SELECT POSITION(B'111010110' IN B'111010110'); -- 1 - position ----------- - 1 -(1 row) - -SELECT POSITION(B'111010110' IN B'0111010110'); -- 2 - position ----------- - 2 -(1 row) - -SELECT POSITION(B'111010110' IN B'000111010110'); -- 4 - position ----------- - 4 -(1 row) - -SELECT POSITION(B'111010110' IN B'00000111010110'); -- 6 - position ----------- - 6 -(1 row) - -SELECT POSITION(B'111010110' IN B'000001110101111101011'); -- 0 - position ----------- - 0 -(1 row) - -SELECT POSITION(B'111010110' IN B'0000001110101111101011'); -- 0 - position ----------- - 0 -(1 row) - -SELECT POSITION(B'111010110' IN B'000000001110101111101011'); -- 0 - position ----------- - 0 -(1 row) - -SELECT POSITION(B'111010110' IN B'00000000001110101111101011'); -- 0 - position ----------- - 0 -(1 row) - -SELECT POSITION(B'111010110' IN B'0000011101011111010110'); -- 14 - position ----------- - 14 -(1 row) - -SELECT POSITION(B'111010110' IN B'00000011101011111010110'); -- 15 - position ----------- - 15 -(1 row) - -SELECT POSITION(B'111010110' IN B'0000000011101011111010110'); -- 17 - position ----------- - 17 -(1 row) - -SELECT POSITION(B'111010110' IN B'000000000011101011111010110'); -- 19 - position ----------- - 19 -(1 row) - -SELECT POSITION(B'000000000011101011111010110' IN B'000000000011101011111010110'); -- 1 - position ----------- - 1 -(1 row) - -SELECT POSITION(B'00000000011101011111010110' IN B'000000000011101011111010110'); -- 2 - position ----------- - 2 -(1 row) - -SELECT POSITION(B'0000000000011101011111010110' IN B'000000000011101011111010110'); -- 0 - position ----------- - 0 -(1 row) - --- Shifting -CREATE TABLE BIT_SHIFT_TABLE(b BIT(16)); -INSERT INTO BIT_SHIFT_TABLE VALUES (B'1101100000000000'); -INSERT INTO BIT_SHIFT_TABLE SELECT b>>1 FROM BIT_SHIFT_TABLE; -INSERT INTO BIT_SHIFT_TABLE SELECT b>>2 FROM BIT_SHIFT_TABLE; -INSERT INTO BIT_SHIFT_TABLE SELECT b>>4 FROM BIT_SHIFT_TABLE; -INSERT INTO BIT_SHIFT_TABLE SELECT b>>8 FROM BIT_SHIFT_TABLE; -SELECT POSITION(B'1101' IN b), - POSITION(B'11011' IN b), - b - FROM BIT_SHIFT_TABLE ; - position | position | b -----------+----------+------------------ - 1 | 1 | 1101100000000000 - 2 | 2 | 0110110000000000 - 3 | 3 | 0011011000000000 - 4 | 4 | 0001101100000000 - 5 | 5 | 0000110110000000 - 6 | 6 | 0000011011000000 - 7 | 7 | 0000001101100000 - 8 | 8 | 0000000110110000 - 9 | 9 | 0000000011011000 - 10 | 10 | 0000000001101100 - 11 | 11 | 0000000000110110 - 12 | 12 | 0000000000011011 - 13 | 0 | 0000000000001101 - 0 | 0 | 0000000000000110 - 0 | 0 | 0000000000000011 - 0 | 0 | 0000000000000001 -(16 rows) - -SELECT b, b >> 1 AS bsr, b << 1 AS bsl - FROM BIT_SHIFT_TABLE ; - b | bsr | bsl -------------------+------------------+------------------ - 1101100000000000 | 0110110000000000 | 1011000000000000 - 0110110000000000 | 0011011000000000 | 1101100000000000 - 0011011000000000 | 0001101100000000 | 0110110000000000 - 0001101100000000 | 0000110110000000 | 0011011000000000 - 0000110110000000 | 0000011011000000 | 0001101100000000 - 0000011011000000 | 0000001101100000 | 0000110110000000 - 0000001101100000 | 0000000110110000 | 0000011011000000 - 0000000110110000 | 0000000011011000 | 0000001101100000 - 0000000011011000 | 0000000001101100 | 0000000110110000 - 0000000001101100 | 0000000000110110 | 0000000011011000 - 0000000000110110 | 0000000000011011 | 0000000001101100 - 0000000000011011 | 0000000000001101 | 0000000000110110 - 0000000000001101 | 0000000000000110 | 0000000000011010 - 0000000000000110 | 0000000000000011 | 0000000000001100 - 0000000000000011 | 0000000000000001 | 0000000000000110 - 0000000000000001 | 0000000000000000 | 0000000000000010 -(16 rows) - -SELECT b, b >> 8 AS bsr8, b << 8 AS bsl8 - FROM BIT_SHIFT_TABLE ; - b | bsr8 | bsl8 -------------------+------------------+------------------ - 1101100000000000 | 0000000011011000 | 0000000000000000 - 0110110000000000 | 0000000001101100 | 0000000000000000 - 0011011000000000 | 0000000000110110 | 0000000000000000 - 0001101100000000 | 0000000000011011 | 0000000000000000 - 0000110110000000 | 0000000000001101 | 1000000000000000 - 0000011011000000 | 0000000000000110 | 1100000000000000 - 0000001101100000 | 0000000000000011 | 0110000000000000 - 0000000110110000 | 0000000000000001 | 1011000000000000 - 0000000011011000 | 0000000000000000 | 1101100000000000 - 0000000001101100 | 0000000000000000 | 0110110000000000 - 0000000000110110 | 0000000000000000 | 0011011000000000 - 0000000000011011 | 0000000000000000 | 0001101100000000 - 0000000000001101 | 0000000000000000 | 0000110100000000 - 0000000000000110 | 0000000000000000 | 0000011000000000 - 0000000000000011 | 0000000000000000 | 0000001100000000 - 0000000000000001 | 0000000000000000 | 0000000100000000 -(16 rows) - -SELECT b::bit(15), b::bit(15) >> 1 AS bsr, b::bit(15) << 1 AS bsl - FROM BIT_SHIFT_TABLE ; - b | bsr | bsl ------------------+-----------------+----------------- - 110110000000000 | 011011000000000 | 101100000000000 - 011011000000000 | 001101100000000 | 110110000000000 - 001101100000000 | 000110110000000 | 011011000000000 - 000110110000000 | 000011011000000 | 001101100000000 - 000011011000000 | 000001101100000 | 000110110000000 - 000001101100000 | 000000110110000 | 000011011000000 - 000000110110000 | 000000011011000 | 000001101100000 - 000000011011000 | 000000001101100 | 000000110110000 - 000000001101100 | 000000000110110 | 000000011011000 - 000000000110110 | 000000000011011 | 000000001101100 - 000000000011011 | 000000000001101 | 000000000110110 - 000000000001101 | 000000000000110 | 000000000011010 - 000000000000110 | 000000000000011 | 000000000001100 - 000000000000011 | 000000000000001 | 000000000000110 - 000000000000001 | 000000000000000 | 000000000000010 - 000000000000000 | 000000000000000 | 000000000000000 -(16 rows) - -SELECT b::bit(15), b::bit(15) >> 8 AS bsr8, b::bit(15) << 8 AS bsl8 - FROM BIT_SHIFT_TABLE ; - b | bsr8 | bsl8 ------------------+-----------------+----------------- - 110110000000000 | 000000001101100 | 000000000000000 - 011011000000000 | 000000000110110 | 000000000000000 - 001101100000000 | 000000000011011 | 000000000000000 - 000110110000000 | 000000000001101 | 000000000000000 - 000011011000000 | 000000000000110 | 100000000000000 - 000001101100000 | 000000000000011 | 110000000000000 - 000000110110000 | 000000000000001 | 011000000000000 - 000000011011000 | 000000000000000 | 101100000000000 - 000000001101100 | 000000000000000 | 110110000000000 - 000000000110110 | 000000000000000 | 011011000000000 - 000000000011011 | 000000000000000 | 001101100000000 - 000000000001101 | 000000000000000 | 000110100000000 - 000000000000110 | 000000000000000 | 000011000000000 - 000000000000011 | 000000000000000 | 000001100000000 - 000000000000001 | 000000000000000 | 000000100000000 - 000000000000000 | 000000000000000 | 000000000000000 -(16 rows) - -CREATE TABLE VARBIT_SHIFT_TABLE(v BIT VARYING(20)); -INSERT INTO VARBIT_SHIFT_TABLE VALUES (B'11011'); -INSERT INTO VARBIT_SHIFT_TABLE SELECT CAST(v || B'0' AS BIT VARYING(6)) >>1 FROM VARBIT_SHIFT_TABLE; -INSERT INTO VARBIT_SHIFT_TABLE SELECT CAST(v || B'00' AS BIT VARYING(8)) >>2 FROM VARBIT_SHIFT_TABLE; -INSERT INTO VARBIT_SHIFT_TABLE SELECT CAST(v || B'0000' AS BIT VARYING(12)) >>4 FROM VARBIT_SHIFT_TABLE; -INSERT INTO VARBIT_SHIFT_TABLE SELECT CAST(v || B'00000000' AS BIT VARYING(20)) >>8 FROM VARBIT_SHIFT_TABLE; -SELECT POSITION(B'1101' IN v), - POSITION(B'11011' IN v), - v - FROM VARBIT_SHIFT_TABLE ; - position | position | v -----------+----------+---------------------- - 1 | 1 | 11011 - 2 | 2 | 011011 - 3 | 3 | 0011011 - 4 | 4 | 00011011 - 5 | 5 | 000011011 - 6 | 6 | 0000011011 - 7 | 7 | 00000011011 - 8 | 8 | 000000011011 - 9 | 9 | 0000000011011 - 10 | 10 | 00000000011011 - 11 | 11 | 000000000011011 - 12 | 12 | 0000000000011011 - 13 | 13 | 00000000000011011 - 14 | 14 | 000000000000011011 - 15 | 15 | 0000000000000011011 - 16 | 16 | 00000000000000011011 -(16 rows) - -SELECT v, v >> 1 AS vsr, v << 1 AS vsl - FROM VARBIT_SHIFT_TABLE ; - v | vsr | vsl -----------------------+----------------------+---------------------- - 11011 | 01101 | 10110 - 011011 | 001101 | 110110 - 0011011 | 0001101 | 0110110 - 00011011 | 00001101 | 00110110 - 000011011 | 000001101 | 000110110 - 0000011011 | 0000001101 | 0000110110 - 00000011011 | 00000001101 | 00000110110 - 000000011011 | 000000001101 | 000000110110 - 0000000011011 | 0000000001101 | 0000000110110 - 00000000011011 | 00000000001101 | 00000000110110 - 000000000011011 | 000000000001101 | 000000000110110 - 0000000000011011 | 0000000000001101 | 0000000000110110 - 00000000000011011 | 00000000000001101 | 00000000000110110 - 000000000000011011 | 000000000000001101 | 000000000000110110 - 0000000000000011011 | 0000000000000001101 | 0000000000000110110 - 00000000000000011011 | 00000000000000001101 | 00000000000000110110 -(16 rows) - -SELECT v, v >> 8 AS vsr8, v << 8 AS vsl8 - FROM VARBIT_SHIFT_TABLE ; - v | vsr8 | vsl8 -----------------------+----------------------+---------------------- - 11011 | 00000 | 00000 - 011011 | 000000 | 000000 - 0011011 | 0000000 | 0000000 - 00011011 | 00000000 | 00000000 - 000011011 | 000000000 | 100000000 - 0000011011 | 0000000000 | 1100000000 - 00000011011 | 00000000000 | 01100000000 - 000000011011 | 000000000000 | 101100000000 - 0000000011011 | 0000000000000 | 1101100000000 - 00000000011011 | 00000000000000 | 01101100000000 - 000000000011011 | 000000000000000 | 001101100000000 - 0000000000011011 | 0000000000000000 | 0001101100000000 - 00000000000011011 | 00000000000000000 | 00001101100000000 - 000000000000011011 | 000000000000000000 | 000001101100000000 - 0000000000000011011 | 0000000000000000000 | 0000001101100000000 - 00000000000000011011 | 00000000000000000000 | 00000001101100000000 -(16 rows) - -DROP TABLE BIT_SHIFT_TABLE; -DROP TABLE VARBIT_SHIFT_TABLE; --- Get/Set bit -SELECT get_bit(B'0101011000100', 10); - get_bit ---------- - 1 -(1 row) - -SELECT set_bit(B'0101011000100100', 15, 1); - set_bit ------------------- - 0101011000100101 -(1 row) - -SELECT set_bit(B'0101011000100100', 16, 1); -- fail -ERROR: bit index 16 out of valid range (0..15) --- Overlay -SELECT overlay(B'0101011100' placing '001' from 2 for 3); - overlay ------------- - 0001011100 -(1 row) - -SELECT overlay(B'0101011100' placing '101' from 6); - overlay ------------- - 0101010100 -(1 row) - -SELECT overlay(B'0101011100' placing '001' from 11); - overlay ---------------- - 0101011100001 -(1 row) - -SELECT overlay(B'0101011100' placing '001' from 20); - overlay ---------------- - 0101011100001 -(1 row) - --- bit_count -SELECT bit_count(B'0101011100'::bit(10)); - bit_count ------------ - 5 -(1 row) - -SELECT bit_count(B'1111111111'::bit(10)); - bit_count ------------ - 10 -(1 row) - -SELECT bit_count(repeat('0', 100)::bit(100)); - bit_count ------------ - 0 -(1 row) - -SELECT bit_count(repeat('1', 100)::bit(100)); - bit_count ------------ - 100 -(1 row) - -SELECT bit_count(repeat('01', 500)::bit(1000)); - bit_count ------------ - 500 -(1 row) - -SELECT bit_count(repeat('10101', 200)::bit(1000)); - bit_count ------------ - 600 -(1 row) - --- This table is intentionally left around to exercise pg_dump/pg_upgrade -CREATE TABLE bit_defaults( - b1 bit(4) DEFAULT '1001', - b2 bit(4) DEFAULT B'0101', - b3 bit varying(5) DEFAULT '1001', - b4 bit varying(5) DEFAULT B'0101' -); -\d bit_defaults - Table "public.bit_defaults" - Column | Type | Collation | Nullable | Default ---------+----------------+-----------+----------+--------------------- - b1 | bit(4) | | | '1001'::"bit" - b2 | bit(4) | | | '0101'::"bit" - b3 | bit varying(5) | | | '1001'::bit varying - b4 | bit varying(5) | | | '0101'::"bit" - -INSERT INTO bit_defaults DEFAULT VALUES; -TABLE bit_defaults; - b1 | b2 | b3 | b4 -------+------+------+------ - 1001 | 0101 | 1001 | 0101 -(1 row) - --- test non-error-throwing API for some core types -SELECT pg_input_is_valid('01010001', 'bit(10)'); - pg_input_is_valid -------------------- - f -(1 row) - -SELECT * FROM pg_input_error_info('01010001', 'bit(10)'); - message | detail | hint | sql_error_code --------------------------------------------------+--------+------+---------------- - bit string length 8 does not match type bit(10) | | | 22026 -(1 row) - -SELECT pg_input_is_valid('01010Z01', 'bit(8)'); - pg_input_is_valid -------------------- - f -(1 row) - -SELECT * FROM pg_input_error_info('01010Z01', 'bit(8)'); - message | detail | hint | sql_error_code ----------------------------------+--------+------+---------------- - "Z" is not a valid binary digit | | | 22P02 -(1 row) - -SELECT pg_input_is_valid('x01010Z01', 'bit(32)'); - pg_input_is_valid -------------------- - f -(1 row) - -SELECT * FROM pg_input_error_info('x01010Z01', 'bit(32)'); - message | detail | hint | sql_error_code ---------------------------------------+--------+------+---------------- - "Z" is not a valid hexadecimal digit | | | 22P02 -(1 row) - -SELECT pg_input_is_valid('01010Z01', 'varbit'); - pg_input_is_valid -------------------- - f -(1 row) - -SELECT * FROM pg_input_error_info('01010Z01', 'varbit'); - message | detail | hint | sql_error_code ----------------------------------+--------+------+---------------- - "Z" is not a valid binary digit | | | 22P02 -(1 row) - -SELECT pg_input_is_valid('x01010Z01', 'varbit'); - pg_input_is_valid -------------------- - f -(1 row) - -SELECT * FROM pg_input_error_info('x01010Z01', 'varbit'); - message | detail | hint | sql_error_code ---------------------------------------+--------+------+---------------- - "Z" is not a valid hexadecimal digit | | | 22P02 -(1 row) - +server closed the connection unexpectedly + This probably means the server terminated abnormally + before or while processing the request. +connection to server was lost diff -U3 /home/dd/projects/postgres/vanilla/planb/postgres/master/src/test/regress/expected/strings.out /home/dd/projects/postgres/vanilla/planb/postgres/master/src/test/regress/results/strings.out --- /home/dd/projects/postgres/vanilla/planb/postgres/master/src/test/regress/expected/strings.out 2025-09-15 23:35:38.174278147 +0300 +++ /home/dd/projects/postgres/vanilla/planb/postgres/master/src/test/regress/results/strings.out 2025-09-16 00:27:00.724744704 +0300 @@ -1,2988 +1,2 @@ --- --- STRINGS --- Test various data entry syntaxes. --- --- SQL string continuation syntax --- E021-03 character string literals -SELECT 'first line' -' - next line' - ' - third line' - AS "Three lines to one"; - Three lines to one -------------------------------------- - first line - next line - third line -(1 row) - --- illegal string continuation syntax -SELECT 'first line' -' - next line' /* this comment is not allowed here */ -' - third line' - AS "Illegal comment within continuation"; -ERROR: syntax error at or near "' - third line'" -LINE 3: ' - third line' - ^ --- Unicode escapes -SET standard_conforming_strings TO on; -SELECT U&'d\0061t\+000061' AS U&"d\0061t\+000061"; - data ------- - data -(1 row) - -SELECT U&'d!0061t\+000061' UESCAPE '!' AS U&"d*0061t\+000061" UESCAPE '*'; - dat\+000061 -------------- - dat\+000061 -(1 row) - -SELECT U&'a\\b' AS "a\b"; - a\b ------ - a\b -(1 row) - -SELECT U&' \' UESCAPE '!' AS "tricky"; - tricky --------- - \ -(1 row) - -SELECT 'tricky' AS U&"\" UESCAPE '!'; - \ --------- - tricky -(1 row) - -SELECT U&'wrong: \061'; -ERROR: invalid Unicode escape -LINE 1: SELECT U&'wrong: \061'; - ^ -HINT: Unicode escapes must be \XXXX or \+XXXXXX. -SELECT U&'wrong: \+0061'; -ERROR: invalid Unicode escape -LINE 1: SELECT U&'wrong: \+0061'; - ^ -HINT: Unicode escapes must be \XXXX or \+XXXXXX. -SELECT U&'wrong: +0061' UESCAPE +; -ERROR: UESCAPE must be followed by a simple string literal at or near "+" -LINE 1: SELECT U&'wrong: +0061' UESCAPE +; - ^ -SELECT U&'wrong: +0061' UESCAPE '+'; -ERROR: invalid Unicode escape character at or near "'+'" -LINE 1: SELECT U&'wrong: +0061' UESCAPE '+'; - ^ -SELECT U&'wrong: \db99'; -ERROR: invalid Unicode surrogate pair -LINE 1: SELECT U&'wrong: \db99'; - ^ -SELECT U&'wrong: \db99xy'; -ERROR: invalid Unicode surrogate pair -LINE 1: SELECT U&'wrong: \db99xy'; - ^ -SELECT U&'wrong: \db99\\'; -ERROR: invalid Unicode surrogate pair -LINE 1: SELECT U&'wrong: \db99\\'; - ^ -SELECT U&'wrong: \db99\0061'; -ERROR: invalid Unicode surrogate pair -LINE 1: SELECT U&'wrong: \db99\0061'; - ^ -SELECT U&'wrong: \+00db99\+000061'; -ERROR: invalid Unicode surrogate pair -LINE 1: SELECT U&'wrong: \+00db99\+000061'; - ^ -SELECT U&'wrong: \+2FFFFF'; -ERROR: invalid Unicode escape value -LINE 1: SELECT U&'wrong: \+2FFFFF'; - ^ --- while we're here, check the same cases in E-style literals -SELECT E'd\u0061t\U00000061' AS "data"; - data ------- - data -(1 row) - -SELECT E'a\\b' AS "a\b"; - a\b ------ - a\b -(1 row) - -SELECT E'wrong: \u061'; -ERROR: invalid Unicode escape -LINE 1: SELECT E'wrong: \u061'; - ^ -HINT: Unicode escapes must be \uXXXX or \UXXXXXXXX. -SELECT E'wrong: \U0061'; -ERROR: invalid Unicode escape -LINE 1: SELECT E'wrong: \U0061'; - ^ -HINT: Unicode escapes must be \uXXXX or \UXXXXXXXX. -SELECT E'wrong: \udb99'; -ERROR: invalid Unicode surrogate pair at or near "'" -LINE 1: SELECT E'wrong: \udb99'; - ^ -SELECT E'wrong: \udb99xy'; -ERROR: invalid Unicode surrogate pair at or near "x" -LINE 1: SELECT E'wrong: \udb99xy'; - ^ -SELECT E'wrong: \udb99\\'; -ERROR: invalid Unicode surrogate pair at or near "\" -LINE 1: SELECT E'wrong: \udb99\\'; - ^ -SELECT E'wrong: \udb99\u0061'; -ERROR: invalid Unicode surrogate pair at or near "\u0061" -LINE 1: SELECT E'wrong: \udb99\u0061'; - ^ -SELECT E'wrong: \U0000db99\U00000061'; -ERROR: invalid Unicode surrogate pair at or near "\U00000061" -LINE 1: SELECT E'wrong: \U0000db99\U00000061'; - ^ -SELECT E'wrong: \U002FFFFF'; -ERROR: invalid Unicode escape value at or near "\U002FFFFF" -LINE 1: SELECT E'wrong: \U002FFFFF'; - ^ -SET standard_conforming_strings TO off; -SELECT U&'d\0061t\+000061' AS U&"d\0061t\+000061"; -ERROR: unsafe use of string constant with Unicode escapes -LINE 1: SELECT U&'d\0061t\+000061' AS U&"d\0061t\+000061"; - ^ -DETAIL: String constants with Unicode escapes cannot be used when "standard_conforming_strings" is off. -SELECT U&'d!0061t\+000061' UESCAPE '!' AS U&"d*0061t\+000061" UESCAPE '*'; -ERROR: unsafe use of string constant with Unicode escapes -LINE 1: SELECT U&'d!0061t\+000061' UESCAPE '!' AS U&"d*0061t\+000061... - ^ -DETAIL: String constants with Unicode escapes cannot be used when "standard_conforming_strings" is off. -SELECT U&' \' UESCAPE '!' AS "tricky"; -ERROR: unsafe use of string constant with Unicode escapes -LINE 1: SELECT U&' \' UESCAPE '!' AS "tricky"; - ^ -DETAIL: String constants with Unicode escapes cannot be used when "standard_conforming_strings" is off. -SELECT 'tricky' AS U&"\" UESCAPE '!'; - \ --------- - tricky -(1 row) - -SELECT U&'wrong: \061'; -ERROR: unsafe use of string constant with Unicode escapes -LINE 1: SELECT U&'wrong: \061'; - ^ -DETAIL: String constants with Unicode escapes cannot be used when "standard_conforming_strings" is off. -SELECT U&'wrong: \+0061'; -ERROR: unsafe use of string constant with Unicode escapes -LINE 1: SELECT U&'wrong: \+0061'; - ^ -DETAIL: String constants with Unicode escapes cannot be used when "standard_conforming_strings" is off. -SELECT U&'wrong: +0061' UESCAPE '+'; -ERROR: unsafe use of string constant with Unicode escapes -LINE 1: SELECT U&'wrong: +0061' UESCAPE '+'; - ^ -DETAIL: String constants with Unicode escapes cannot be used when "standard_conforming_strings" is off. -RESET standard_conforming_strings; --- bytea -SET bytea_output TO hex; -SELECT E'\\xDeAdBeEf'::bytea; - bytea ------------- - \xdeadbeef -(1 row) - -SELECT E'\\x De Ad Be Ef '::bytea; - bytea ------------- - \xdeadbeef -(1 row) - -SELECT E'\\xDeAdBeE'::bytea; -ERROR: invalid hexadecimal data: odd number of digits -LINE 1: SELECT E'\\xDeAdBeE'::bytea; - ^ -SELECT E'\\xDeAdBeEx'::bytea; -ERROR: invalid hexadecimal digit: "x" -LINE 1: SELECT E'\\xDeAdBeEx'::bytea; - ^ -SELECT E'\\xDe00BeEf'::bytea; - bytea ------------- - \xde00beef -(1 row) - -SELECT E'DeAdBeEf'::bytea; - bytea --------------------- - \x4465416442654566 -(1 row) - -SELECT E'De\\000dBeEf'::bytea; - bytea --------------------- - \x4465006442654566 -(1 row) - -SELECT E'De\123dBeEf'::bytea; - bytea --------------------- - \x4465536442654566 -(1 row) - -SELECT E'De\\123dBeEf'::bytea; - bytea --------------------- - \x4465536442654566 -(1 row) - -SELECT E'De\\678dBeEf'::bytea; -ERROR: invalid input syntax for type bytea -LINE 1: SELECT E'De\\678dBeEf'::bytea; - ^ -SELECT E'DeAd\\\\BeEf'::bytea; - bytea ----------------------- - \x446541645c42654566 -(1 row) - -SELECT reverse(''::bytea); - reverse ---------- - \x -(1 row) - -SELECT reverse('\xaa'::bytea); - reverse ---------- - \xaa -(1 row) - -SELECT reverse('\xabcd'::bytea); - reverse ---------- - \xcdab -(1 row) - -SET bytea_output TO escape; -SELECT E'\\xDeAdBeEf'::bytea; - bytea ------------------- - \336\255\276\357 -(1 row) - -SELECT E'\\x De Ad Be Ef '::bytea; - bytea ------------------- - \336\255\276\357 -(1 row) - -SELECT E'\\xDe00BeEf'::bytea; - bytea ------------------- - \336\000\276\357 -(1 row) - -SELECT E'DeAdBeEf'::bytea; - bytea ----------- - DeAdBeEf -(1 row) - -SELECT E'De\\000dBeEf'::bytea; - bytea -------------- - De\000dBeEf -(1 row) - -SELECT E'De\\123dBeEf'::bytea; - bytea ----------- - DeSdBeEf -(1 row) - -SELECT E'DeAd\\\\BeEf'::bytea; - bytea ------------- - DeAd\\BeEf -(1 row) - --- Test non-error-throwing API too -SELECT pg_input_is_valid(E'\\xDeAdBeE', 'bytea'); - pg_input_is_valid -------------------- - f -(1 row) - -SELECT * FROM pg_input_error_info(E'\\xDeAdBeE', 'bytea'); - message | detail | hint | sql_error_code -------------------------------------------------+--------+------+---------------- - invalid hexadecimal data: odd number of digits | | | 22023 -(1 row) - -SELECT * FROM pg_input_error_info(E'\\xDeAdBeEx', 'bytea'); - message | detail | hint | sql_error_code ---------------------------------+--------+------+---------------- - invalid hexadecimal digit: "x" | | | 22023 -(1 row) - -SELECT * FROM pg_input_error_info(E'foo\\99bar', 'bytea'); - message | detail | hint | sql_error_code --------------------------------------+--------+------+---------------- - invalid input syntax for type bytea | | | 22P02 -(1 row) - --- --- test conversions between various string types --- E021-10 implicit casting among the character data types --- -SELECT CAST(f1 AS text) AS "text(char)" FROM CHAR_TBL; - text(char) ------------- - a - ab - abcd - abcd -(4 rows) - -SELECT CAST(f1 AS text) AS "text(varchar)" FROM VARCHAR_TBL; - text(varchar) ---------------- - a - ab - abcd - abcd -(4 rows) - -SELECT CAST(name 'namefield' AS text) AS "text(name)"; - text(name) ------------- - namefield -(1 row) - --- since this is an explicit cast, it should truncate w/o error: -SELECT CAST(f1 AS char(10)) AS "char(text)" FROM TEXT_TBL; - char(text) ------------- - doh! - hi de ho n -(2 rows) - --- note: implicit-cast case is tested in char.sql -SELECT CAST(f1 AS char(20)) AS "char(text)" FROM TEXT_TBL; - char(text) ----------------------- - doh! - hi de ho neighbor -(2 rows) - -SELECT CAST(f1 AS char(10)) AS "char(varchar)" FROM VARCHAR_TBL; - char(varchar) ---------------- - a - ab - abcd - abcd -(4 rows) - -SELECT CAST(name 'namefield' AS char(10)) AS "char(name)"; - char(name) ------------- - namefield -(1 row) - -SELECT CAST(f1 AS varchar) AS "varchar(text)" FROM TEXT_TBL; - varchar(text) -------------------- - doh! - hi de ho neighbor -(2 rows) - -SELECT CAST(f1 AS varchar) AS "varchar(char)" FROM CHAR_TBL; - varchar(char) ---------------- - a - ab - abcd - abcd -(4 rows) - -SELECT CAST(name 'namefield' AS varchar) AS "varchar(name)"; - varchar(name) ---------------- - namefield -(1 row) - --- --- test SQL string functions --- E### and T### are feature reference numbers from SQL99 --- --- E021-09 trim function -SELECT TRIM(BOTH FROM ' bunch o blanks ') = 'bunch o blanks' AS "bunch o blanks"; - bunch o blanks ----------------- - t -(1 row) - -SELECT TRIM(LEADING FROM ' bunch o blanks ') = 'bunch o blanks ' AS "bunch o blanks "; - bunch o blanks ------------------- - t -(1 row) - -SELECT TRIM(TRAILING FROM ' bunch o blanks ') = ' bunch o blanks' AS " bunch o blanks"; - bunch o blanks ------------------- - t -(1 row) - -SELECT TRIM(BOTH 'x' FROM 'xxxxxsome Xsxxxxx') = 'some Xs' AS "some Xs"; - some Xs ---------- - t -(1 row) - --- E021-06 substring expression -SELECT SUBSTRING('1234567890' FROM 3) = '34567890' AS "34567890"; - 34567890 ----------- - t -(1 row) - -SELECT SUBSTRING('1234567890' FROM 4 FOR 3) = '456' AS "456"; - 456 ------ - t -(1 row) - --- test overflow cases -SELECT SUBSTRING('string' FROM 2 FOR 2147483646) AS "tring"; - tring -------- - tring -(1 row) - -SELECT SUBSTRING('string' FROM -10 FOR 2147483646) AS "string"; - string --------- - string -(1 row) - -SELECT SUBSTRING('string' FROM -10 FOR -2147483646) AS "error"; -ERROR: negative substring length not allowed --- T581 regular expression substring (with SQL's bizarre regexp syntax) -SELECT SUBSTRING('abcdefg' SIMILAR 'a#"(b_d)#"%' ESCAPE '#') AS "bcd"; - bcd ------ - bcd -(1 row) - --- obsolete SQL99 syntax -SELECT SUBSTRING('abcdefg' FROM 'a#"(b_d)#"%' FOR '#') AS "bcd"; - bcd ------ - bcd -(1 row) - --- No match should return NULL -SELECT SUBSTRING('abcdefg' SIMILAR '#"(b_d)#"%' ESCAPE '#') IS NULL AS "True"; - True ------- - t -(1 row) - --- Null inputs should return NULL -SELECT SUBSTRING('abcdefg' SIMILAR '%' ESCAPE NULL) IS NULL AS "True"; - True ------- - t -(1 row) - -SELECT SUBSTRING(NULL SIMILAR '%' ESCAPE '#') IS NULL AS "True"; - True ------- - t -(1 row) - -SELECT SUBSTRING('abcdefg' SIMILAR NULL ESCAPE '#') IS NULL AS "True"; - True ------- - t -(1 row) - --- The first and last parts should act non-greedy -SELECT SUBSTRING('abcdefg' SIMILAR 'a#"%#"g' ESCAPE '#') AS "bcdef"; - bcdef -------- - bcdef -(1 row) - -SELECT SUBSTRING('abcdefg' SIMILAR 'a*#"%#"g*' ESCAPE '#') AS "abcdefg"; - abcdefg ---------- - abcdefg -(1 row) - --- Vertical bar in any part affects only that part -SELECT SUBSTRING('abcdefg' SIMILAR 'a|b#"%#"g' ESCAPE '#') AS "bcdef"; - bcdef -------- - bcdef -(1 row) - -SELECT SUBSTRING('abcdefg' SIMILAR 'a#"%#"x|g' ESCAPE '#') AS "bcdef"; - bcdef -------- - bcdef -(1 row) - -SELECT SUBSTRING('abcdefg' SIMILAR 'a#"%|ab#"g' ESCAPE '#') AS "bcdef"; - bcdef -------- - bcdef -(1 row) - --- Can't have more than two part separators -SELECT SUBSTRING('abcdefg' SIMILAR 'a*#"%#"g*#"x' ESCAPE '#') AS "error"; -ERROR: SQL regular expression may not contain more than two escape-double-quote separators -CONTEXT: SQL function "substring" statement 1 --- Postgres extension: with 0 or 1 separator, assume parts 1 and 3 are empty -SELECT SUBSTRING('abcdefg' SIMILAR 'a#"%g' ESCAPE '#') AS "bcdefg"; - bcdefg --------- - bcdefg -(1 row) - -SELECT SUBSTRING('abcdefg' SIMILAR 'a%g' ESCAPE '#') AS "abcdefg"; - abcdefg ---------- - abcdefg -(1 row) - --- substring() with just two arguments is not allowed by SQL spec; --- we accept it, but we interpret the pattern as a POSIX regexp not SQL -SELECT SUBSTRING('abcdefg' FROM 'c.e') AS "cde"; - cde ------ - cde -(1 row) - --- With a parenthesized subexpression, return only what matches the subexpr -SELECT SUBSTRING('abcdefg' FROM 'b(.*)f') AS "cde"; - cde ------ - cde -(1 row) - --- Check case where we have a match, but not a subexpression match -SELECT SUBSTRING('foo' FROM 'foo(bar)?') IS NULL AS t; - t ---- - t -(1 row) - --- Check behavior of SIMILAR TO, which uses largely the same regexp variant -SELECT 'abcdefg' SIMILAR TO '_bcd%' AS true; - true ------- - t -(1 row) - -SELECT 'abcdefg' SIMILAR TO 'bcd%' AS false; - false -------- - f -(1 row) - -SELECT 'abcdefg' SIMILAR TO '_bcd#%' ESCAPE '#' AS false; - false -------- - f -(1 row) - -SELECT 'abcd%' SIMILAR TO '_bcd#%' ESCAPE '#' AS true; - true ------- - t -(1 row) - --- Postgres uses '\' as the default escape character, which is not per spec -SELECT 'abcdefg' SIMILAR TO '_bcd\%' AS false; - false -------- - f -(1 row) - --- and an empty string to mean "no escape", which is also not per spec -SELECT 'abcd\efg' SIMILAR TO '_bcd\%' ESCAPE '' AS true; - true ------- - t -(1 row) - --- these behaviors are per spec, though: -SELECT 'abcdefg' SIMILAR TO '_bcd%' ESCAPE NULL AS null; - null ------- - -(1 row) - -SELECT 'abcdefg' SIMILAR TO '_bcd#%' ESCAPE '##' AS error; -ERROR: invalid escape string -HINT: Escape string must be empty or one character. --- Characters that should be left alone in character classes when a --- SIMILAR TO regexp pattern is converted to POSIX style. --- Underscore "_" -EXPLAIN (COSTS OFF) SELECT * FROM TEXT_TBL WHERE f1 SIMILAR TO '_[_[:alpha:]_]_'; - QUERY PLAN ------------------------------------------------- - Seq Scan on text_tbl - Filter: (f1 ~ '^(?:.[_[:alpha:]_].)$'::text) -(2 rows) - --- Percentage "%" -EXPLAIN (COSTS OFF) SELECT * FROM TEXT_TBL WHERE f1 SIMILAR TO '%[%[:alnum:]%]%'; - QUERY PLAN --------------------------------------------------- - Seq Scan on text_tbl - Filter: (f1 ~ '^(?:.*[%[:alnum:]%].*)$'::text) -(2 rows) - --- Dot "." -EXPLAIN (COSTS OFF) SELECT * FROM TEXT_TBL WHERE f1 SIMILAR TO '.[.[:alnum:].].'; - QUERY PLAN --------------------------------------------------- - Seq Scan on text_tbl - Filter: (f1 ~ '^(?:\.[.[:alnum:].]\.)$'::text) -(2 rows) - --- Dollar "$" -EXPLAIN (COSTS OFF) SELECT * FROM TEXT_TBL WHERE f1 SIMILAR TO '$[$[:alnum:]$]$'; - QUERY PLAN --------------------------------------------------- - Seq Scan on text_tbl - Filter: (f1 ~ '^(?:\$[$[:alnum:]$]\$)$'::text) -(2 rows) - --- Opening parenthesis "(" -EXPLAIN (COSTS OFF) SELECT * FROM TEXT_TBL WHERE f1 SIMILAR TO '()[([:alnum:](]()'; - QUERY PLAN ------------------------------------------------------- - Seq Scan on text_tbl - Filter: (f1 ~ '^(?:(?:)[([:alnum:](](?:))$'::text) -(2 rows) - --- Caret "^" -EXPLAIN (COSTS OFF) SELECT * FROM TEXT_TBL WHERE f1 SIMILAR TO '^[^[:alnum:]^[^^][[^^]][\^][[\^]]\^]^'; - QUERY PLAN ------------------------------------------------------------------------- - Seq Scan on text_tbl - Filter: (f1 ~ '^(?:\^[^[:alnum:]^[^^][[^^]][\^][[\^]]\^]\^)$'::text) -(2 rows) - --- Closing square bracket "]" at the beginning of character class -EXPLAIN (COSTS OFF) SELECT * FROM TEXT_TBL WHERE f1 SIMILAR TO '[]%][^]%][^%]%'; - QUERY PLAN ------------------------------------------------- - Seq Scan on text_tbl - Filter: (f1 ~ '^(?:[]%][^]%][^%].*)$'::text) -(2 rows) - --- Closing square bracket effective after two carets at the beginning --- of character class. -EXPLAIN (COSTS OFF) SELECT * FROM TEXT_TBL WHERE f1 SIMILAR TO '[^^]^'; - QUERY PLAN ---------------------------------------- - Seq Scan on text_tbl - Filter: (f1 ~ '^(?:[^^]\^)$'::text) -(2 rows) - --- Closing square bracket after an escape sequence at the beginning of --- a character closes the character class -EXPLAIN (COSTS OFF) SELECT * FROM TEXT_TBL WHERE f1 SIMILAR TO '[|a]%' ESCAPE '|'; - QUERY PLAN ---------------------------------------- - Seq Scan on text_tbl - Filter: (f1 ~ '^(?:[\a].*)$'::text) -(2 rows) - --- Test backslash escapes in regexp_replace's replacement string -SELECT regexp_replace('1112223333', E'(\\d{3})(\\d{3})(\\d{4})', E'(\\1) \\2-\\3'); - regexp_replace ----------------- - (111) 222-3333 -(1 row) - -SELECT regexp_replace('foobarrbazz', E'(.)\\1', E'X\\&Y', 'g'); - regexp_replace -------------------- - fXooYbaXrrYbaXzzY -(1 row) - -SELECT regexp_replace('foobarrbazz', E'(.)\\1', E'X\\\\Y', 'g'); - regexp_replace ----------------- - fX\YbaX\YbaX\Y -(1 row) - --- not an error, though perhaps it should be: -SELECT regexp_replace('foobarrbazz', E'(.)\\1', E'X\\Y\\1Z\\'); - regexp_replace ------------------ - fX\YoZ\barrbazz -(1 row) - -SELECT regexp_replace('AAA BBB CCC ', E'\\s+', ' ', 'g'); - regexp_replace ----------------- - AAA BBB CCC -(1 row) - -SELECT regexp_replace('AAA', '^|$', 'Z', 'g'); - regexp_replace ----------------- - ZAAAZ -(1 row) - -SELECT regexp_replace('AAA aaa', 'A+', 'Z', 'gi'); - regexp_replace ----------------- - Z Z -(1 row) - --- invalid regexp option -SELECT regexp_replace('AAA aaa', 'A+', 'Z', 'z'); -ERROR: invalid regular expression option: "z" --- extended regexp_replace tests -SELECT regexp_replace('A PostgreSQL function', 'A|e|i|o|u', 'X', 1); - regexp_replace ------------------------ - X PostgreSQL function -(1 row) - -SELECT regexp_replace('A PostgreSQL function', 'A|e|i|o|u', 'X', 1, 2); - regexp_replace ------------------------ - A PXstgreSQL function -(1 row) - -SELECT regexp_replace('A PostgreSQL function', 'a|e|i|o|u', 'X', 1, 0, 'i'); - regexp_replace ------------------------ - X PXstgrXSQL fXnctXXn -(1 row) - -SELECT regexp_replace('A PostgreSQL function', 'a|e|i|o|u', 'X', 1, 1, 'i'); - regexp_replace ------------------------ - X PostgreSQL function -(1 row) - -SELECT regexp_replace('A PostgreSQL function', 'a|e|i|o|u', 'X', 1, 2, 'i'); - regexp_replace ------------------------ - A PXstgreSQL function -(1 row) - -SELECT regexp_replace('A PostgreSQL function', 'a|e|i|o|u', 'X', 1, 3, 'i'); - regexp_replace ------------------------ - A PostgrXSQL function -(1 row) - -SELECT regexp_replace('A PostgreSQL function', 'a|e|i|o|u', 'X', 1, 9, 'i'); - regexp_replace ------------------------ - A PostgreSQL function -(1 row) - -SELECT regexp_replace('A PostgreSQL function', 'A|e|i|o|u', 'X', 7, 0, 'i'); - regexp_replace ------------------------ - A PostgrXSQL fXnctXXn -(1 row) - --- 'g' flag should be ignored when N is specified -SELECT regexp_replace('A PostgreSQL function', 'a|e|i|o|u', 'X', 1, 1, 'g'); - regexp_replace ------------------------ - A PXstgreSQL function -(1 row) - --- errors -SELECT regexp_replace('A PostgreSQL function', 'a|e|i|o|u', 'X', -1, 0, 'i'); -ERROR: invalid value for parameter "start": -1 -SELECT regexp_replace('A PostgreSQL function', 'a|e|i|o|u', 'X', 1, -1, 'i'); -ERROR: invalid value for parameter "n": -1 --- erroneous invocation of non-extended form -SELECT regexp_replace('A PostgreSQL function', 'a|e|i|o|u', 'X', '1'); -ERROR: invalid regular expression option: "1" -HINT: If you meant to use regexp_replace() with a start parameter, cast the fourth argument to integer explicitly. --- regexp_count tests -SELECT regexp_count('123123123123123', '(12)3'); - regexp_count --------------- - 5 -(1 row) - -SELECT regexp_count('123123123123', '123', 1); - regexp_count --------------- - 4 -(1 row) - -SELECT regexp_count('123123123123', '123', 3); - regexp_count --------------- - 3 -(1 row) - -SELECT regexp_count('123123123123', '123', 33); - regexp_count --------------- - 0 -(1 row) - -SELECT regexp_count('ABCABCABCABC', 'Abc', 1, ''); - regexp_count --------------- - 0 -(1 row) - -SELECT regexp_count('ABCABCABCABC', 'Abc', 1, 'i'); - regexp_count --------------- - 4 -(1 row) - --- errors -SELECT regexp_count('123123123123', '123', 0); -ERROR: invalid value for parameter "start": 0 -SELECT regexp_count('123123123123', '123', -3); -ERROR: invalid value for parameter "start": -3 --- regexp_like tests -SELECT regexp_like('Steven', '^Ste(v|ph)en$'); - regexp_like -------------- - t -(1 row) - -SELECT regexp_like('a'||CHR(10)||'d', 'a.d', 'n'); - regexp_like -------------- - f -(1 row) - -SELECT regexp_like('a'||CHR(10)||'d', 'a.d', 's'); - regexp_like -------------- - t -(1 row) - -SELECT regexp_like('abc', ' a . c ', 'x'); - regexp_like -------------- - t -(1 row) - -SELECT regexp_like('abc', 'a.c', 'g'); -- error -ERROR: regexp_like() does not support the "global" option --- regexp_instr tests -SELECT regexp_instr('abcdefghi', 'd.f'); - regexp_instr --------------- - 4 -(1 row) - -SELECT regexp_instr('abcdefghi', 'd.q'); - regexp_instr --------------- - 0 -(1 row) - -SELECT regexp_instr('abcabcabc', 'a.c'); - regexp_instr --------------- - 1 -(1 row) - -SELECT regexp_instr('abcabcabc', 'a.c', 2); - regexp_instr --------------- - 4 -(1 row) - -SELECT regexp_instr('abcabcabc', 'a.c', 1, 3); - regexp_instr --------------- - 7 -(1 row) - -SELECT regexp_instr('abcabcabc', 'a.c', 1, 4); - regexp_instr --------------- - 0 -(1 row) - -SELECT regexp_instr('abcabcabc', 'A.C', 1, 2, 0, 'i'); - regexp_instr --------------- - 4 -(1 row) - -SELECT regexp_instr('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 0); - regexp_instr --------------- - 1 -(1 row) - -SELECT regexp_instr('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 1); - regexp_instr --------------- - 1 -(1 row) - -SELECT regexp_instr('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 2); - regexp_instr --------------- - 4 -(1 row) - -SELECT regexp_instr('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 3); - regexp_instr --------------- - 5 -(1 row) - -SELECT regexp_instr('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 4); - regexp_instr --------------- - 7 -(1 row) - -SELECT regexp_instr('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 5); - regexp_instr --------------- - 0 -(1 row) - -SELECT regexp_instr('1234567890', '(123)(4(56)(78))', 1, 1, 1, 'i', 0); - regexp_instr --------------- - 9 -(1 row) - -SELECT regexp_instr('1234567890', '(123)(4(56)(78))', 1, 1, 1, 'i', 1); - regexp_instr --------------- - 4 -(1 row) - -SELECT regexp_instr('1234567890', '(123)(4(56)(78))', 1, 1, 1, 'i', 2); - regexp_instr --------------- - 9 -(1 row) - -SELECT regexp_instr('1234567890', '(123)(4(56)(78))', 1, 1, 1, 'i', 3); - regexp_instr --------------- - 7 -(1 row) - -SELECT regexp_instr('1234567890', '(123)(4(56)(78))', 1, 1, 1, 'i', 4); - regexp_instr --------------- - 9 -(1 row) - -SELECT regexp_instr('1234567890', '(123)(4(56)(78))', 1, 1, 1, 'i', 5); - regexp_instr --------------- - 0 -(1 row) - --- Check case where we have a match, but not a subexpression match -SELECT regexp_instr('foo', 'foo(bar)?', 1, 1, 0, '', 1); - regexp_instr --------------- - 0 -(1 row) - --- errors -SELECT regexp_instr('abcabcabc', 'a.c', 0, 1); -ERROR: invalid value for parameter "start": 0 -SELECT regexp_instr('abcabcabc', 'a.c', 1, 0); -ERROR: invalid value for parameter "n": 0 -SELECT regexp_instr('abcabcabc', 'a.c', 1, 1, -1); -ERROR: invalid value for parameter "endoption": -1 -SELECT regexp_instr('abcabcabc', 'a.c', 1, 1, 2); -ERROR: invalid value for parameter "endoption": 2 -SELECT regexp_instr('abcabcabc', 'a.c', 1, 1, 0, 'g'); -ERROR: regexp_instr() does not support the "global" option -SELECT regexp_instr('abcabcabc', 'a.c', 1, 1, 0, '', -1); -ERROR: invalid value for parameter "subexpr": -1 --- regexp_substr tests -SELECT regexp_substr('abcdefghi', 'd.f'); - regexp_substr ---------------- - def -(1 row) - -SELECT regexp_substr('abcdefghi', 'd.q') IS NULL AS t; - t ---- - t -(1 row) - -SELECT regexp_substr('abcabcabc', 'a.c'); - regexp_substr ---------------- - abc -(1 row) - -SELECT regexp_substr('abcabcabc', 'a.c', 2); - regexp_substr ---------------- - abc -(1 row) - -SELECT regexp_substr('abcabcabc', 'a.c', 1, 3); - regexp_substr ---------------- - abc -(1 row) - -SELECT regexp_substr('abcabcabc', 'a.c', 1, 4) IS NULL AS t; - t ---- - t -(1 row) - -SELECT regexp_substr('abcabcabc', 'A.C', 1, 2, 'i'); - regexp_substr ---------------- - abc -(1 row) - -SELECT regexp_substr('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 0); - regexp_substr ---------------- - 12345678 -(1 row) - -SELECT regexp_substr('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 1); - regexp_substr ---------------- - 123 -(1 row) - -SELECT regexp_substr('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 2); - regexp_substr ---------------- - 45678 -(1 row) - -SELECT regexp_substr('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 3); - regexp_substr ---------------- - 56 -(1 row) - -SELECT regexp_substr('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 4); - regexp_substr ---------------- - 78 -(1 row) - -SELECT regexp_substr('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 5) IS NULL AS t; - t ---- - t -(1 row) - --- Check case where we have a match, but not a subexpression match -SELECT regexp_substr('foo', 'foo(bar)?', 1, 1, '', 1) IS NULL AS t; - t ---- - t -(1 row) - --- errors -SELECT regexp_substr('abcabcabc', 'a.c', 0, 1); -ERROR: invalid value for parameter "start": 0 -SELECT regexp_substr('abcabcabc', 'a.c', 1, 0); -ERROR: invalid value for parameter "n": 0 -SELECT regexp_substr('abcabcabc', 'a.c', 1, 1, 'g'); -ERROR: regexp_substr() does not support the "global" option -SELECT regexp_substr('abcabcabc', 'a.c', 1, 1, '', -1); -ERROR: invalid value for parameter "subexpr": -1 --- set so we can tell NULL from empty string -\pset null '\\N' --- return all matches from regexp -SELECT regexp_matches('foobarbequebaz', $re$(bar)(beque)$re$); - regexp_matches ----------------- - {bar,beque} -(1 row) - --- test case insensitive -SELECT regexp_matches('foObARbEqUEbAz', $re$(bar)(beque)$re$, 'i'); - regexp_matches ----------------- - {bAR,bEqUE} -(1 row) - --- global option - more than one match -SELECT regexp_matches('foobarbequebazilbarfbonk', $re$(b[^b]+)(b[^b]+)$re$, 'g'); - regexp_matches ----------------- - {bar,beque} - {bazil,barf} -(2 rows) - --- empty capture group (matched empty string) -SELECT regexp_matches('foobarbequebaz', $re$(bar)(.*)(beque)$re$); - regexp_matches ----------------- - {bar,"",beque} -(1 row) - --- no match -SELECT regexp_matches('foobarbequebaz', $re$(bar)(.+)(beque)$re$); - regexp_matches ----------------- -(0 rows) - --- optional capture group did not match, null entry in array -SELECT regexp_matches('foobarbequebaz', $re$(bar)(.+)?(beque)$re$); - regexp_matches ------------------- - {bar,NULL,beque} -(1 row) - --- no capture groups -SELECT regexp_matches('foobarbequebaz', $re$barbeque$re$); - regexp_matches ----------------- - {barbeque} -(1 row) - --- start/end-of-line matches are of zero length -SELECT regexp_matches('foo' || chr(10) || 'bar' || chr(10) || 'bequq' || chr(10) || 'baz', '^', 'mg'); - regexp_matches ----------------- - {""} - {""} - {""} - {""} -(4 rows) - -SELECT regexp_matches('foo' || chr(10) || 'bar' || chr(10) || 'bequq' || chr(10) || 'baz', '$', 'mg'); - regexp_matches ----------------- - {""} - {""} - {""} - {""} -(4 rows) - -SELECT regexp_matches('1' || chr(10) || '2' || chr(10) || '3' || chr(10) || '4' || chr(10), '^.?', 'mg'); - regexp_matches ----------------- - {1} - {2} - {3} - {4} - {""} -(5 rows) - -SELECT regexp_matches(chr(10) || '1' || chr(10) || '2' || chr(10) || '3' || chr(10) || '4' || chr(10), '.?$', 'mg'); - regexp_matches ----------------- - {""} - {1} - {""} - {2} - {""} - {3} - {""} - {4} - {""} - {""} -(10 rows) - -SELECT regexp_matches(chr(10) || '1' || chr(10) || '2' || chr(10) || '3' || chr(10) || '4', '.?$', 'mg'); - regexp_matches ----------------- - {""} - {1} - {""} - {2} - {""} - {3} - {""} - {4} - {""} -(9 rows) - --- give me errors -SELECT regexp_matches('foobarbequebaz', $re$(bar)(beque)$re$, 'gz'); -ERROR: invalid regular expression option: "z" -SELECT regexp_matches('foobarbequebaz', $re$(barbeque$re$); -ERROR: invalid regular expression: parentheses () not balanced -SELECT regexp_matches('foobarbequebaz', $re$(bar)(beque){2,1}$re$); -ERROR: invalid regular expression: invalid repetition count(s) --- split string on regexp -SELECT foo, length(foo) FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', $re$\s+$re$) AS foo; - foo | length --------+-------- - the | 3 - quick | 5 - brown | 5 - fox | 3 - jumps | 5 - over | 4 - the | 3 - lazy | 4 - dog | 3 -(9 rows) - -SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', $re$\s+$re$); - regexp_split_to_array ------------------------------------------------ - {the,quick,brown,fox,jumps,over,the,lazy,dog} -(1 row) - -SELECT foo, length(foo) FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', $re$\s*$re$) AS foo; - foo | length ------+-------- - t | 1 - h | 1 - e | 1 - q | 1 - u | 1 - i | 1 - c | 1 - k | 1 - b | 1 - r | 1 - o | 1 - w | 1 - n | 1 - f | 1 - o | 1 - x | 1 - j | 1 - u | 1 - m | 1 - p | 1 - s | 1 - o | 1 - v | 1 - e | 1 - r | 1 - t | 1 - h | 1 - e | 1 - l | 1 - a | 1 - z | 1 - y | 1 - d | 1 - o | 1 - g | 1 -(35 rows) - -SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', $re$\s*$re$); - regexp_split_to_array -------------------------------------------------------------------------- - {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} -(1 row) - -SELECT foo, length(foo) FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', '') AS foo; - foo | length ------+-------- - t | 1 - h | 1 - e | 1 - | 1 - q | 1 - u | 1 - i | 1 - c | 1 - k | 1 - | 1 - b | 1 - r | 1 - o | 1 - w | 1 - n | 1 - | 1 - f | 1 - o | 1 - x | 1 - | 1 - j | 1 - u | 1 - m | 1 - p | 1 - s | 1 - | 1 - o | 1 - v | 1 - e | 1 - r | 1 - | 1 - t | 1 - h | 1 - e | 1 - | 1 - l | 1 - a | 1 - z | 1 - y | 1 - | 1 - d | 1 - o | 1 - g | 1 -(43 rows) - -SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', ''); - regexp_split_to_array ---------------------------------------------------------------------------------------------------------- - {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} -(1 row) - --- case insensitive -SELECT foo, length(foo) FROM regexp_split_to_table('thE QUick bROWn FOx jUMPs ovEr The lazy dOG', 'e', 'i') AS foo; - foo | length ----------------------------+-------- - th | 2 - QUick bROWn FOx jUMPs ov | 25 - r Th | 4 - lazy dOG | 9 -(4 rows) - -SELECT regexp_split_to_array('thE QUick bROWn FOx jUMPs ovEr The lazy dOG', 'e', 'i'); - regexp_split_to_array ------------------------------------------------------ - {th," QUick bROWn FOx jUMPs ov","r Th"," lazy dOG"} -(1 row) - --- no match of pattern -SELECT foo, length(foo) FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', 'nomatch') AS foo; - foo | length ----------------------------------------------+-------- - the quick brown fox jumps over the lazy dog | 43 -(1 row) - -SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', 'nomatch'); - regexp_split_to_array -------------------------------------------------- - {"the quick brown fox jumps over the lazy dog"} -(1 row) - --- some corner cases -SELECT regexp_split_to_array('123456','1'); - regexp_split_to_array ------------------------ - {"",23456} -(1 row) - -SELECT regexp_split_to_array('123456','6'); - regexp_split_to_array ------------------------ - {12345,""} -(1 row) - -SELECT regexp_split_to_array('123456','.'); - regexp_split_to_array ------------------------- - {"","","","","","",""} -(1 row) - -SELECT regexp_split_to_array('123456',''); - regexp_split_to_array ------------------------ - {1,2,3,4,5,6} -(1 row) - -SELECT regexp_split_to_array('123456','(?:)'); - regexp_split_to_array ------------------------ - {1,2,3,4,5,6} -(1 row) - -SELECT regexp_split_to_array('1',''); - regexp_split_to_array ------------------------ - {1} -(1 row) - --- errors -SELECT foo, length(foo) FROM regexp_split_to_table('thE QUick bROWn FOx jUMPs ovEr The lazy dOG', 'e', 'zippy') AS foo; -ERROR: invalid regular expression option: "z" -SELECT regexp_split_to_array('thE QUick bROWn FOx jUMPs ovEr The lazy dOG', 'e', 'iz'); -ERROR: invalid regular expression option: "z" --- global option meaningless for regexp_split -SELECT foo, length(foo) FROM regexp_split_to_table('thE QUick bROWn FOx jUMPs ovEr The lazy dOG', 'e', 'g') AS foo; -ERROR: regexp_split_to_table() does not support the "global" option -SELECT regexp_split_to_array('thE QUick bROWn FOx jUMPs ovEr The lazy dOG', 'e', 'g'); -ERROR: regexp_split_to_array() does not support the "global" option --- change NULL-display back -\pset null '' --- E021-11 position expression -SELECT POSITION('4' IN '1234567890') = '4' AS "4"; - 4 ---- - t -(1 row) - -SELECT POSITION('5' IN '1234567890') = '5' AS "5"; - 5 ---- - t -(1 row) - -SELECT POSITION('\x11'::bytea IN ''::bytea) = 0 AS "0"; - 0 ---- - t -(1 row) - -SELECT POSITION('\x33'::bytea IN '\x1122'::bytea) = 0 AS "0"; - 0 ---- - t -(1 row) - -SELECT POSITION(''::bytea IN '\x1122'::bytea) = 1 AS "1"; - 1 ---- - t -(1 row) - -SELECT POSITION('\x22'::bytea IN '\x1122'::bytea) = 2 AS "2"; - 2 ---- - t -(1 row) - -SELECT POSITION('\x5678'::bytea IN '\x1234567890'::bytea) = 3 AS "3"; - 3 ---- - t -(1 row) - --- T312 character overlay function -SELECT OVERLAY('abcdef' PLACING '45' FROM 4) AS "abc45f"; - abc45f --------- - abc45f -(1 row) - -SELECT OVERLAY('yabadoo' PLACING 'daba' FROM 5) AS "yabadaba"; - yabadaba ----------- - yabadaba -(1 row) - -SELECT OVERLAY('yabadoo' PLACING 'daba' FROM 5 FOR 0) AS "yabadabadoo"; - yabadabadoo -------------- - yabadabadoo -(1 row) - -SELECT OVERLAY('babosa' PLACING 'ubb' FROM 2 FOR 4) AS "bubba"; - bubba -------- - bubba -(1 row) - --- --- test LIKE --- Be sure to form every test as a LIKE/NOT LIKE pair. --- --- simplest examples --- E061-04 like predicate -SELECT 'hawkeye' LIKE 'h%' AS "true"; - true ------- - t -(1 row) - -SELECT 'hawkeye' NOT LIKE 'h%' AS "false"; - false -------- - f -(1 row) - -SELECT 'hawkeye' LIKE 'H%' AS "false"; - false -------- - f -(1 row) - -SELECT 'hawkeye' NOT LIKE 'H%' AS "true"; - true ------- - t -(1 row) - -SELECT 'hawkeye' LIKE 'indio%' AS "false"; - false -------- - f -(1 row) - -SELECT 'hawkeye' NOT LIKE 'indio%' AS "true"; - true ------- - t -(1 row) - -SELECT 'hawkeye' LIKE 'h%eye' AS "true"; - true ------- - t -(1 row) - -SELECT 'hawkeye' NOT LIKE 'h%eye' AS "false"; - false -------- - f -(1 row) - -SELECT 'indio' LIKE '_ndio' AS "true"; - true ------- - t -(1 row) - -SELECT 'indio' NOT LIKE '_ndio' AS "false"; - false -------- - f -(1 row) - -SELECT 'indio' LIKE 'in__o' AS "true"; - true ------- - t -(1 row) - -SELECT 'indio' NOT LIKE 'in__o' AS "false"; - false -------- - f -(1 row) - -SELECT 'indio' LIKE 'in_o' AS "false"; - false -------- - f -(1 row) - -SELECT 'indio' NOT LIKE 'in_o' AS "true"; - true ------- - t -(1 row) - -SELECT 'abc'::name LIKE '_b_' AS "true"; - true ------- - t -(1 row) - -SELECT 'abc'::name NOT LIKE '_b_' AS "false"; - false -------- - f -(1 row) - -SELECT 'abc'::bytea LIKE '_b_'::bytea AS "true"; - true ------- - t -(1 row) - -SELECT 'abc'::bytea NOT LIKE '_b_'::bytea AS "false"; - false -------- - f -(1 row) - --- unused escape character -SELECT 'hawkeye' LIKE 'h%' ESCAPE '#' AS "true"; - true ------- - t -(1 row) - -SELECT 'hawkeye' NOT LIKE 'h%' ESCAPE '#' AS "false"; - false -------- - f -(1 row) - -SELECT 'indio' LIKE 'ind_o' ESCAPE '$' AS "true"; - true ------- - t -(1 row) - -SELECT 'indio' NOT LIKE 'ind_o' ESCAPE '$' AS "false"; - false -------- - f -(1 row) - --- escape character --- E061-05 like predicate with escape clause -SELECT 'h%' LIKE 'h#%' ESCAPE '#' AS "true"; - true ------- - t -(1 row) - -SELECT 'h%' NOT LIKE 'h#%' ESCAPE '#' AS "false"; - false -------- - f -(1 row) - -SELECT 'h%wkeye' LIKE 'h#%' ESCAPE '#' AS "false"; - false -------- - f -(1 row) - -SELECT 'h%wkeye' NOT LIKE 'h#%' ESCAPE '#' AS "true"; - true ------- - t -(1 row) - -SELECT 'h%wkeye' LIKE 'h#%%' ESCAPE '#' AS "true"; - true ------- - t -(1 row) - -SELECT 'h%wkeye' NOT LIKE 'h#%%' ESCAPE '#' AS "false"; - false -------- - f -(1 row) - -SELECT 'h%awkeye' LIKE 'h#%a%k%e' ESCAPE '#' AS "true"; - true ------- - t -(1 row) - -SELECT 'h%awkeye' NOT LIKE 'h#%a%k%e' ESCAPE '#' AS "false"; - false -------- - f -(1 row) - -SELECT 'indio' LIKE '_ndio' ESCAPE '$' AS "true"; - true ------- - t -(1 row) - -SELECT 'indio' NOT LIKE '_ndio' ESCAPE '$' AS "false"; - false -------- - f -(1 row) - -SELECT 'i_dio' LIKE 'i$_d_o' ESCAPE '$' AS "true"; - true ------- - t -(1 row) - -SELECT 'i_dio' NOT LIKE 'i$_d_o' ESCAPE '$' AS "false"; - false -------- - f -(1 row) - -SELECT 'i_dio' LIKE 'i$_nd_o' ESCAPE '$' AS "false"; - false -------- - f -(1 row) - -SELECT 'i_dio' NOT LIKE 'i$_nd_o' ESCAPE '$' AS "true"; - true ------- - t -(1 row) - -SELECT 'i_dio' LIKE 'i$_d%o' ESCAPE '$' AS "true"; - true ------- - t -(1 row) - -SELECT 'i_dio' NOT LIKE 'i$_d%o' ESCAPE '$' AS "false"; - false -------- - f -(1 row) - -SELECT 'a_c'::bytea LIKE 'a$__'::bytea ESCAPE '$'::bytea AS "true"; - true ------- - t -(1 row) - -SELECT 'a_c'::bytea NOT LIKE 'a$__'::bytea ESCAPE '$'::bytea AS "false"; - false -------- - f -(1 row) - --- escape character same as pattern character -SELECT 'maca' LIKE 'm%aca' ESCAPE '%' AS "true"; - true ------- - t -(1 row) - -SELECT 'maca' NOT LIKE 'm%aca' ESCAPE '%' AS "false"; - false -------- - f -(1 row) - -SELECT 'ma%a' LIKE 'm%a%%a' ESCAPE '%' AS "true"; - true ------- - t -(1 row) - -SELECT 'ma%a' NOT LIKE 'm%a%%a' ESCAPE '%' AS "false"; - false -------- - f -(1 row) - -SELECT 'bear' LIKE 'b_ear' ESCAPE '_' AS "true"; - true ------- - t -(1 row) - -SELECT 'bear' NOT LIKE 'b_ear' ESCAPE '_' AS "false"; - false -------- - f -(1 row) - -SELECT 'be_r' LIKE 'b_e__r' ESCAPE '_' AS "true"; - true ------- - t -(1 row) - -SELECT 'be_r' NOT LIKE 'b_e__r' ESCAPE '_' AS "false"; - false -------- - f -(1 row) - -SELECT 'be_r' LIKE '__e__r' ESCAPE '_' AS "false"; - false -------- - f -(1 row) - -SELECT 'be_r' NOT LIKE '__e__r' ESCAPE '_' AS "true"; - true ------- - t -(1 row) - --- --- test ILIKE (case-insensitive LIKE) --- Be sure to form every test as an ILIKE/NOT ILIKE pair. --- -SELECT 'hawkeye' ILIKE 'h%' AS "true"; - true ------- - t -(1 row) - -SELECT 'hawkeye' NOT ILIKE 'h%' AS "false"; - false -------- - f -(1 row) - -SELECT 'hawkeye' ILIKE 'H%' AS "true"; - true ------- - t -(1 row) - -SELECT 'hawkeye' NOT ILIKE 'H%' AS "false"; - false -------- - f -(1 row) - -SELECT 'hawkeye' ILIKE 'H%Eye' AS "true"; - true ------- - t -(1 row) - -SELECT 'hawkeye' NOT ILIKE 'H%Eye' AS "false"; - false -------- - f -(1 row) - -SELECT 'Hawkeye' ILIKE 'h%' AS "true"; - true ------- - t -(1 row) - -SELECT 'Hawkeye' NOT ILIKE 'h%' AS "false"; - false -------- - f -(1 row) - -SELECT 'ABC'::name ILIKE '_b_' AS "true"; - true ------- - t -(1 row) - -SELECT 'ABC'::name NOT ILIKE '_b_' AS "false"; - false -------- - f -(1 row) - --- --- test %/_ combination cases, cf bugs #4821 and #5478 --- -SELECT 'foo' LIKE '_%' as t, 'f' LIKE '_%' as t, '' LIKE '_%' as f; - t | t | f ----+---+--- - t | t | f -(1 row) - -SELECT 'foo' LIKE '%_' as t, 'f' LIKE '%_' as t, '' LIKE '%_' as f; - t | t | f ----+---+--- - t | t | f -(1 row) - -SELECT 'foo' LIKE '__%' as t, 'foo' LIKE '___%' as t, 'foo' LIKE '____%' as f; - t | t | f ----+---+--- - t | t | f -(1 row) - -SELECT 'foo' LIKE '%__' as t, 'foo' LIKE '%___' as t, 'foo' LIKE '%____' as f; - t | t | f ----+---+--- - t | t | f -(1 row) - -SELECT 'jack' LIKE '%____%' AS t; - t ---- - t -(1 row) - --- --- basic tests of LIKE with indexes --- -CREATE TABLE texttest (a text PRIMARY KEY, b int); -SELECT * FROM texttest WHERE a LIKE '%1%'; - a | b ----+--- -(0 rows) - -CREATE TABLE byteatest (a bytea PRIMARY KEY, b int); -SELECT * FROM byteatest WHERE a LIKE '%1%'; - a | b ----+--- -(0 rows) - -DROP TABLE texttest, byteatest; --- --- test implicit type conversion --- --- E021-07 character concatenation -SELECT 'unknown' || ' and unknown' AS "Concat unknown types"; - Concat unknown types ----------------------- - unknown and unknown -(1 row) - -SELECT text 'text' || ' and unknown' AS "Concat text to unknown type"; - Concat text to unknown type ------------------------------ - text and unknown -(1 row) - -SELECT char(20) 'characters' || ' and text' AS "Concat char to unknown type"; - Concat char to unknown type ------------------------------ - characters and text -(1 row) - -SELECT text 'text' || char(20) ' and characters' AS "Concat text to char"; - Concat text to char ---------------------- - text and characters -(1 row) - -SELECT text 'text' || varchar ' and varchar' AS "Concat text to varchar"; - Concat text to varchar ------------------------- - text and varchar -(1 row) - --- --- test substr with toasted text values --- -CREATE TABLE toasttest(f1 text); -insert into toasttest values(repeat('1234567890',10000)); -insert into toasttest values(repeat('1234567890',10000)); --- --- Ensure that some values are uncompressed, to test the faster substring --- operation used in that case --- -alter table toasttest alter column f1 set storage external; -insert into toasttest values(repeat('1234567890',10000)); -insert into toasttest values(repeat('1234567890',10000)); --- If the starting position is zero or less, then return from the start of the string --- adjusting the length to be consistent with the "negative start" per SQL. -SELECT substr(f1, -1, 5) from toasttest; - substr --------- - 123 - 123 - 123 - 123 -(4 rows) - --- If the length is less than zero, an ERROR is thrown. -SELECT substr(f1, 5, -1) from toasttest; -ERROR: negative substring length not allowed --- If no third argument (length) is provided, the length to the end of the --- string is assumed. -SELECT substr(f1, 99995) from toasttest; - substr --------- - 567890 - 567890 - 567890 - 567890 -(4 rows) - --- If start plus length is > string length, the result is truncated to --- string length -SELECT substr(f1, 99995, 10) from toasttest; - substr --------- - 567890 - 567890 - 567890 - 567890 -(4 rows) - -TRUNCATE TABLE toasttest; -INSERT INTO toasttest values (repeat('1234567890',300)); -INSERT INTO toasttest values (repeat('1234567890',300)); -INSERT INTO toasttest values (repeat('1234567890',300)); -INSERT INTO toasttest values (repeat('1234567890',300)); --- expect >0 blocks -SELECT pg_relation_size(reltoastrelid) = 0 AS is_empty - FROM pg_class where relname = 'toasttest'; - is_empty ----------- - f -(1 row) - -TRUNCATE TABLE toasttest; -ALTER TABLE toasttest set (toast_tuple_target = 4080); -INSERT INTO toasttest values (repeat('1234567890',300)); -INSERT INTO toasttest values (repeat('1234567890',300)); -INSERT INTO toasttest values (repeat('1234567890',300)); -INSERT INTO toasttest values (repeat('1234567890',300)); --- expect 0 blocks -SELECT pg_relation_size(reltoastrelid) = 0 AS is_empty - FROM pg_class where relname = 'toasttest'; - is_empty ----------- - t -(1 row) - -DROP TABLE toasttest; --- --- test substr with toasted bytea values --- -CREATE TABLE toasttest(f1 bytea); -insert into toasttest values(decode(repeat('1234567890',10000),'escape')); -insert into toasttest values(decode(repeat('1234567890',10000),'escape')); --- --- Ensure that some values are uncompressed, to test the faster substring --- operation used in that case --- -alter table toasttest alter column f1 set storage external; -insert into toasttest values(decode(repeat('1234567890',10000),'escape')); -insert into toasttest values(decode(repeat('1234567890',10000),'escape')); --- If the starting position is zero or less, then return from the start of the string --- adjusting the length to be consistent with the "negative start" per SQL. -SELECT substr(f1, -1, 5) from toasttest; - substr --------- - 123 - 123 - 123 - 123 -(4 rows) - --- If the length is less than zero, an ERROR is thrown. -SELECT substr(f1, 5, -1) from toasttest; -ERROR: negative substring length not allowed --- If no third argument (length) is provided, the length to the end of the --- string is assumed. -SELECT substr(f1, 99995) from toasttest; - substr --------- - 567890 - 567890 - 567890 - 567890 -(4 rows) - --- If start plus length is > string length, the result is truncated to --- string length -SELECT substr(f1, 99995, 10) from toasttest; - substr --------- - 567890 - 567890 - 567890 - 567890 -(4 rows) - -DROP TABLE toasttest; --- test internally compressing datums --- this tests compressing a datum to a very small size which exercises a --- corner case in packed-varlena handling: even though small, the compressed --- datum must be given a 4-byte header because there are no bits to indicate --- compression in a 1-byte header -CREATE TABLE toasttest (c char(4096)); -INSERT INTO toasttest VALUES('x'); -SELECT length(c), c::text FROM toasttest; - length | c ---------+--- - 1 | x -(1 row) - -SELECT c FROM toasttest; - c ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - x -(1 row) - -DROP TABLE toasttest; --- test with short varlenas (up to 126 data bytes reduced to a 1-byte header) --- being toasted. -CREATE TABLE toasttest (f1 text, f2 text); -ALTER TABLE toasttest SET (toast_tuple_target = 128); -ALTER TABLE toasttest ALTER COLUMN f1 SET STORAGE EXTERNAL; -ALTER TABLE toasttest ALTER COLUMN f2 SET STORAGE EXTERNAL; --- Here, the first value is a varlena large enough to make it toasted and --- stored uncompressed. The second value is a short varlena, toasted --- and stored uncompressed. -INSERT INTO toasttest values(repeat('1234', 1000), repeat('5678', 30)); -SELECT reltoastrelid::regclass AS reltoastname FROM pg_class - WHERE oid = 'toasttest'::regclass \gset --- There should be two values inserted in the toast relation. -SELECT count(*) FROM :reltoastname WHERE chunk_seq = 0; - count -------- - 2 -(1 row) - -SELECT substr(f1, 5, 10) AS f1_data, substr(f2, 5, 10) AS f2_data - FROM toasttest; - f1_data | f2_data -------------+------------ - 1234123412 | 5678567856 -(1 row) - -SELECT pg_column_compression(f1) AS f1_comp, pg_column_compression(f2) AS f2_comp - FROM toasttest; - f1_comp | f2_comp ----------+--------- - | -(1 row) - -DROP TABLE toasttest; --- --- test length --- -SELECT length('abcdef') AS "length_6"; - length_6 ----------- - 6 -(1 row) - --- --- test strpos --- -SELECT strpos('abcdef', 'cd') AS "pos_3"; - pos_3 -------- - 3 -(1 row) - -SELECT strpos('abcdef', 'xy') AS "pos_0"; - pos_0 -------- - 0 -(1 row) - -SELECT strpos('abcdef', '') AS "pos_1"; - pos_1 -------- - 1 -(1 row) - -SELECT strpos('', 'xy') AS "pos_0"; - pos_0 -------- - 0 -(1 row) - -SELECT strpos('', '') AS "pos_1"; - pos_1 -------- - 1 -(1 row) - --- --- test replace --- -SELECT replace('abcdef', 'de', '45') AS "abc45f"; - abc45f --------- - abc45f -(1 row) - -SELECT replace('yabadabadoo', 'ba', '123') AS "ya123da123doo"; - ya123da123doo ---------------- - ya123da123doo -(1 row) - -SELECT replace('yabadoo', 'bad', '') AS "yaoo"; - yaoo ------- - yaoo -(1 row) - --- --- test split_part --- -select split_part('','@',1) AS "empty string"; - empty string --------------- - -(1 row) - -select split_part('','@',-1) AS "empty string"; - empty string --------------- - -(1 row) - -select split_part('joeuser@mydatabase','',1) AS "joeuser@mydatabase"; - joeuser@mydatabase --------------------- - joeuser@mydatabase -(1 row) - -select split_part('joeuser@mydatabase','',2) AS "empty string"; - empty string --------------- - -(1 row) - -select split_part('joeuser@mydatabase','',-1) AS "joeuser@mydatabase"; - joeuser@mydatabase --------------------- - joeuser@mydatabase -(1 row) - -select split_part('joeuser@mydatabase','',-2) AS "empty string"; - empty string --------------- - -(1 row) - -select split_part('joeuser@mydatabase','@',0) AS "an error"; -ERROR: field position must not be zero -select split_part('joeuser@mydatabase','@@',1) AS "joeuser@mydatabase"; - joeuser@mydatabase --------------------- - joeuser@mydatabase -(1 row) - -select split_part('joeuser@mydatabase','@@',2) AS "empty string"; - empty string --------------- - -(1 row) - -select split_part('joeuser@mydatabase','@',1) AS "joeuser"; - joeuser ---------- - joeuser -(1 row) - -select split_part('joeuser@mydatabase','@',2) AS "mydatabase"; - mydatabase ------------- - mydatabase -(1 row) - -select split_part('joeuser@mydatabase','@',3) AS "empty string"; - empty string --------------- - -(1 row) - -select split_part('@joeuser@mydatabase@','@',2) AS "joeuser"; - joeuser ---------- - joeuser -(1 row) - -select split_part('joeuser@mydatabase','@',-1) AS "mydatabase"; - mydatabase ------------- - mydatabase -(1 row) - -select split_part('joeuser@mydatabase','@',-2) AS "joeuser"; - joeuser ---------- - joeuser -(1 row) - -select split_part('joeuser@mydatabase','@',-3) AS "empty string"; - empty string --------------- - -(1 row) - -select split_part('@joeuser@mydatabase@','@',-2) AS "mydatabase"; - mydatabase ------------- - mydatabase -(1 row) - --- --- test to_bin, to_oct, and to_hex --- -select to_bin(-1234) AS "11111111111111111111101100101110"; - 11111111111111111111101100101110 ----------------------------------- - 11111111111111111111101100101110 -(1 row) - -select to_bin(-1234::bigint); - to_bin ------------------------------------------------------------------- - 1111111111111111111111111111111111111111111111111111101100101110 -(1 row) - -select to_bin(256*256*256 - 1) AS "111111111111111111111111"; - 111111111111111111111111 --------------------------- - 111111111111111111111111 -(1 row) - -select to_bin(256::bigint*256::bigint*256::bigint*256::bigint - 1) AS "11111111111111111111111111111111"; - 11111111111111111111111111111111 ----------------------------------- - 11111111111111111111111111111111 -(1 row) - -select to_oct(-1234) AS "37777775456"; - 37777775456 -------------- - 37777775456 -(1 row) - -select to_oct(-1234::bigint) AS "1777777777777777775456"; - 1777777777777777775456 ------------------------- - 1777777777777777775456 -(1 row) - -select to_oct(256*256*256 - 1) AS "77777777"; - 77777777 ----------- - 77777777 -(1 row) - -select to_oct(256::bigint*256::bigint*256::bigint*256::bigint - 1) AS "37777777777"; - 37777777777 -------------- - 37777777777 -(1 row) - -select to_hex(-1234) AS "fffffb2e"; - fffffb2e ----------- - fffffb2e -(1 row) - -select to_hex(-1234::bigint) AS "fffffffffffffb2e"; - fffffffffffffb2e ------------------- - fffffffffffffb2e -(1 row) - -select to_hex(256*256*256 - 1) AS "ffffff"; - ffffff --------- - ffffff -(1 row) - -select to_hex(256::bigint*256::bigint*256::bigint*256::bigint - 1) AS "ffffffff"; - ffffffff ----------- - ffffffff -(1 row) - --- --- SHA-2 --- -SET bytea_output TO hex; -SELECT sha224(''); - sha224 ------------------------------------------------------------- - \xd14a028c2a3a2bc9476102bb288234c415a2b01f828ea62ac5b3e42f -(1 row) - -SELECT sha224('The quick brown fox jumps over the lazy dog.'); - sha224 ------------------------------------------------------------- - \x619cba8e8e05826e9b8c519c0a5c68f4fb653e8a3d8aa04bb2c8cd4c -(1 row) - -SELECT sha256(''); - sha256 --------------------------------------------------------------------- - \xe3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855 -(1 row) - -SELECT sha256('The quick brown fox jumps over the lazy dog.'); - sha256 --------------------------------------------------------------------- - \xef537f25c895bfa782526529a9b63d97aa631564d5d789c2b765448c8635fb6c -(1 row) - -SELECT sha384(''); - sha384 ----------------------------------------------------------------------------------------------------- - \x38b060a751ac96384cd9327eb1b1e36a21fdb71114be07434c0cc7bf63f6e1da274edebfe76f65fbd51ad2f14898b95b -(1 row) - -SELECT sha384('The quick brown fox jumps over the lazy dog.'); - sha384 ----------------------------------------------------------------------------------------------------- - \xed892481d8272ca6df370bf706e4d7bc1b5739fa2177aae6c50e946678718fc67a7af2819a021c2fc34e91bdb63409d7 -(1 row) - -SELECT sha512(''); - sha512 ------------------------------------------------------------------------------------------------------------------------------------- - \xcf83e1357eefb8bdf1542850d66d8007d620e4050b5715dc83f4a921d36ce9ce47d0d13c5d85f2b0ff8318d2877eec2f63b931bd47417a81a538327af927da3e -(1 row) - -SELECT sha512('The quick brown fox jumps over the lazy dog.'); - sha512 ------------------------------------------------------------------------------------------------------------------------------------- - \x91ea1245f20d46ae9a037a989f54f1f790f0a47607eeb8a14d12890cea77a1bbc6c7ed9cf205e67b7f2b8fd4c7dfd3a7a8617e45f3c463d481c7e586c39ac1ed -(1 row) - --- --- CRC --- -SELECT crc32(''); - crc32 -------- - 0 -(1 row) - -SELECT crc32('The quick brown fox jumps over the lazy dog.'); - crc32 ------------- - 1368401385 -(1 row) - -SELECT crc32c(''); - crc32c --------- - 0 -(1 row) - -SELECT crc32c('The quick brown fox jumps over the lazy dog.'); - crc32c ------------ - 419469235 -(1 row) - -SELECT crc32c(repeat('A', 127)::bytea); - crc32c ------------ - 291820082 -(1 row) - -SELECT crc32c(repeat('A', 128)::bytea); - crc32c ------------ - 816091258 -(1 row) - -SELECT crc32c(repeat('A', 129)::bytea); - crc32c ------------- - 4213642571 -(1 row) - -SELECT crc32c(repeat('A', 800)::bytea); - crc32c ------------- - 3134039419 -(1 row) - --- --- encode/decode --- -SELECT encode('\x1234567890abcdef00', 'hex'); - encode --------------------- - 1234567890abcdef00 -(1 row) - -SELECT decode('1234567890abcdef00', 'hex'); - decode ----------------------- - \x1234567890abcdef00 -(1 row) - -SELECT encode(('\x' || repeat('1234567890abcdef0001', 7))::bytea, 'base64'); - encode ------------------------------------------------------------------------------- - EjRWeJCrze8AARI0VniQq83vAAESNFZ4kKvN7wABEjRWeJCrze8AARI0VniQq83vAAESNFZ4kKvN+ - 7wABEjRWeJCrze8AAQ== -(1 row) - -SELECT decode(encode(('\x' || repeat('1234567890abcdef0001', 7))::bytea, - 'base64'), 'base64'); - decode ------------------------------------------------------------------------------------------------------------------------------------------------- - \x1234567890abcdef00011234567890abcdef00011234567890abcdef00011234567890abcdef00011234567890abcdef00011234567890abcdef00011234567890abcdef0001 -(1 row) - -SELECT encode('\x1234567890abcdef00', 'escape'); - encode ------------------------------ - \x124Vx\220\253\315\357\000 -(1 row) - -SELECT decode(encode('\x1234567890abcdef00', 'escape'), 'escape'); - decode ----------------------- - \x1234567890abcdef00 -(1 row) - --- --- get_bit/set_bit etc --- -SELECT get_bit('\x1234567890abcdef00'::bytea, 43); - get_bit ---------- - 1 -(1 row) - -SELECT get_bit('\x1234567890abcdef00'::bytea, 99); -- error -ERROR: index 99 out of valid range, 0..71 -SELECT set_bit('\x1234567890abcdef00'::bytea, 43, 0); - set_bit ----------------------- - \x1234567890a3cdef00 -(1 row) - -SELECT set_bit('\x1234567890abcdef00'::bytea, 99, 0); -- error -ERROR: index 99 out of valid range, 0..71 -SELECT get_byte('\x1234567890abcdef00'::bytea, 3); - get_byte ----------- - 120 -(1 row) - -SELECT get_byte('\x1234567890abcdef00'::bytea, 99); -- error -ERROR: index 99 out of valid range, 0..8 -SELECT set_byte('\x1234567890abcdef00'::bytea, 7, 11); - set_byte ----------------------- - \x1234567890abcd0b00 -(1 row) - -SELECT set_byte('\x1234567890abcdef00'::bytea, 99, 11); -- error -ERROR: index 99 out of valid range, 0..8 --- --- conversions between bytea and integer types --- -SELECT 0x1234::int2::bytea AS "\x1234", (-0x1234)::int2::bytea AS "\xedcc"; - \x1234 | \xedcc ---------+-------- - \x1234 | \xedcc -(1 row) - -SELECT 0x12345678::int4::bytea AS "\x12345678", (-0x12345678)::int4::bytea AS "\xedcba988"; - \x12345678 | \xedcba988 -------------+------------ - \x12345678 | \xedcba988 -(1 row) - -SELECT 0x1122334455667788::int8::bytea AS "\x1122334455667788", - (-0x1122334455667788)::int8::bytea AS "\xeeddccbbaa998878"; - \x1122334455667788 | \xeeddccbbaa998878 ---------------------+-------------------- - \x1122334455667788 | \xeeddccbbaa998878 -(1 row) - -SELECT ''::bytea::int2 AS "0"; - 0 ---- - 0 -(1 row) - -SELECT '\x12'::bytea::int2 AS "18"; - 18 ----- - 18 -(1 row) - -SELECT '\x1234'::bytea::int2 AS "4460"; - 4460 ------- - 4660 -(1 row) - -SELECT '\x123456'::bytea::int2; -- error -ERROR: smallint out of range -SELECT ''::bytea::int4 AS "0"; - 0 ---- - 0 -(1 row) - -SELECT '\x12'::bytea::int4 AS "18"; - 18 ----- - 18 -(1 row) - -SELECT '\x12345678'::bytea::int4 AS "305419896"; - 305419896 ------------ - 305419896 -(1 row) - -SELECT '\x123456789A'::bytea::int4; -- error -ERROR: integer out of range -SELECT ''::bytea::int8 AS "0"; - 0 ---- - 0 -(1 row) - -SELECT '\x12'::bytea::int8 AS "18"; - 18 ----- - 18 -(1 row) - -SELECT '\x1122334455667788'::bytea::int8 AS "1234605616436508552"; - 1234605616436508552 ---------------------- - 1234605616436508552 -(1 row) - -SELECT '\x112233445566778899'::bytea::int8; -- error -ERROR: bigint out of range --- min/max integer values -SELECT '\x8000'::bytea::int2 AS "-32768", '\x7FFF'::bytea::int2 AS "32767"; - -32768 | 32767 ---------+------- - -32768 | 32767 -(1 row) - -SELECT '\x80000000'::bytea::int4 AS "-2147483648", '\x7FFFFFFF'::bytea::int4 AS "2147483647"; - -2147483648 | 2147483647 --------------+------------ - -2147483648 | 2147483647 -(1 row) - -SELECT '\x8000000000000000'::bytea::int8 AS "-9223372036854775808", - '\x7FFFFFFFFFFFFFFF'::bytea::int8 AS "9223372036854775807"; - -9223372036854775808 | 9223372036854775807 -----------------------+--------------------- - -9223372036854775808 | 9223372036854775807 -(1 row) - --- --- test behavior of escape_string_warning and standard_conforming_strings options --- -set escape_string_warning = off; -set standard_conforming_strings = off; -show escape_string_warning; - escape_string_warning ------------------------ - off -(1 row) - -show standard_conforming_strings; - standard_conforming_strings ------------------------------ - off -(1 row) - -set escape_string_warning = on; -set standard_conforming_strings = on; -show escape_string_warning; - escape_string_warning ------------------------ - on -(1 row) - -show standard_conforming_strings; - standard_conforming_strings ------------------------------ - on -(1 row) - -select 'a\bcd' as f1, 'a\b''cd' as f2, 'a\b''''cd' as f3, 'abcd\' as f4, 'ab\''cd' as f5, '\\' as f6; - f1 | f2 | f3 | f4 | f5 | f6 --------+--------+---------+-------+--------+---- - a\bcd | a\b'cd | a\b''cd | abcd\ | ab\'cd | \\ -(1 row) - -set standard_conforming_strings = off; -select 'a\\bcd' as f1, 'a\\b\'cd' as f2, 'a\\b\'''cd' as f3, 'abcd\\' as f4, 'ab\\\'cd' as f5, '\\\\' as f6; -WARNING: nonstandard use of \\ in a string literal -LINE 1: select 'a\\bcd' as f1, 'a\\b\'cd' as f2, 'a\\b\'''cd' as f3,... - ^ -HINT: Use the escape string syntax for backslashes, e.g., E'\\'. -WARNING: nonstandard use of \\ in a string literal -LINE 1: select 'a\\bcd' as f1, 'a\\b\'cd' as f2, 'a\\b\'''cd' as f3,... - ^ -HINT: Use the escape string syntax for backslashes, e.g., E'\\'. -WARNING: nonstandard use of \\ in a string literal -LINE 1: select 'a\\bcd' as f1, 'a\\b\'cd' as f2, 'a\\b\'''cd' as f3,... - ^ -HINT: Use the escape string syntax for backslashes, e.g., E'\\'. -WARNING: nonstandard use of \\ in a string literal -LINE 1: ...bcd' as f1, 'a\\b\'cd' as f2, 'a\\b\'''cd' as f3, 'abcd\\' ... - ^ -HINT: Use the escape string syntax for backslashes, e.g., E'\\'. -WARNING: nonstandard use of \\ in a string literal -LINE 1: ...'cd' as f2, 'a\\b\'''cd' as f3, 'abcd\\' as f4, 'ab\\\'cd'... - ^ -HINT: Use the escape string syntax for backslashes, e.g., E'\\'. -WARNING: nonstandard use of \\ in a string literal -LINE 1: ...'''cd' as f3, 'abcd\\' as f4, 'ab\\\'cd' as f5, '\\\\' as ... - ^ -HINT: Use the escape string syntax for backslashes, e.g., E'\\'. - f1 | f2 | f3 | f4 | f5 | f6 --------+--------+---------+-------+--------+---- - a\bcd | a\b'cd | a\b''cd | abcd\ | ab\'cd | \\ -(1 row) - -set escape_string_warning = off; -set standard_conforming_strings = on; -select 'a\bcd' as f1, 'a\b''cd' as f2, 'a\b''''cd' as f3, 'abcd\' as f4, 'ab\''cd' as f5, '\\' as f6; - f1 | f2 | f3 | f4 | f5 | f6 --------+--------+---------+-------+--------+---- - a\bcd | a\b'cd | a\b''cd | abcd\ | ab\'cd | \\ -(1 row) - -set standard_conforming_strings = off; -select 'a\\bcd' as f1, 'a\\b\'cd' as f2, 'a\\b\'''cd' as f3, 'abcd\\' as f4, 'ab\\\'cd' as f5, '\\\\' as f6; - f1 | f2 | f3 | f4 | f5 | f6 --------+--------+---------+-------+--------+---- - a\bcd | a\b'cd | a\b''cd | abcd\ | ab\'cd | \\ -(1 row) - -reset standard_conforming_strings; --- --- Additional string functions --- -SET bytea_output TO escape; -SELECT initcap('hi THOMAS'); - initcap ------------ - Hi Thomas -(1 row) - -SELECT lpad('hi', 5, 'xy'); - lpad -------- - xyxhi -(1 row) - -SELECT lpad('hi', 5); - lpad -------- - hi -(1 row) - -SELECT lpad('hi', -5, 'xy'); - lpad ------- - -(1 row) - -SELECT lpad('hello', 2); - lpad ------- - he -(1 row) - -SELECT lpad('hi', 5, ''); - lpad ------- - hi -(1 row) - -SELECT rpad('hi', 5, 'xy'); - rpad -------- - hixyx -(1 row) - -SELECT rpad('hi', 5); - rpad -------- - hi -(1 row) - -SELECT rpad('hi', -5, 'xy'); - rpad ------- - -(1 row) - -SELECT rpad('hello', 2); - rpad ------- - he -(1 row) - -SELECT rpad('hi', 5, ''); - rpad ------- - hi -(1 row) - -SELECT ltrim('zzzytrim', 'xyz'); - ltrim -------- - trim -(1 row) - -SELECT translate('', '14', 'ax'); - translate ------------ - -(1 row) - -SELECT translate('12345', '14', 'ax'); - translate ------------ - a23x5 -(1 row) - -SELECT translate('12345', '134', 'a'); - translate ------------ - a25 -(1 row) - -SELECT ascii('x'); - ascii -------- - 120 -(1 row) - -SELECT ascii(''); - ascii -------- - 0 -(1 row) - -SELECT chr(65); - chr ------ - A -(1 row) - -SELECT chr(0); -ERROR: null character not permitted -SELECT repeat('Pg', 4); - repeat ----------- - PgPgPgPg -(1 row) - -SELECT repeat('Pg', -4); - repeat --------- - -(1 row) - -SELECT SUBSTRING('1234567890'::bytea FROM 3) "34567890"; - 34567890 ----------- - 34567890 -(1 row) - -SELECT SUBSTRING('1234567890'::bytea FROM 4 FOR 3) AS "456"; - 456 ------ - 456 -(1 row) - -SELECT SUBSTRING('string'::bytea FROM 2 FOR 2147483646) AS "tring"; - tring -------- - tring -(1 row) - -SELECT SUBSTRING('string'::bytea FROM -10 FOR 2147483646) AS "string"; - string --------- - string -(1 row) - -SELECT SUBSTRING('string'::bytea FROM -10 FOR -2147483646) AS "error"; -ERROR: negative substring length not allowed -SELECT trim(E'\\000'::bytea from E'\\000Tom\\000'::bytea); - btrim -------- - Tom -(1 row) - -SELECT trim(leading E'\\000'::bytea from E'\\000Tom\\000'::bytea); - ltrim ---------- - Tom\000 -(1 row) - -SELECT trim(trailing E'\\000'::bytea from E'\\000Tom\\000'::bytea); - rtrim ---------- - \000Tom -(1 row) - -SELECT btrim(E'\\000trim\\000'::bytea, E'\\000'::bytea); - btrim -------- - trim -(1 row) - -SELECT btrim(''::bytea, E'\\000'::bytea); - btrim -------- - -(1 row) - -SELECT btrim(E'\\000trim\\000'::bytea, ''::bytea); - btrim --------------- - \000trim\000 -(1 row) - -SELECT encode(overlay(E'Th\\000omas'::bytea placing E'Th\\001omas'::bytea from 2),'escape'); - encode -------------- - TTh\x01omas -(1 row) - -SELECT encode(overlay(E'Th\\000omas'::bytea placing E'\\002\\003'::bytea from 8),'escape'); - encode --------------------- - Th\000omas\x02\x03 -(1 row) - -SELECT encode(overlay(E'Th\\000omas'::bytea placing E'\\002\\003'::bytea from 5 for 3),'escape'); - encode ------------------ - Th\000o\x02\x03 -(1 row) - -SELECT bit_count('\x1234567890'::bytea); - bit_count ------------ - 15 -(1 row) - -SELECT unistr('\0064at\+0000610'); - unistr --------- - data0 -(1 row) - -SELECT unistr('d\u0061t\U000000610'); - unistr --------- - data0 -(1 row) - -SELECT unistr('a\\b'); - unistr --------- - a\b -(1 row) - --- errors: -SELECT unistr('wrong: \db99'); -ERROR: invalid Unicode surrogate pair -SELECT unistr('wrong: \db99\0061'); -ERROR: invalid Unicode surrogate pair -SELECT unistr('wrong: \+00db99\+000061'); -ERROR: invalid Unicode surrogate pair -SELECT unistr('wrong: \+2FFFFF'); -ERROR: invalid Unicode code point: 2FFFFF -SELECT unistr('wrong: \udb99\u0061'); -ERROR: invalid Unicode surrogate pair -SELECT unistr('wrong: \U0000db99\U00000061'); -ERROR: invalid Unicode surrogate pair -SELECT unistr('wrong: \U002FFFFF'); -ERROR: invalid Unicode code point: 2FFFFF -SELECT unistr('wrong: \xyz'); -ERROR: invalid Unicode escape -HINT: Unicode escapes must be \XXXX, \+XXXXXX, \uXXXX, or \UXXXXXXXX. +psql: error: connection to server on socket "/tmp/pg_regress-y0DQT8/.s.PGSQL.58928" failed: FATAL: the database system is not yet accepting connections +DETAIL: Consistent recovery state has not been yet reached. diff -U3 /home/dd/projects/postgres/vanilla/planb/postgres/master/src/test/regress/expected/md5_1.out /home/dd/projects/postgres/vanilla/planb/postgres/master/src/test/regress/results/md5.out --- /home/dd/projects/postgres/vanilla/planb/postgres/master/src/test/regress/expected/md5_1.out 2025-07-30 23:13:15.376942975 +0300 +++ /home/dd/projects/postgres/vanilla/planb/postgres/master/src/test/regress/results/md5.out 2025-09-16 00:27:00.724744704 +0300 @@ -1,35 +1,2 @@ --- --- MD5 test suite - from IETF RFC 1321 --- (see: https://www.rfc-editor.org/rfc/rfc1321) --- --- (The md5() function will error in OpenSSL FIPS mode. By keeping --- this test in a separate file, it is easier to manage variant --- results.) -select md5('') = 'd41d8cd98f00b204e9800998ecf8427e' AS "TRUE"; -ERROR: could not compute MD5 hash: unsupported -select md5('a') = '0cc175b9c0f1b6a831c399e269772661' AS "TRUE"; -ERROR: could not compute MD5 hash: unsupported -select md5('abc') = '900150983cd24fb0d6963f7d28e17f72' AS "TRUE"; -ERROR: could not compute MD5 hash: unsupported -select md5('message digest') = 'f96b697d7cb7938d525a2f31aaf161d0' AS "TRUE"; -ERROR: could not compute MD5 hash: unsupported -select md5('abcdefghijklmnopqrstuvwxyz') = 'c3fcd3d76192e4007dfb496cca67e13b' AS "TRUE"; -ERROR: could not compute MD5 hash: unsupported -select md5('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789') = 'd174ab98d277d9f5a5611c2c9f419d9f' AS "TRUE"; -ERROR: could not compute MD5 hash: unsupported -select md5('12345678901234567890123456789012345678901234567890123456789012345678901234567890') = '57edf4a22be3c955ac49da2e2107b67a' AS "TRUE"; -ERROR: could not compute MD5 hash: unsupported -select md5(''::bytea) = 'd41d8cd98f00b204e9800998ecf8427e' AS "TRUE"; -ERROR: could not compute MD5 hash: unsupported -select md5('a'::bytea) = '0cc175b9c0f1b6a831c399e269772661' AS "TRUE"; -ERROR: could not compute MD5 hash: unsupported -select md5('abc'::bytea) = '900150983cd24fb0d6963f7d28e17f72' AS "TRUE"; -ERROR: could not compute MD5 hash: unsupported -select md5('message digest'::bytea) = 'f96b697d7cb7938d525a2f31aaf161d0' AS "TRUE"; -ERROR: could not compute MD5 hash: unsupported -select md5('abcdefghijklmnopqrstuvwxyz'::bytea) = 'c3fcd3d76192e4007dfb496cca67e13b' AS "TRUE"; -ERROR: could not compute MD5 hash: unsupported -select md5('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'::bytea) = 'd174ab98d277d9f5a5611c2c9f419d9f' AS "TRUE"; -ERROR: could not compute MD5 hash: unsupported -select md5('12345678901234567890123456789012345678901234567890123456789012345678901234567890'::bytea) = '57edf4a22be3c955ac49da2e2107b67a' AS "TRUE"; -ERROR: could not compute MD5 hash: unsupported +psql: error: connection to server on socket "/tmp/pg_regress-y0DQT8/.s.PGSQL.58928" failed: FATAL: the database system is not yet accepting connections +DETAIL: Consistent recovery state has not been yet reached. diff -U3 /home/dd/projects/postgres/vanilla/planb/postgres/master/src/test/regress/expected/numerology.out /home/dd/projects/postgres/vanilla/planb/postgres/master/src/test/regress/results/numerology.out --- /home/dd/projects/postgres/vanilla/planb/postgres/master/src/test/regress/expected/numerology.out 2025-09-05 17:46:10.918102806 +0300 +++ /home/dd/projects/postgres/vanilla/planb/postgres/master/src/test/regress/results/numerology.out 2025-09-16 00:27:00.724744704 +0300 @@ -1,483 +1,2 @@ --- --- NUMEROLOGY --- Test various combinations of numeric types and functions. --- --- --- numeric literals --- -SELECT 0b100101; - ?column? ----------- - 37 -(1 row) - -SELECT 0o273; - ?column? ----------- - 187 -(1 row) - -SELECT 0x42F; - ?column? ----------- - 1071 -(1 row) - --- cases near int4 overflow -SELECT 0b1111111111111111111111111111111; - ?column? ------------- - 2147483647 -(1 row) - -SELECT 0b10000000000000000000000000000000; - ?column? ------------- - 2147483648 -(1 row) - -SELECT 0o17777777777; - ?column? ------------- - 2147483647 -(1 row) - -SELECT 0o20000000000; - ?column? ------------- - 2147483648 -(1 row) - -SELECT 0x7FFFFFFF; - ?column? ------------- - 2147483647 -(1 row) - -SELECT 0x80000000; - ?column? ------------- - 2147483648 -(1 row) - -SELECT -0b10000000000000000000000000000000; - ?column? -------------- - -2147483648 -(1 row) - -SELECT -0b10000000000000000000000000000001; - ?column? -------------- - -2147483649 -(1 row) - -SELECT -0o20000000000; - ?column? -------------- - -2147483648 -(1 row) - -SELECT -0o20000000001; - ?column? -------------- - -2147483649 -(1 row) - -SELECT -0x80000000; - ?column? -------------- - -2147483648 -(1 row) - -SELECT -0x80000001; - ?column? -------------- - -2147483649 -(1 row) - --- cases near int8 overflow -SELECT 0b111111111111111111111111111111111111111111111111111111111111111; - ?column? ---------------------- - 9223372036854775807 -(1 row) - -SELECT 0b1000000000000000000000000000000000000000000000000000000000000000; - ?column? ---------------------- - 9223372036854775808 -(1 row) - -SELECT 0o777777777777777777777; - ?column? ---------------------- - 9223372036854775807 -(1 row) - -SELECT 0o1000000000000000000000; - ?column? ---------------------- - 9223372036854775808 -(1 row) - -SELECT 0x7FFFFFFFFFFFFFFF; - ?column? ---------------------- - 9223372036854775807 -(1 row) - -SELECT 0x8000000000000000; - ?column? ---------------------- - 9223372036854775808 -(1 row) - -SELECT -0b1000000000000000000000000000000000000000000000000000000000000000; - ?column? ----------------------- - -9223372036854775808 -(1 row) - -SELECT -0b1000000000000000000000000000000000000000000000000000000000000001; - ?column? ----------------------- - -9223372036854775809 -(1 row) - -SELECT -0o1000000000000000000000; - ?column? ----------------------- - -9223372036854775808 -(1 row) - -SELECT -0o1000000000000000000001; - ?column? ----------------------- - -9223372036854775809 -(1 row) - -SELECT -0x8000000000000000; - ?column? ----------------------- - -9223372036854775808 -(1 row) - -SELECT -0x8000000000000001; - ?column? ----------------------- - -9223372036854775809 -(1 row) - --- error cases -SELECT 123abc; -ERROR: trailing junk after numeric literal at or near "123abc" -LINE 1: SELECT 123abc; - ^ -SELECT 0x0o; -ERROR: trailing junk after numeric literal at or near "0x0o" -LINE 1: SELECT 0x0o; - ^ -SELECT 0.a; -ERROR: trailing junk after numeric literal at or near "0.a" -LINE 1: SELECT 0.a; - ^ -SELECT 0.0a; -ERROR: trailing junk after numeric literal at or near "0.0a" -LINE 1: SELECT 0.0a; - ^ -SELECT .0a; -ERROR: trailing junk after numeric literal at or near ".0a" -LINE 1: SELECT .0a; - ^ -SELECT 0.0e1a; -ERROR: trailing junk after numeric literal at or near "0.0e1a" -LINE 1: SELECT 0.0e1a; - ^ -SELECT 0.0e; -ERROR: trailing junk after numeric literal at or near "0.0e" -LINE 1: SELECT 0.0e; - ^ -SELECT 0.0e+a; -ERROR: trailing junk after numeric literal at or near "0.0e+" -LINE 1: SELECT 0.0e+a; - ^ -PREPARE p1 AS SELECT $1a; -ERROR: trailing junk after parameter at or near "$1a" -LINE 1: PREPARE p1 AS SELECT $1a; - ^ -PREPARE p1 AS SELECT $2147483648; -ERROR: parameter number too large at or near "$2147483648" -LINE 1: PREPARE p1 AS SELECT $2147483648; - ^ -SELECT 0b; -ERROR: invalid binary integer at or near "0b" -LINE 1: SELECT 0b; - ^ -SELECT 1b; -ERROR: trailing junk after numeric literal at or near "1b" -LINE 1: SELECT 1b; - ^ -SELECT 0b0x; -ERROR: trailing junk after numeric literal at or near "0b0x" -LINE 1: SELECT 0b0x; - ^ -SELECT 0o; -ERROR: invalid octal integer at or near "0o" -LINE 1: SELECT 0o; - ^ -SELECT 1o; -ERROR: trailing junk after numeric literal at or near "1o" -LINE 1: SELECT 1o; - ^ -SELECT 0o0x; -ERROR: trailing junk after numeric literal at or near "0o0x" -LINE 1: SELECT 0o0x; - ^ -SELECT 0x; -ERROR: invalid hexadecimal integer at or near "0x" -LINE 1: SELECT 0x; - ^ -SELECT 1x; -ERROR: trailing junk after numeric literal at or near "1x" -LINE 1: SELECT 1x; - ^ -SELECT 0x0y; -ERROR: trailing junk after numeric literal at or near "0x0y" -LINE 1: SELECT 0x0y; - ^ --- underscores -SELECT 1_000_000; - ?column? ----------- - 1000000 -(1 row) - -SELECT 1_2_3; - ?column? ----------- - 123 -(1 row) - -SELECT 0x1EEE_FFFF; - ?column? ------------ - 518979583 -(1 row) - -SELECT 0o2_73; - ?column? ----------- - 187 -(1 row) - -SELECT 0b_10_0101; - ?column? ----------- - 37 -(1 row) - -SELECT 1_000.000_005; - ?column? -------------- - 1000.000005 -(1 row) - -SELECT 1_000.; - ?column? ----------- - 1000 -(1 row) - -SELECT .000_005; - ?column? ----------- - 0.000005 -(1 row) - -SELECT 1_000.5e0_1; - ?column? ----------- - 10005 -(1 row) - -DO $$ -DECLARE - i int; -BEGIN - FOR i IN 1_001..1_003 LOOP - RAISE NOTICE 'i = %', i; - END LOOP; -END $$; -NOTICE: i = 1001 -NOTICE: i = 1002 -NOTICE: i = 1003 --- error cases -SELECT _100; -ERROR: column "_100" does not exist -LINE 1: SELECT _100; - ^ -SELECT 100_; -ERROR: trailing junk after numeric literal at or near "100_" -LINE 1: SELECT 100_; - ^ -SELECT 100__000; -ERROR: trailing junk after numeric literal at or near "100__000" -LINE 1: SELECT 100__000; - ^ -SELECT _1_000.5; -ERROR: syntax error at or near ".5" -LINE 1: SELECT _1_000.5; - ^ -SELECT 1_000_.5; -ERROR: trailing junk after numeric literal at or near "1_000_" -LINE 1: SELECT 1_000_.5; - ^ -SELECT 1_000._5; -ERROR: trailing junk after numeric literal at or near "1_000._5" -LINE 1: SELECT 1_000._5; - ^ -SELECT 1_000.5_; -ERROR: trailing junk after numeric literal at or near "1_000.5_" -LINE 1: SELECT 1_000.5_; - ^ -SELECT 1_000.5e_1; -ERROR: trailing junk after numeric literal at or near "1_000.5e_1" -LINE 1: SELECT 1_000.5e_1; - ^ -PREPARE p1 AS SELECT $0_1; -ERROR: trailing junk after parameter at or near "$0_1" -LINE 1: PREPARE p1 AS SELECT $0_1; - ^ --- --- Test implicit type conversions --- This fails for Postgres v6.1 (and earlier?) --- so let's try explicit conversions for now - tgl 97/05/07 --- -CREATE TABLE TEMP_FLOAT (f1 FLOAT8); -INSERT INTO TEMP_FLOAT (f1) - SELECT float8(f1) FROM INT4_TBL; -INSERT INTO TEMP_FLOAT (f1) - SELECT float8(f1) FROM INT2_TBL; -SELECT f1 FROM TEMP_FLOAT - ORDER BY f1; - f1 -------------- - -2147483647 - -123456 - -32767 - -1234 - 0 - 0 - 1234 - 32767 - 123456 - 2147483647 -(10 rows) - --- int4 -CREATE TABLE TEMP_INT4 (f1 INT4); -INSERT INTO TEMP_INT4 (f1) - SELECT int4(f1) FROM FLOAT8_TBL - WHERE (f1 > -2147483647) AND (f1 < 2147483647); -INSERT INTO TEMP_INT4 (f1) - SELECT int4(f1) FROM INT2_TBL; -SELECT f1 FROM TEMP_INT4 - ORDER BY f1; - f1 --------- - -32767 - -1234 - -1004 - -35 - 0 - 0 - 0 - 1234 - 32767 -(9 rows) - --- int2 -CREATE TABLE TEMP_INT2 (f1 INT2); -INSERT INTO TEMP_INT2 (f1) - SELECT int2(f1) FROM FLOAT8_TBL - WHERE (f1 >= -32767) AND (f1 <= 32767); -INSERT INTO TEMP_INT2 (f1) - SELECT int2(f1) FROM INT4_TBL - WHERE (f1 >= -32767) AND (f1 <= 32767); -SELECT f1 FROM TEMP_INT2 - ORDER BY f1; - f1 -------- - -1004 - -35 - 0 - 0 - 0 -(5 rows) - --- --- Group-by combinations --- -CREATE TABLE TEMP_GROUP (f1 INT4, f2 INT4, f3 FLOAT8); -INSERT INTO TEMP_GROUP - SELECT 1, (- i.f1), (- f.f1) - FROM INT4_TBL i, FLOAT8_TBL f; -INSERT INTO TEMP_GROUP - SELECT 2, i.f1, f.f1 - FROM INT4_TBL i, FLOAT8_TBL f; -SELECT DISTINCT f1 AS two FROM TEMP_GROUP ORDER BY 1; - two ------ - 1 - 2 -(2 rows) - -SELECT f1 AS two, max(f3) AS max_float, min(f3) as min_float - FROM TEMP_GROUP - GROUP BY f1 - ORDER BY two, max_float, min_float; - two | max_float | min_float ------+----------------------+----------------------- - 1 | 1.2345678901234e+200 | -0 - 2 | 0 | -1.2345678901234e+200 -(2 rows) - --- GROUP BY a result column name is not legal per SQL92, but we accept it --- anyway (if the name is not the name of any column exposed by FROM). -SELECT f1 AS two, max(f3) AS max_float, min(f3) AS min_float - FROM TEMP_GROUP - GROUP BY two - ORDER BY two, max_float, min_float; - two | max_float | min_float ------+----------------------+----------------------- - 1 | 1.2345678901234e+200 | -0 - 2 | 0 | -1.2345678901234e+200 -(2 rows) - -SELECT f1 AS two, (max(f3) + 1) AS max_plus_1, (min(f3) - 1) AS min_minus_1 - FROM TEMP_GROUP - GROUP BY f1 - ORDER BY two, min_minus_1; - two | max_plus_1 | min_minus_1 ------+----------------------+----------------------- - 1 | 1.2345678901234e+200 | -1 - 2 | 1 | -1.2345678901234e+200 -(2 rows) - -SELECT f1 AS two, - max(f2) + min(f2) AS max_plus_min, - min(f3) - 1 AS min_minus_1 - FROM TEMP_GROUP - GROUP BY f1 - ORDER BY two, min_minus_1; - two | max_plus_min | min_minus_1 ------+--------------+----------------------- - 1 | 0 | -1 - 2 | 0 | -1.2345678901234e+200 -(2 rows) - -DROP TABLE TEMP_INT2; -DROP TABLE TEMP_INT4; -DROP TABLE TEMP_FLOAT; -DROP TABLE TEMP_GROUP; +psql: error: connection to server on socket "/tmp/pg_regress-y0DQT8/.s.PGSQL.58928" failed: FATAL: the database system is not yet accepting connections +DETAIL: Consistent recovery state has not been yet reached. diff -U3 /home/dd/projects/postgres/vanilla/planb/postgres/master/src/test/regress/expected/point.out /home/dd/projects/postgres/vanilla/planb/postgres/master/src/test/regress/results/point.out --- /home/dd/projects/postgres/vanilla/planb/postgres/master/src/test/regress/expected/point.out 2025-07-30 23:13:15.381943022 +0300 +++ /home/dd/projects/postgres/vanilla/planb/postgres/master/src/test/regress/results/point.out 2025-09-16 00:27:00.725744713 +0300 @@ -1,478 +1,2 @@ --- --- POINT --- --- avoid bit-exact output here because operations may not be bit-exact. -SET extra_float_digits = 0; --- point_tbl was already created and filled in test_setup.sql. --- Here we just try to insert bad values. -INSERT INTO POINT_TBL(f1) VALUES ('asdfasdf'); -ERROR: invalid input syntax for type point: "asdfasdf" -LINE 1: INSERT INTO POINT_TBL(f1) VALUES ('asdfasdf'); - ^ -INSERT INTO POINT_TBL(f1) VALUES ('(10.0 10.0)'); -ERROR: invalid input syntax for type point: "(10.0 10.0)" -LINE 1: INSERT INTO POINT_TBL(f1) VALUES ('(10.0 10.0)'); - ^ -INSERT INTO POINT_TBL(f1) VALUES ('(10.0, 10.0) x'); -ERROR: invalid input syntax for type point: "(10.0, 10.0) x" -LINE 1: INSERT INTO POINT_TBL(f1) VALUES ('(10.0, 10.0) x'); - ^ -INSERT INTO POINT_TBL(f1) VALUES ('(10.0,10.0'); -ERROR: invalid input syntax for type point: "(10.0,10.0" -LINE 1: INSERT INTO POINT_TBL(f1) VALUES ('(10.0,10.0'); - ^ -INSERT INTO POINT_TBL(f1) VALUES ('(10.0, 1e+500)'); -- Out of range -ERROR: "1e+500" is out of range for type double precision -LINE 1: INSERT INTO POINT_TBL(f1) VALUES ('(10.0, 1e+500)'); - ^ -SELECT * FROM POINT_TBL; - f1 -------------------- - (0,0) - (-10,0) - (-3,4) - (5.1,34.5) - (-5,-12) - (1e-300,-1e-300) - (1e+300,Infinity) - (Infinity,1e+300) - (NaN,NaN) - (10,10) -(10 rows) - --- left of -SELECT p.* FROM POINT_TBL p WHERE p.f1 << '(0.0, 0.0)'; - f1 ----------- - (-10,0) - (-3,4) - (-5,-12) -(3 rows) - --- right of -SELECT p.* FROM POINT_TBL p WHERE '(0.0,0.0)' >> p.f1; - f1 ----------- - (-10,0) - (-3,4) - (-5,-12) -(3 rows) - --- above -SELECT p.* FROM POINT_TBL p WHERE '(0.0,0.0)' |>> p.f1; - f1 ----------- - (-5,-12) -(1 row) - --- below -SELECT p.* FROM POINT_TBL p WHERE p.f1 <<| '(0.0, 0.0)'; - f1 ----------- - (-5,-12) -(1 row) - --- equal -SELECT p.* FROM POINT_TBL p WHERE p.f1 ~= '(5.1, 34.5)'; - f1 ------------- - (5.1,34.5) -(1 row) - --- point in box -SELECT p.* FROM POINT_TBL p - WHERE p.f1 <@ box '(0,0,100,100)'; - f1 ------------- - (0,0) - (5.1,34.5) - (10,10) -(3 rows) - -SELECT p.* FROM POINT_TBL p - WHERE box '(0,0,100,100)' @> p.f1; - f1 ------------- - (0,0) - (5.1,34.5) - (10,10) -(3 rows) - -SELECT p.* FROM POINT_TBL p - WHERE not p.f1 <@ box '(0,0,100,100)'; - f1 -------------------- - (-10,0) - (-3,4) - (-5,-12) - (1e-300,-1e-300) - (1e+300,Infinity) - (Infinity,1e+300) - (NaN,NaN) -(7 rows) - -SELECT p.* FROM POINT_TBL p - WHERE p.f1 <@ path '[(0,0),(-10,0),(-10,10)]'; - f1 ------------------- - (0,0) - (-10,0) - (1e-300,-1e-300) -(3 rows) - -SELECT p.* FROM POINT_TBL p - WHERE not box '(0,0,100,100)' @> p.f1; - f1 -------------------- - (-10,0) - (-3,4) - (-5,-12) - (1e-300,-1e-300) - (1e+300,Infinity) - (Infinity,1e+300) - (NaN,NaN) -(7 rows) - -SELECT p.f1, p.f1 <-> point '(0,0)' AS dist - FROM POINT_TBL p - ORDER BY dist; - f1 | dist --------------------+---------------------- - (0,0) | 0 - (1e-300,-1e-300) | 1.4142135623731e-300 - (-3,4) | 5 - (-10,0) | 10 - (-5,-12) | 13 - (10,10) | 14.142135623731 - (5.1,34.5) | 34.8749193547455 - (1e+300,Infinity) | Infinity - (Infinity,1e+300) | Infinity - (NaN,NaN) | NaN -(10 rows) - -SELECT p1.f1 AS point1, p2.f1 AS point2, p1.f1 <-> p2.f1 AS dist - FROM POINT_TBL p1, POINT_TBL p2 - ORDER BY dist, p1.f1[0], p2.f1[0]; - point1 | point2 | dist --------------------+-------------------+---------------------- - (-10,0) | (-10,0) | 0 - (-5,-12) | (-5,-12) | 0 - (-3,4) | (-3,4) | 0 - (0,0) | (0,0) | 0 - (1e-300,-1e-300) | (1e-300,-1e-300) | 0 - (5.1,34.5) | (5.1,34.5) | 0 - (10,10) | (10,10) | 0 - (0,0) | (1e-300,-1e-300) | 1.4142135623731e-300 - (1e-300,-1e-300) | (0,0) | 1.4142135623731e-300 - (-3,4) | (0,0) | 5 - (-3,4) | (1e-300,-1e-300) | 5 - (0,0) | (-3,4) | 5 - (1e-300,-1e-300) | (-3,4) | 5 - (-10,0) | (-3,4) | 8.06225774829855 - (-3,4) | (-10,0) | 8.06225774829855 - (-10,0) | (0,0) | 10 - (-10,0) | (1e-300,-1e-300) | 10 - (0,0) | (-10,0) | 10 - (1e-300,-1e-300) | (-10,0) | 10 - (-10,0) | (-5,-12) | 13 - (-5,-12) | (-10,0) | 13 - (-5,-12) | (0,0) | 13 - (-5,-12) | (1e-300,-1e-300) | 13 - (0,0) | (-5,-12) | 13 - (1e-300,-1e-300) | (-5,-12) | 13 - (0,0) | (10,10) | 14.142135623731 - (1e-300,-1e-300) | (10,10) | 14.142135623731 - (10,10) | (0,0) | 14.142135623731 - (10,10) | (1e-300,-1e-300) | 14.142135623731 - (-3,4) | (10,10) | 14.3178210632764 - (10,10) | (-3,4) | 14.3178210632764 - (-5,-12) | (-3,4) | 16.1245154965971 - (-3,4) | (-5,-12) | 16.1245154965971 - (-10,0) | (10,10) | 22.3606797749979 - (10,10) | (-10,0) | 22.3606797749979 - (5.1,34.5) | (10,10) | 24.9851956166046 - (10,10) | (5.1,34.5) | 24.9851956166046 - (-5,-12) | (10,10) | 26.6270539113887 - (10,10) | (-5,-12) | 26.6270539113887 - (-3,4) | (5.1,34.5) | 31.5572495632937 - (5.1,34.5) | (-3,4) | 31.5572495632937 - (0,0) | (5.1,34.5) | 34.8749193547455 - (1e-300,-1e-300) | (5.1,34.5) | 34.8749193547455 - (5.1,34.5) | (0,0) | 34.8749193547455 - (5.1,34.5) | (1e-300,-1e-300) | 34.8749193547455 - (-10,0) | (5.1,34.5) | 37.6597928831267 - (5.1,34.5) | (-10,0) | 37.6597928831267 - (-5,-12) | (5.1,34.5) | 47.5842410888311 - (5.1,34.5) | (-5,-12) | 47.5842410888311 - (-10,0) | (1e+300,Infinity) | Infinity - (-10,0) | (Infinity,1e+300) | Infinity - (-5,-12) | (1e+300,Infinity) | Infinity - (-5,-12) | (Infinity,1e+300) | Infinity - (-3,4) | (1e+300,Infinity) | Infinity - (-3,4) | (Infinity,1e+300) | Infinity - (0,0) | (1e+300,Infinity) | Infinity - (0,0) | (Infinity,1e+300) | Infinity - (1e-300,-1e-300) | (1e+300,Infinity) | Infinity - (1e-300,-1e-300) | (Infinity,1e+300) | Infinity - (5.1,34.5) | (1e+300,Infinity) | Infinity - (5.1,34.5) | (Infinity,1e+300) | Infinity - (10,10) | (1e+300,Infinity) | Infinity - (10,10) | (Infinity,1e+300) | Infinity - (1e+300,Infinity) | (-10,0) | Infinity - (1e+300,Infinity) | (-5,-12) | Infinity - (1e+300,Infinity) | (-3,4) | Infinity - (1e+300,Infinity) | (0,0) | Infinity - (1e+300,Infinity) | (1e-300,-1e-300) | Infinity - (1e+300,Infinity) | (5.1,34.5) | Infinity - (1e+300,Infinity) | (10,10) | Infinity - (1e+300,Infinity) | (Infinity,1e+300) | Infinity - (Infinity,1e+300) | (-10,0) | Infinity - (Infinity,1e+300) | (-5,-12) | Infinity - (Infinity,1e+300) | (-3,4) | Infinity - (Infinity,1e+300) | (0,0) | Infinity - (Infinity,1e+300) | (1e-300,-1e-300) | Infinity - (Infinity,1e+300) | (5.1,34.5) | Infinity - (Infinity,1e+300) | (10,10) | Infinity - (Infinity,1e+300) | (1e+300,Infinity) | Infinity - (-10,0) | (NaN,NaN) | NaN - (-5,-12) | (NaN,NaN) | NaN - (-3,4) | (NaN,NaN) | NaN - (0,0) | (NaN,NaN) | NaN - (1e-300,-1e-300) | (NaN,NaN) | NaN - (5.1,34.5) | (NaN,NaN) | NaN - (10,10) | (NaN,NaN) | NaN - (1e+300,Infinity) | (1e+300,Infinity) | NaN - (1e+300,Infinity) | (NaN,NaN) | NaN - (Infinity,1e+300) | (Infinity,1e+300) | NaN - (Infinity,1e+300) | (NaN,NaN) | NaN - (NaN,NaN) | (-10,0) | NaN - (NaN,NaN) | (-5,-12) | NaN - (NaN,NaN) | (-3,4) | NaN - (NaN,NaN) | (0,0) | NaN - (NaN,NaN) | (1e-300,-1e-300) | NaN - (NaN,NaN) | (5.1,34.5) | NaN - (NaN,NaN) | (10,10) | NaN - (NaN,NaN) | (1e+300,Infinity) | NaN - (NaN,NaN) | (Infinity,1e+300) | NaN - (NaN,NaN) | (NaN,NaN) | NaN -(100 rows) - -SELECT p1.f1 AS point1, p2.f1 AS point2 - FROM POINT_TBL p1, POINT_TBL p2 - WHERE (p1.f1 <-> p2.f1) > 3; - point1 | point2 --------------------+------------------- - (0,0) | (-10,0) - (0,0) | (-3,4) - (0,0) | (5.1,34.5) - (0,0) | (-5,-12) - (0,0) | (1e+300,Infinity) - (0,0) | (Infinity,1e+300) - (0,0) | (NaN,NaN) - (0,0) | (10,10) - (-10,0) | (0,0) - (-10,0) | (-3,4) - (-10,0) | (5.1,34.5) - (-10,0) | (-5,-12) - (-10,0) | (1e-300,-1e-300) - (-10,0) | (1e+300,Infinity) - (-10,0) | (Infinity,1e+300) - (-10,0) | (NaN,NaN) - (-10,0) | (10,10) - (-3,4) | (0,0) - (-3,4) | (-10,0) - (-3,4) | (5.1,34.5) - (-3,4) | (-5,-12) - (-3,4) | (1e-300,-1e-300) - (-3,4) | (1e+300,Infinity) - (-3,4) | (Infinity,1e+300) - (-3,4) | (NaN,NaN) - (-3,4) | (10,10) - (5.1,34.5) | (0,0) - (5.1,34.5) | (-10,0) - (5.1,34.5) | (-3,4) - (5.1,34.5) | (-5,-12) - (5.1,34.5) | (1e-300,-1e-300) - (5.1,34.5) | (1e+300,Infinity) - (5.1,34.5) | (Infinity,1e+300) - (5.1,34.5) | (NaN,NaN) - (5.1,34.5) | (10,10) - (-5,-12) | (0,0) - (-5,-12) | (-10,0) - (-5,-12) | (-3,4) - (-5,-12) | (5.1,34.5) - (-5,-12) | (1e-300,-1e-300) - (-5,-12) | (1e+300,Infinity) - (-5,-12) | (Infinity,1e+300) - (-5,-12) | (NaN,NaN) - (-5,-12) | (10,10) - (1e-300,-1e-300) | (-10,0) - (1e-300,-1e-300) | (-3,4) - (1e-300,-1e-300) | (5.1,34.5) - (1e-300,-1e-300) | (-5,-12) - (1e-300,-1e-300) | (1e+300,Infinity) - (1e-300,-1e-300) | (Infinity,1e+300) - (1e-300,-1e-300) | (NaN,NaN) - (1e-300,-1e-300) | (10,10) - (1e+300,Infinity) | (0,0) - (1e+300,Infinity) | (-10,0) - (1e+300,Infinity) | (-3,4) - (1e+300,Infinity) | (5.1,34.5) - (1e+300,Infinity) | (-5,-12) - (1e+300,Infinity) | (1e-300,-1e-300) - (1e+300,Infinity) | (1e+300,Infinity) - (1e+300,Infinity) | (Infinity,1e+300) - (1e+300,Infinity) | (NaN,NaN) - (1e+300,Infinity) | (10,10) - (Infinity,1e+300) | (0,0) - (Infinity,1e+300) | (-10,0) - (Infinity,1e+300) | (-3,4) - (Infinity,1e+300) | (5.1,34.5) - (Infinity,1e+300) | (-5,-12) - (Infinity,1e+300) | (1e-300,-1e-300) - (Infinity,1e+300) | (1e+300,Infinity) - (Infinity,1e+300) | (Infinity,1e+300) - (Infinity,1e+300) | (NaN,NaN) - (Infinity,1e+300) | (10,10) - (NaN,NaN) | (0,0) - (NaN,NaN) | (-10,0) - (NaN,NaN) | (-3,4) - (NaN,NaN) | (5.1,34.5) - (NaN,NaN) | (-5,-12) - (NaN,NaN) | (1e-300,-1e-300) - (NaN,NaN) | (1e+300,Infinity) - (NaN,NaN) | (Infinity,1e+300) - (NaN,NaN) | (NaN,NaN) - (NaN,NaN) | (10,10) - (10,10) | (0,0) - (10,10) | (-10,0) - (10,10) | (-3,4) - (10,10) | (5.1,34.5) - (10,10) | (-5,-12) - (10,10) | (1e-300,-1e-300) - (10,10) | (1e+300,Infinity) - (10,10) | (Infinity,1e+300) - (10,10) | (NaN,NaN) -(91 rows) - --- put distance result into output to allow sorting with GEQ optimizer - tgl 97/05/10 -SELECT p1.f1 AS point1, p2.f1 AS point2, (p1.f1 <-> p2.f1) AS distance - FROM POINT_TBL p1, POINT_TBL p2 - WHERE (p1.f1 <-> p2.f1) > 3 and p1.f1 << p2.f1 - ORDER BY distance, p1.f1[0], p2.f1[0]; - point1 | point2 | distance --------------------+-------------------+------------------ - (-3,4) | (0,0) | 5 - (-3,4) | (1e-300,-1e-300) | 5 - (-10,0) | (-3,4) | 8.06225774829855 - (-10,0) | (0,0) | 10 - (-10,0) | (1e-300,-1e-300) | 10 - (-10,0) | (-5,-12) | 13 - (-5,-12) | (0,0) | 13 - (-5,-12) | (1e-300,-1e-300) | 13 - (0,0) | (10,10) | 14.142135623731 - (1e-300,-1e-300) | (10,10) | 14.142135623731 - (-3,4) | (10,10) | 14.3178210632764 - (-5,-12) | (-3,4) | 16.1245154965971 - (-10,0) | (10,10) | 22.3606797749979 - (5.1,34.5) | (10,10) | 24.9851956166046 - (-5,-12) | (10,10) | 26.6270539113887 - (-3,4) | (5.1,34.5) | 31.5572495632937 - (0,0) | (5.1,34.5) | 34.8749193547455 - (1e-300,-1e-300) | (5.1,34.5) | 34.8749193547455 - (-10,0) | (5.1,34.5) | 37.6597928831267 - (-5,-12) | (5.1,34.5) | 47.5842410888311 - (-10,0) | (1e+300,Infinity) | Infinity - (-10,0) | (Infinity,1e+300) | Infinity - (-5,-12) | (1e+300,Infinity) | Infinity - (-5,-12) | (Infinity,1e+300) | Infinity - (-3,4) | (1e+300,Infinity) | Infinity - (-3,4) | (Infinity,1e+300) | Infinity - (0,0) | (1e+300,Infinity) | Infinity - (0,0) | (Infinity,1e+300) | Infinity - (1e-300,-1e-300) | (1e+300,Infinity) | Infinity - (1e-300,-1e-300) | (Infinity,1e+300) | Infinity - (5.1,34.5) | (1e+300,Infinity) | Infinity - (5.1,34.5) | (Infinity,1e+300) | Infinity - (10,10) | (1e+300,Infinity) | Infinity - (10,10) | (Infinity,1e+300) | Infinity - (1e+300,Infinity) | (Infinity,1e+300) | Infinity -(35 rows) - --- put distance result into output to allow sorting with GEQ optimizer - tgl 97/05/10 -SELECT p1.f1 AS point1, p2.f1 AS point2, (p1.f1 <-> p2.f1) AS distance - FROM POINT_TBL p1, POINT_TBL p2 - WHERE (p1.f1 <-> p2.f1) > 3 and p1.f1 << p2.f1 and p1.f1 |>> p2.f1 - ORDER BY distance; - point1 | point2 | distance --------------------+-------------------+------------------ - (-3,4) | (0,0) | 5 - (-3,4) | (1e-300,-1e-300) | 5 - (-10,0) | (-5,-12) | 13 - (5.1,34.5) | (10,10) | 24.9851956166046 - (1e+300,Infinity) | (Infinity,1e+300) | Infinity -(5 rows) - --- Test that GiST indexes provide same behavior as sequential scan -CREATE TEMP TABLE point_gist_tbl(f1 point); -INSERT INTO point_gist_tbl SELECT '(0,0)' FROM generate_series(0,1000); -CREATE INDEX point_gist_tbl_index ON point_gist_tbl USING gist (f1); -INSERT INTO point_gist_tbl VALUES ('(0.0000009,0.0000009)'); -SET enable_seqscan TO true; -SET enable_indexscan TO false; -SET enable_bitmapscan TO false; -SELECT COUNT(*) FROM point_gist_tbl WHERE f1 ~= '(0.0000009,0.0000009)'::point; - count -------- - 1002 -(1 row) - -SELECT COUNT(*) FROM point_gist_tbl WHERE f1 <@ '(0.0000009,0.0000009),(0.0000009,0.0000009)'::box; - count -------- - 1 -(1 row) - -SELECT COUNT(*) FROM point_gist_tbl WHERE f1 ~= '(0.0000018,0.0000018)'::point; - count -------- - 1 -(1 row) - -SET enable_seqscan TO false; -SET enable_indexscan TO true; -SET enable_bitmapscan TO true; -SELECT COUNT(*) FROM point_gist_tbl WHERE f1 ~= '(0.0000009,0.0000009)'::point; - count -------- - 1002 -(1 row) - -SELECT COUNT(*) FROM point_gist_tbl WHERE f1 <@ '(0.0000009,0.0000009),(0.0000009,0.0000009)'::box; - count -------- - 1 -(1 row) - -SELECT COUNT(*) FROM point_gist_tbl WHERE f1 ~= '(0.0000018,0.0000018)'::point; - count -------- - 1 -(1 row) - -RESET enable_seqscan; -RESET enable_indexscan; -RESET enable_bitmapscan; --- test non-error-throwing API for some core types -SELECT pg_input_is_valid('1,y', 'point'); - pg_input_is_valid -------------------- - f -(1 row) - -SELECT * FROM pg_input_error_info('1,y', 'point'); - message | detail | hint | sql_error_code ---------------------------------------------+--------+------+---------------- - invalid input syntax for type point: "1,y" | | | 22P02 -(1 row) - +psql: error: connection to server on socket "/tmp/pg_regress-y0DQT8/.s.PGSQL.58928" failed: FATAL: the database system is not yet accepting connections +DETAIL: Consistent recovery state has not been yet reached. diff -U3 /home/dd/projects/postgres/vanilla/planb/postgres/master/src/test/regress/expected/lseg.out /home/dd/projects/postgres/vanilla/planb/postgres/master/src/test/regress/results/lseg.out --- /home/dd/projects/postgres/vanilla/planb/postgres/master/src/test/regress/expected/lseg.out 2025-07-30 23:13:15.376942975 +0300 +++ /home/dd/projects/postgres/vanilla/planb/postgres/master/src/test/regress/results/lseg.out 2025-09-16 00:27:00.725744713 +0300 @@ -1,57 +1,2 @@ --- --- LSEG --- Line segments --- ---DROP TABLE LSEG_TBL; -CREATE TABLE LSEG_TBL (s lseg); -INSERT INTO LSEG_TBL VALUES ('[(1,2),(3,4)]'); -INSERT INTO LSEG_TBL VALUES ('(0,0),(6,6)'); -INSERT INTO LSEG_TBL VALUES ('10,-10 ,-3,-4'); -INSERT INTO LSEG_TBL VALUES ('[-1e6,2e2,3e5, -4e1]'); -INSERT INTO LSEG_TBL VALUES (lseg(point(11, 22), point(33,44))); -INSERT INTO LSEG_TBL VALUES ('[(-10,2),(-10,3)]'); -- vertical -INSERT INTO LSEG_TBL VALUES ('[(0,-20),(30,-20)]'); -- horizontal -INSERT INTO LSEG_TBL VALUES ('[(NaN,1),(NaN,90)]'); -- NaN --- bad values for parser testing -INSERT INTO LSEG_TBL VALUES ('(3asdf,2 ,3,4r2)'); -ERROR: invalid input syntax for type lseg: "(3asdf,2 ,3,4r2)" -LINE 1: INSERT INTO LSEG_TBL VALUES ('(3asdf,2 ,3,4r2)'); - ^ -INSERT INTO LSEG_TBL VALUES ('[1,2,3, 4'); -ERROR: invalid input syntax for type lseg: "[1,2,3, 4" -LINE 1: INSERT INTO LSEG_TBL VALUES ('[1,2,3, 4'); - ^ -INSERT INTO LSEG_TBL VALUES ('[(,2),(3,4)]'); -ERROR: invalid input syntax for type lseg: "[(,2),(3,4)]" -LINE 1: INSERT INTO LSEG_TBL VALUES ('[(,2),(3,4)]'); - ^ -INSERT INTO LSEG_TBL VALUES ('[(1,2),(3,4)'); -ERROR: invalid input syntax for type lseg: "[(1,2),(3,4)" -LINE 1: INSERT INTO LSEG_TBL VALUES ('[(1,2),(3,4)'); - ^ -select * from LSEG_TBL; - s -------------------------------- - [(1,2),(3,4)] - [(0,0),(6,6)] - [(10,-10),(-3,-4)] - [(-1000000,200),(300000,-40)] - [(11,22),(33,44)] - [(-10,2),(-10,3)] - [(0,-20),(30,-20)] - [(NaN,1),(NaN,90)] -(8 rows) - --- test non-error-throwing API for some core types -SELECT pg_input_is_valid('[(1,2),(3)]', 'lseg'); - pg_input_is_valid -------------------- - f -(1 row) - -SELECT * FROM pg_input_error_info('[(1,2),(3)]', 'lseg'); - message | detail | hint | sql_error_code ----------------------------------------------------+--------+------+---------------- - invalid input syntax for type lseg: "[(1,2),(3)]" | | | 22P02 -(1 row) - +psql: error: connection to server on socket "/tmp/pg_regress-y0DQT8/.s.PGSQL.58928" failed: FATAL: the database system is not yet accepting connections +DETAIL: Consistent recovery state has not been yet reached. diff -U3 /home/dd/projects/postgres/vanilla/planb/postgres/master/src/test/regress/expected/line.out /home/dd/projects/postgres/vanilla/planb/postgres/master/src/test/regress/results/line.out --- /home/dd/projects/postgres/vanilla/planb/postgres/master/src/test/regress/expected/line.out 2025-07-30 23:13:15.376942975 +0300 +++ /home/dd/projects/postgres/vanilla/planb/postgres/master/src/test/regress/results/line.out 2025-09-16 00:27:00.724744704 +0300 @@ -1,148 +1,2 @@ --- --- LINE --- Infinite lines --- ---DROP TABLE LINE_TBL; -CREATE TABLE LINE_TBL (s line); -INSERT INTO LINE_TBL VALUES ('{0,-1,5}'); -- A == 0 -INSERT INTO LINE_TBL VALUES ('{1,0,5}'); -- B == 0 -INSERT INTO LINE_TBL VALUES ('{0,3,0}'); -- A == C == 0 -INSERT INTO LINE_TBL VALUES (' (0,0), (6,6)'); -INSERT INTO LINE_TBL VALUES ('10,-10 ,-5,-4'); -INSERT INTO LINE_TBL VALUES ('[-1e6,2e2,3e5, -4e1]'); -INSERT INTO LINE_TBL VALUES ('{3,NaN,5}'); -INSERT INTO LINE_TBL VALUES ('{NaN,NaN,NaN}'); --- horizontal -INSERT INTO LINE_TBL VALUES ('[(1,3),(2,3)]'); --- vertical -INSERT INTO LINE_TBL VALUES (line(point '(3,1)', point '(3,2)')); --- bad values for parser testing -INSERT INTO LINE_TBL VALUES ('{}'); -ERROR: invalid input syntax for type line: "{}" -LINE 1: INSERT INTO LINE_TBL VALUES ('{}'); - ^ -INSERT INTO LINE_TBL VALUES ('{0'); -ERROR: invalid input syntax for type line: "{0" -LINE 1: INSERT INTO LINE_TBL VALUES ('{0'); - ^ -INSERT INTO LINE_TBL VALUES ('{0,0}'); -ERROR: invalid input syntax for type line: "{0,0}" -LINE 1: INSERT INTO LINE_TBL VALUES ('{0,0}'); - ^ -INSERT INTO LINE_TBL VALUES ('{0,0,1'); -ERROR: invalid input syntax for type line: "{0,0,1" -LINE 1: INSERT INTO LINE_TBL VALUES ('{0,0,1'); - ^ -INSERT INTO LINE_TBL VALUES ('{0,0,1}'); -ERROR: invalid line specification: A and B cannot both be zero -LINE 1: INSERT INTO LINE_TBL VALUES ('{0,0,1}'); - ^ -INSERT INTO LINE_TBL VALUES ('{0,0,1} x'); -ERROR: invalid input syntax for type line: "{0,0,1} x" -LINE 1: INSERT INTO LINE_TBL VALUES ('{0,0,1} x'); - ^ -INSERT INTO LINE_TBL VALUES ('(3asdf,2 ,3,4r2)'); -ERROR: invalid input syntax for type line: "(3asdf,2 ,3,4r2)" -LINE 1: INSERT INTO LINE_TBL VALUES ('(3asdf,2 ,3,4r2)'); - ^ -INSERT INTO LINE_TBL VALUES ('[1,2,3, 4'); -ERROR: invalid input syntax for type line: "[1,2,3, 4" -LINE 1: INSERT INTO LINE_TBL VALUES ('[1,2,3, 4'); - ^ -INSERT INTO LINE_TBL VALUES ('[(,2),(3,4)]'); -ERROR: invalid input syntax for type line: "[(,2),(3,4)]" -LINE 1: INSERT INTO LINE_TBL VALUES ('[(,2),(3,4)]'); - ^ -INSERT INTO LINE_TBL VALUES ('[(1,2),(3,4)'); -ERROR: invalid input syntax for type line: "[(1,2),(3,4)" -LINE 1: INSERT INTO LINE_TBL VALUES ('[(1,2),(3,4)'); - ^ -INSERT INTO LINE_TBL VALUES ('[(1,2),(1,2)]'); -ERROR: invalid line specification: must be two distinct points -LINE 1: INSERT INTO LINE_TBL VALUES ('[(1,2),(1,2)]'); - ^ -INSERT INTO LINE_TBL VALUES (line(point '(1,0)', point '(1,0)')); -ERROR: invalid line specification: must be two distinct points -select * from LINE_TBL; - s ------------------------------------------------- - {0,-1,5} - {1,0,5} - {0,3,0} - {1,-1,0} - {-0.4,-1,-6} - {-0.0001846153846153846,-1,15.384615384615387} - {3,NaN,5} - {NaN,NaN,NaN} - {0,-1,3} - {-1,0,3} -(10 rows) - -select '{nan, 1, nan}'::line = '{nan, 1, nan}'::line as true, - '{nan, 1, nan}'::line = '{nan, 2, nan}'::line as false; - true | false -------+------- - t | f -(1 row) - --- test non-error-throwing API for some core types -SELECT pg_input_is_valid('{1, 1}', 'line'); - pg_input_is_valid -------------------- - f -(1 row) - -SELECT * FROM pg_input_error_info('{1, 1}', 'line'); - message | detail | hint | sql_error_code -----------------------------------------------+--------+------+---------------- - invalid input syntax for type line: "{1, 1}" | | | 22P02