diff options
Diffstat (limited to 'src/test/regress/expected/bit.out')
| -rw-r--r-- | src/test/regress/expected/bit.out | 809 |
1 files changed, 809 insertions, 0 deletions
diff --git a/src/test/regress/expected/bit.out b/src/test/regress/expected/bit.out new file mode 100644 index 0000000..98c2655 --- /dev/null +++ b/src/test/regress/expected/bit.out @@ -0,0 +1,809 @@ +-- +-- 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) + +-- 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) + |