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-- test overflow cases 57SELECT SUBSTRING('01010101'::bit(8) FROM 2 FOR 2147483646) AS "1010101"; 58SELECT SUBSTRING('01010101'::bit(8) FROM -10 FOR 2147483646) AS "01010101"; 59SELECT SUBSTRING('01010101'::bit(8) FROM -10 FOR -2147483646) AS "error"; 60SELECT SUBSTRING('01010101'::varbit FROM 2 FOR 2147483646) AS "1010101"; 61SELECT SUBSTRING('01010101'::varbit FROM -10 FOR 2147483646) AS "01010101"; 62SELECT SUBSTRING('01010101'::varbit FROM -10 FOR -2147483646) AS "error"; 63 64--- Bit operations 65DROP TABLE varbit_table; 66CREATE TABLE varbit_table (a BIT VARYING(16), b BIT VARYING(16)); 67COPY varbit_table FROM stdin; 68X0F X10 69X1F X11 70X2F X12 71X3F X13 72X8F X04 73X000F X0010 74X0123 XFFFF 75X2468 X2468 76XFA50 X05AF 77X1234 XFFF5 78\. 79 80SELECT a, b, ~a AS "~ a", a & b AS "a & b", 81 a | b AS "a | b", a # b AS "a # b" FROM varbit_table; 82SELECT a,b,a<b AS "a<b",a<=b AS "a<=b",a=b AS "a=b", 83 a>=b AS "a>=b",a>b AS "a>b",a<>b AS "a<>b" FROM varbit_table; 84SELECT a,a<<4 AS "a<<4",b,b>>2 AS "b>>2" FROM varbit_table; 85 86DROP TABLE varbit_table; 87 88--- Bit operations 89DROP TABLE bit_table; 90CREATE TABLE bit_table (a BIT(16), b BIT(16)); 91COPY bit_table FROM stdin; 92X0F00 X1000 93X1F00 X1100 94X2F00 X1200 95X3F00 X1300 96X8F00 X0400 97X000F X0010 98X0123 XFFFF 99X2468 X2468 100XFA50 X05AF 101X1234 XFFF5 102\. 103 104SELECT a,b,~a AS "~ a",a & b AS "a & b", 105 a|b AS "a | b", a # b AS "a # b" FROM bit_table; 106SELECT a,b,a<b AS "a<b",a<=b AS "a<=b",a=b AS "a=b", 107 a>=b AS "a>=b",a>b AS "a>b",a<>b AS "a<>b" FROM bit_table; 108SELECT a,a<<4 AS "a<<4",b,b>>2 AS "b>>2" FROM bit_table; 109 110DROP TABLE bit_table; 111 112 113-- The following should fail 114select B'001' & B'10'; 115select B'0111' | B'011'; 116select B'0010' # B'011101'; 117 118-- More position tests, checking all the boundary cases 119SELECT POSITION(B'1010' IN B'0000101'); -- 0 120SELECT POSITION(B'1010' IN B'00001010'); -- 5 121SELECT POSITION(B'1010' IN B'00000101'); -- 0 122SELECT POSITION(B'1010' IN B'000001010'); -- 6 123 124SELECT POSITION(B'' IN B'00001010'); -- 1 125SELECT POSITION(B'0' IN B''); -- 0 126SELECT POSITION(B'' IN B''); -- 0 127SELECT POSITION(B'101101' IN B'001011011011011000'); -- 3 128SELECT POSITION(B'10110110' IN B'001011011011010'); -- 3 129SELECT POSITION(B'1011011011011' IN B'001011011011011'); -- 3 130SELECT POSITION(B'1011011011011' IN B'00001011011011011'); -- 5 131 132SELECT POSITION(B'11101011' IN B'11101011'); -- 1 133SELECT POSITION(B'11101011' IN B'011101011'); -- 2 134SELECT POSITION(B'11101011' IN B'00011101011'); -- 4 135SELECT POSITION(B'11101011' IN B'0000011101011'); -- 6 136 137SELECT POSITION(B'111010110' IN B'111010110'); -- 1 138SELECT POSITION(B'111010110' IN B'0111010110'); -- 2 139SELECT POSITION(B'111010110' IN B'000111010110'); -- 4 140SELECT POSITION(B'111010110' IN B'00000111010110'); -- 6 141 142SELECT POSITION(B'111010110' IN B'11101011'); -- 0 143SELECT POSITION(B'111010110' IN B'011101011'); -- 0 144SELECT POSITION(B'111010110' IN B'00011101011'); -- 0 145SELECT POSITION(B'111010110' IN B'0000011101011'); -- 0 146 147SELECT POSITION(B'111010110' IN B'111010110'); -- 1 148SELECT POSITION(B'111010110' IN B'0111010110'); -- 2 149SELECT POSITION(B'111010110' IN B'000111010110'); -- 4 150SELECT POSITION(B'111010110' IN B'00000111010110'); -- 6 151 152SELECT POSITION(B'111010110' IN B'000001110101111101011'); -- 0 153SELECT POSITION(B'111010110' IN B'0000001110101111101011'); -- 0 154SELECT POSITION(B'111010110' IN B'000000001110101111101011'); -- 0 155SELECT POSITION(B'111010110' IN B'00000000001110101111101011'); -- 0 156 157SELECT POSITION(B'111010110' IN B'0000011101011111010110'); -- 14 158SELECT POSITION(B'111010110' IN B'00000011101011111010110'); -- 15 159SELECT POSITION(B'111010110' IN B'0000000011101011111010110'); -- 17 160SELECT POSITION(B'111010110' IN B'000000000011101011111010110'); -- 19 161 162SELECT POSITION(B'000000000011101011111010110' IN B'000000000011101011111010110'); -- 1 163SELECT POSITION(B'00000000011101011111010110' IN B'000000000011101011111010110'); -- 2 164SELECT POSITION(B'0000000000011101011111010110' IN B'000000000011101011111010110'); -- 0 165 166 167-- Shifting 168 169CREATE TABLE BIT_SHIFT_TABLE(b BIT(16)); 170INSERT INTO BIT_SHIFT_TABLE VALUES (B'1101100000000000'); 171INSERT INTO BIT_SHIFT_TABLE SELECT b>>1 FROM BIT_SHIFT_TABLE; 172INSERT INTO BIT_SHIFT_TABLE SELECT b>>2 FROM BIT_SHIFT_TABLE; 173INSERT INTO BIT_SHIFT_TABLE SELECT b>>4 FROM BIT_SHIFT_TABLE; 174INSERT INTO BIT_SHIFT_TABLE SELECT b>>8 FROM BIT_SHIFT_TABLE; 175SELECT POSITION(B'1101' IN b), 176 POSITION(B'11011' IN b), 177 b 178 FROM BIT_SHIFT_TABLE ; 179SELECT b, b >> 1 AS bsr, b << 1 AS bsl 180 FROM BIT_SHIFT_TABLE ; 181SELECT b, b >> 8 AS bsr8, b << 8 AS bsl8 182 FROM BIT_SHIFT_TABLE ; 183SELECT b::bit(15), b::bit(15) >> 1 AS bsr, b::bit(15) << 1 AS bsl 184 FROM BIT_SHIFT_TABLE ; 185SELECT b::bit(15), b::bit(15) >> 8 AS bsr8, b::bit(15) << 8 AS bsl8 186 FROM BIT_SHIFT_TABLE ; 187 188 189CREATE TABLE VARBIT_SHIFT_TABLE(v BIT VARYING(20)); 190INSERT INTO VARBIT_SHIFT_TABLE VALUES (B'11011'); 191INSERT INTO VARBIT_SHIFT_TABLE SELECT CAST(v || B'0' AS BIT VARYING(6)) >>1 FROM VARBIT_SHIFT_TABLE; 192INSERT INTO VARBIT_SHIFT_TABLE SELECT CAST(v || B'00' AS BIT VARYING(8)) >>2 FROM VARBIT_SHIFT_TABLE; 193INSERT INTO VARBIT_SHIFT_TABLE SELECT CAST(v || B'0000' AS BIT VARYING(12)) >>4 FROM VARBIT_SHIFT_TABLE; 194INSERT INTO VARBIT_SHIFT_TABLE SELECT CAST(v || B'00000000' AS BIT VARYING(20)) >>8 FROM VARBIT_SHIFT_TABLE; 195SELECT POSITION(B'1101' IN v), 196 POSITION(B'11011' IN v), 197 v 198 FROM VARBIT_SHIFT_TABLE ; 199SELECT v, v >> 1 AS vsr, v << 1 AS vsl 200 FROM VARBIT_SHIFT_TABLE ; 201SELECT v, v >> 8 AS vsr8, v << 8 AS vsl8 202 FROM VARBIT_SHIFT_TABLE ; 203 204DROP TABLE BIT_SHIFT_TABLE; 205DROP TABLE VARBIT_SHIFT_TABLE; 206 207-- Get/Set bit 208SELECT get_bit(B'0101011000100', 10); 209SELECT set_bit(B'0101011000100100', 15, 1); 210SELECT set_bit(B'0101011000100100', 16, 1); -- fail 211 212-- Overlay 213SELECT overlay(B'0101011100' placing '001' from 2 for 3); 214SELECT overlay(B'0101011100' placing '101' from 6); 215SELECT overlay(B'0101011100' placing '001' from 11); 216SELECT overlay(B'0101011100' placing '001' from 20); 217 218-- bit_count 219SELECT bit_count(B'0101011100'::bit(10)); 220SELECT bit_count(B'1111111111'::bit(10)); 221 222-- This table is intentionally left around to exercise pg_dump/pg_upgrade 223CREATE TABLE bit_defaults( 224 b1 bit(4) DEFAULT '1001', 225 b2 bit(4) DEFAULT B'0101', 226 b3 bit varying(5) DEFAULT '1001', 227 b4 bit varying(5) DEFAULT B'0101' 228); 229\d bit_defaults 230INSERT INTO bit_defaults DEFAULT VALUES; 231TABLE bit_defaults; 232