1-- 2-- BIT types 3-- 4 5-- 6-- Build tables for testing 7-- 8 9CREATE TABLE BIT_TABLE(b BIT(11)); 10 11INSERT INTO BIT_TABLE VALUES (B'10'); -- too short 12INSERT INTO BIT_TABLE VALUES (B'00000000000'); 13INSERT INTO BIT_TABLE VALUES (B'11011000000'); 14INSERT INTO BIT_TABLE VALUES (B'01010101010'); 15INSERT INTO BIT_TABLE VALUES (B'101011111010'); -- too long 16--INSERT INTO BIT_TABLE VALUES ('X554'); 17--INSERT INTO BIT_TABLE VALUES ('X555'); 18 19SELECT * FROM BIT_TABLE; 20 21CREATE TABLE VARBIT_TABLE(v BIT VARYING(11)); 22 23INSERT INTO VARBIT_TABLE VALUES (B''); 24INSERT INTO VARBIT_TABLE VALUES (B'0'); 25INSERT INTO VARBIT_TABLE VALUES (B'010101'); 26INSERT INTO VARBIT_TABLE VALUES (B'01010101010'); 27INSERT INTO VARBIT_TABLE VALUES (B'101011111010'); -- too long 28--INSERT INTO VARBIT_TABLE VALUES ('X554'); 29--INSERT INTO VARBIT_TABLE VALUES ('X555'); 30SELECT * FROM VARBIT_TABLE; 31 32 33-- Concatenation 34SELECT v, b, (v || b) AS concat 35 FROM BIT_TABLE, VARBIT_TABLE 36 ORDER BY 3; 37 38-- Length 39SELECT b, length(b) AS lb 40 FROM BIT_TABLE; 41SELECT v, length(v) AS lv 42 FROM VARBIT_TABLE; 43 44-- Substring 45SELECT b, 46 SUBSTRING(b FROM 2 FOR 4) AS sub_2_4, 47 SUBSTRING(b FROM 7 FOR 13) AS sub_7_13, 48 SUBSTRING(b FROM 6) AS sub_6 49 FROM BIT_TABLE; 50SELECT v, 51 SUBSTRING(v FROM 2 FOR 4) AS sub_2_4, 52 SUBSTRING(v FROM 7 FOR 13) AS sub_7_13, 53 SUBSTRING(v FROM 6) AS sub_6 54 FROM VARBIT_TABLE; 55 56--- Bit operations 57DROP TABLE varbit_table; 58CREATE TABLE varbit_table (a BIT VARYING(16), b BIT VARYING(16)); 59COPY varbit_table FROM stdin; 60X0F X10 61X1F X11 62X2F X12 63X3F X13 64X8F X04 65X000F X0010 66X0123 XFFFF 67X2468 X2468 68XFA50 X05AF 69X1234 XFFF5 70\. 71 72SELECT a, b, ~a AS "~ a", a & b AS "a & b", 73 a | b AS "a | b", a # b AS "a # b" FROM varbit_table; 74SELECT a,b,a<b AS "a<b",a<=b AS "a<=b",a=b AS "a=b", 75 a>=b AS "a>=b",a>b AS "a>b",a<>b AS "a<>b" FROM varbit_table; 76SELECT a,a<<4 AS "a<<4",b,b>>2 AS "b>>2" FROM varbit_table; 77 78DROP TABLE varbit_table; 79 80--- Bit operations 81DROP TABLE bit_table; 82CREATE TABLE bit_table (a BIT(16), b BIT(16)); 83COPY bit_table FROM stdin; 84X0F00 X1000 85X1F00 X1100 86X2F00 X1200 87X3F00 X1300 88X8F00 X0400 89X000F X0010 90X0123 XFFFF 91X2468 X2468 92XFA50 X05AF 93X1234 XFFF5 94\. 95 96SELECT a,b,~a AS "~ a",a & b AS "a & b", 97 a|b AS "a | b", a # b AS "a # b" FROM bit_table; 98SELECT a,b,a<b AS "a<b",a<=b AS "a<=b",a=b AS "a=b", 99 a>=b AS "a>=b",a>b AS "a>b",a<>b AS "a<>b" FROM bit_table; 100SELECT a,a<<4 AS "a<<4",b,b>>2 AS "b>>2" FROM bit_table; 101 102DROP TABLE bit_table; 103 104 105-- The following should fail 106select B'001' & B'10'; 107select B'0111' | B'011'; 108select B'0010' # B'011101'; 109 110-- More position tests, checking all the boundary cases 111SELECT POSITION(B'1010' IN B'0000101'); -- 0 112SELECT POSITION(B'1010' IN B'00001010'); -- 5 113SELECT POSITION(B'1010' IN B'00000101'); -- 0 114SELECT POSITION(B'1010' IN B'000001010'); -- 6 115 116SELECT POSITION(B'' IN B'00001010'); -- 1 117SELECT POSITION(B'0' IN B''); -- 0 118SELECT POSITION(B'' IN B''); -- 0 119SELECT POSITION(B'101101' IN B'001011011011011000'); -- 3 120SELECT POSITION(B'10110110' IN B'001011011011010'); -- 3 121SELECT POSITION(B'1011011011011' IN B'001011011011011'); -- 3 122SELECT POSITION(B'1011011011011' IN B'00001011011011011'); -- 5 123 124SELECT POSITION(B'11101011' IN B'11101011'); -- 1 125SELECT POSITION(B'11101011' IN B'011101011'); -- 2 126SELECT POSITION(B'11101011' IN B'00011101011'); -- 4 127SELECT POSITION(B'11101011' IN B'0000011101011'); -- 6 128 129SELECT POSITION(B'111010110' IN B'111010110'); -- 1 130SELECT POSITION(B'111010110' IN B'0111010110'); -- 2 131SELECT POSITION(B'111010110' IN B'000111010110'); -- 4 132SELECT POSITION(B'111010110' IN B'00000111010110'); -- 6 133 134SELECT POSITION(B'111010110' IN B'11101011'); -- 0 135SELECT POSITION(B'111010110' IN B'011101011'); -- 0 136SELECT POSITION(B'111010110' IN B'00011101011'); -- 0 137SELECT POSITION(B'111010110' IN B'0000011101011'); -- 0 138 139SELECT POSITION(B'111010110' IN B'111010110'); -- 1 140SELECT POSITION(B'111010110' IN B'0111010110'); -- 2 141SELECT POSITION(B'111010110' IN B'000111010110'); -- 4 142SELECT POSITION(B'111010110' IN B'00000111010110'); -- 6 143 144SELECT POSITION(B'111010110' IN B'000001110101111101011'); -- 0 145SELECT POSITION(B'111010110' IN B'0000001110101111101011'); -- 0 146SELECT POSITION(B'111010110' IN B'000000001110101111101011'); -- 0 147SELECT POSITION(B'111010110' IN B'00000000001110101111101011'); -- 0 148 149SELECT POSITION(B'111010110' IN B'0000011101011111010110'); -- 14 150SELECT POSITION(B'111010110' IN B'00000011101011111010110'); -- 15 151SELECT POSITION(B'111010110' IN B'0000000011101011111010110'); -- 17 152SELECT POSITION(B'111010110' IN B'000000000011101011111010110'); -- 19 153 154SELECT POSITION(B'000000000011101011111010110' IN B'000000000011101011111010110'); -- 1 155SELECT POSITION(B'00000000011101011111010110' IN B'000000000011101011111010110'); -- 2 156SELECT POSITION(B'0000000000011101011111010110' IN B'000000000011101011111010110'); -- 0 157 158 159-- Shifting 160 161CREATE TABLE BIT_SHIFT_TABLE(b BIT(16)); 162INSERT INTO BIT_SHIFT_TABLE VALUES (B'1101100000000000'); 163INSERT INTO BIT_SHIFT_TABLE SELECT b>>1 FROM BIT_SHIFT_TABLE; 164INSERT INTO BIT_SHIFT_TABLE SELECT b>>2 FROM BIT_SHIFT_TABLE; 165INSERT INTO BIT_SHIFT_TABLE SELECT b>>4 FROM BIT_SHIFT_TABLE; 166INSERT INTO BIT_SHIFT_TABLE SELECT b>>8 FROM BIT_SHIFT_TABLE; 167SELECT POSITION(B'1101' IN b), 168 POSITION(B'11011' IN b), 169 b 170 FROM BIT_SHIFT_TABLE ; 171SELECT b, b >> 1 AS bsr, b << 1 AS bsl 172 FROM BIT_SHIFT_TABLE ; 173SELECT b, b >> 8 AS bsr8, b << 8 AS bsl8 174 FROM BIT_SHIFT_TABLE ; 175SELECT b::bit(15), b::bit(15) >> 1 AS bsr, b::bit(15) << 1 AS bsl 176 FROM BIT_SHIFT_TABLE ; 177SELECT b::bit(15), b::bit(15) >> 8 AS bsr8, b::bit(15) << 8 AS bsl8 178 FROM BIT_SHIFT_TABLE ; 179 180 181CREATE TABLE VARBIT_SHIFT_TABLE(v BIT VARYING(20)); 182INSERT INTO VARBIT_SHIFT_TABLE VALUES (B'11011'); 183INSERT INTO VARBIT_SHIFT_TABLE SELECT CAST(v || B'0' AS BIT VARYING(6)) >>1 FROM VARBIT_SHIFT_TABLE; 184INSERT INTO VARBIT_SHIFT_TABLE SELECT CAST(v || B'00' AS BIT VARYING(8)) >>2 FROM VARBIT_SHIFT_TABLE; 185INSERT INTO VARBIT_SHIFT_TABLE SELECT CAST(v || B'0000' AS BIT VARYING(12)) >>4 FROM VARBIT_SHIFT_TABLE; 186INSERT INTO VARBIT_SHIFT_TABLE SELECT CAST(v || B'00000000' AS BIT VARYING(20)) >>8 FROM VARBIT_SHIFT_TABLE; 187SELECT POSITION(B'1101' IN v), 188 POSITION(B'11011' IN v), 189 v 190 FROM VARBIT_SHIFT_TABLE ; 191SELECT v, v >> 1 AS vsr, v << 1 AS vsl 192 FROM VARBIT_SHIFT_TABLE ; 193SELECT v, v >> 8 AS vsr8, v << 8 AS vsl8 194 FROM VARBIT_SHIFT_TABLE ; 195 196DROP TABLE BIT_SHIFT_TABLE; 197DROP TABLE VARBIT_SHIFT_TABLE; 198 199-- Get/Set bit 200SELECT get_bit(B'0101011000100', 10); 201SELECT set_bit(B'0101011000100100', 15, 1); 202SELECT set_bit(B'0101011000100100', 16, 1); -- fail 203 204-- Overlay 205SELECT overlay(B'0101011100' placing '001' from 2 for 3); 206SELECT overlay(B'0101011100' placing '101' from 6); 207SELECT overlay(B'0101011100' placing '001' from 11); 208SELECT overlay(B'0101011100' placing '001' from 20); 209