-- -- BIT types -- -- -- Build tables for testing -- CREATE TABLE BIT_TABLE(b BIT(11)); INSERT INTO BIT_TABLE VALUES (B'00000000000'); INSERT INTO BIT_TABLE VALUES (B'11011000000'); INSERT INTO BIT_TABLE VALUES (B'01010101010'); 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'); -- 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 CREATE TABLE varbit_table (a BIT VARYING(16), b BIT VARYING(16)); DROP TABLE varbit_table; CREATE TABLE bit_table (a BIT(16), b BIT(16)); 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 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 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' );