YQL-19206 Move contrib/ydb/library/yql/tests/postgresql -> yql/essentials/tests/postgresql

commit_hash:46fdf59714b20cf2b61233a06e58365227d3c8b2

1 files changed, 748 insertions, 0 deletions

diff --git a/yql/essentials/tests/postgresql/original/cases/bit.out b/yql/essentials/tests/postgresql/original/cases/bit.out
new file mode 100644
index 0000000000..a5aab9c0e3
--- /dev/null
+++ b/yql/essentials/tests/postgresql/original/cases/bit.out
@@ -0,0 +1,748 @@
+--
+-- BIT types
+--
+--
+-- Build tables for testing
+--
+CREATE TABLE BIT_TABLE(b BIT(11));
+INSERT INTO BIT_TABLE VALUES (B'10'); -- too short
+ERROR:  bit string length 2 does not match type bit(11)
+INSERT INTO BIT_TABLE VALUES (B'00000000000');
+INSERT INTO BIT_TABLE VALUES (B'11011000000');
+INSERT INTO BIT_TABLE VALUES (B'01010101010');
+INSERT INTO BIT_TABLE VALUES (B'101011111010'); -- too long
+ERROR:  bit string length 12 does not match type bit(11)
+--INSERT INTO BIT_TABLE VALUES ('X554');
+--INSERT INTO BIT_TABLE VALUES ('X555');
+SELECT * FROM BIT_TABLE;
+      b      
+-------------
+ 00000000000
+ 11011000000
+ 01010101010
+(3 rows)
+
+CREATE TABLE VARBIT_TABLE(v BIT VARYING(11));
+INSERT INTO VARBIT_TABLE VALUES (B'');
+INSERT INTO VARBIT_TABLE VALUES (B'0');
+INSERT INTO VARBIT_TABLE VALUES (B'010101');
+INSERT INTO VARBIT_TABLE VALUES (B'01010101010');
+INSERT INTO VARBIT_TABLE VALUES (B'101011111010'); -- too long
+ERROR:  bit string too long for type bit varying(11)
+--INSERT INTO VARBIT_TABLE VALUES ('X554');
+--INSERT INTO VARBIT_TABLE VALUES ('X555');
+SELECT * FROM VARBIT_TABLE;
+      v      
+-------------
+ 
+ 0
+ 010101
+ 01010101010
+(4 rows)
+
+-- Concatenation
+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",
+        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 | 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",
+        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 | 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)
+
+-- 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)
+