6 -- Build tables for testing
9 CREATE TABLE BIT_TABLE(b BIT(11));
11 INSERT INTO BIT_TABLE VALUES (B'10'); -- too short
12 INSERT INTO BIT_TABLE VALUES (B'00000000000');
13 INSERT INTO BIT_TABLE VALUES (B'11011000000');
14 INSERT INTO BIT_TABLE VALUES (B'01010101010');
15 INSERT INTO BIT_TABLE VALUES (B'101011111010'); -- too long
16 --INSERT INTO BIT_TABLE VALUES ('X554');
17 --INSERT INTO BIT_TABLE VALUES ('X555');
19 SELECT * FROM BIT_TABLE;
21 CREATE TABLE VARBIT_TABLE(v BIT VARYING(11));
23 INSERT INTO VARBIT_TABLE VALUES (B'');
24 INSERT INTO VARBIT_TABLE VALUES (B'0');
25 INSERT INTO VARBIT_TABLE VALUES (B'010101');
26 INSERT INTO VARBIT_TABLE VALUES (B'01010101010');
27 INSERT INTO VARBIT_TABLE VALUES (B'101011111010'); -- too long
28 --INSERT INTO VARBIT_TABLE VALUES ('X554');
29 --INSERT INTO VARBIT_TABLE VALUES ('X555');
30 SELECT * FROM VARBIT_TABLE;
32 -- Literals with syntax errors
39 SELECT v, b, (v || b) AS concat
40 FROM BIT_TABLE, VARBIT_TABLE
44 SELECT b, length(b) AS lb
46 SELECT v, length(v) AS lv
51 SUBSTRING(b FROM 2 FOR 4) AS sub_2_4,
52 SUBSTRING(b FROM 7 FOR 13) AS sub_7_13,
53 SUBSTRING(b FROM 6) AS sub_6
56 SUBSTRING(v FROM 2 FOR 4) AS sub_2_4,
57 SUBSTRING(v FROM 7 FOR 13) AS sub_7_13,
58 SUBSTRING(v FROM 6) AS sub_6
61 -- test overflow cases
62 SELECT SUBSTRING('01010101'::bit(8) FROM 2 FOR 2147483646) AS "1010101";
63 SELECT SUBSTRING('01010101'::bit(8) FROM -10 FOR 2147483646) AS "01010101";
64 SELECT SUBSTRING('01010101'::bit(8) FROM -10 FOR -2147483646) AS "error";
65 SELECT SUBSTRING('01010101'::varbit FROM 2 FOR 2147483646) AS "1010101";
66 SELECT SUBSTRING('01010101'::varbit FROM -10 FOR 2147483646) AS "01010101";
67 SELECT SUBSTRING('01010101'::varbit FROM -10 FOR -2147483646) AS "error";
70 DROP TABLE varbit_table;
71 CREATE TABLE varbit_table (a BIT VARYING(16), b BIT VARYING(16));
72 COPY varbit_table FROM stdin;
85 SELECT a, b, ~a AS "~ a", a & b AS "a & b",
86 a | b AS "a | b", a # b AS "a # b" FROM varbit_table;
87 SELECT a,b,a<b AS "a<b",a<=b AS "a<=b",a=b AS "a=b",
88 a>=b AS "a>=b",a>b AS "a>b",a<>b AS "a<>b" FROM varbit_table;
89 SELECT a,a<<4 AS "a<<4",b,b>>2 AS "b>>2" FROM varbit_table;
91 DROP TABLE varbit_table;
95 CREATE TABLE bit_table (a BIT(16), b BIT(16));
96 COPY bit_table FROM stdin;
109 SELECT a,b,~a AS "~ a",a & b AS "a & b",
110 a|b AS "a | b", a # b AS "a # b" FROM bit_table;
111 SELECT a,b,a<b AS "a<b",a<=b AS "a<=b",a=b AS "a=b",
112 a>=b AS "a>=b",a>b AS "a>b",a<>b AS "a<>b" FROM bit_table;
113 SELECT a,a<<4 AS "a<<4",b,b>>2 AS "b>>2" FROM bit_table;
115 DROP TABLE bit_table;
118 -- The following should fail
119 select B'001' & B'10';
120 select B'0111' | B'011';
121 select B'0010' # B'011101';
123 -- More position tests, checking all the boundary cases
124 SELECT POSITION(B'1010' IN B'0000101'); -- 0
125 SELECT POSITION(B'1010' IN B'00001010'); -- 5
126 SELECT POSITION(B'1010' IN B'00000101'); -- 0
127 SELECT POSITION(B'1010' IN B'000001010'); -- 6
129 SELECT POSITION(B'' IN B'00001010'); -- 1
130 SELECT POSITION(B'0' IN B''); -- 0
131 SELECT POSITION(B'' IN B''); -- 0
132 SELECT POSITION(B'101101' IN B'001011011011011000'); -- 3
133 SELECT POSITION(B'10110110' IN B'001011011011010'); -- 3
134 SELECT POSITION(B'1011011011011' IN B'001011011011011'); -- 3
135 SELECT POSITION(B'1011011011011' IN B'00001011011011011'); -- 5
137 SELECT POSITION(B'11101011' IN B'11101011'); -- 1
138 SELECT POSITION(B'11101011' IN B'011101011'); -- 2
139 SELECT POSITION(B'11101011' IN B'00011101011'); -- 4
140 SELECT POSITION(B'11101011' IN B'0000011101011'); -- 6
142 SELECT POSITION(B'111010110' IN B'111010110'); -- 1
143 SELECT POSITION(B'111010110' IN B'0111010110'); -- 2
144 SELECT POSITION(B'111010110' IN B'000111010110'); -- 4
145 SELECT POSITION(B'111010110' IN B'00000111010110'); -- 6
147 SELECT POSITION(B'111010110' IN B'11101011'); -- 0
148 SELECT POSITION(B'111010110' IN B'011101011'); -- 0
149 SELECT POSITION(B'111010110' IN B'00011101011'); -- 0
150 SELECT POSITION(B'111010110' IN B'0000011101011'); -- 0
152 SELECT POSITION(B'111010110' IN B'111010110'); -- 1
153 SELECT POSITION(B'111010110' IN B'0111010110'); -- 2
154 SELECT POSITION(B'111010110' IN B'000111010110'); -- 4
155 SELECT POSITION(B'111010110' IN B'00000111010110'); -- 6
157 SELECT POSITION(B'111010110' IN B'000001110101111101011'); -- 0
158 SELECT POSITION(B'111010110' IN B'0000001110101111101011'); -- 0
159 SELECT POSITION(B'111010110' IN B'000000001110101111101011'); -- 0
160 SELECT POSITION(B'111010110' IN B'00000000001110101111101011'); -- 0
162 SELECT POSITION(B'111010110' IN B'0000011101011111010110'); -- 14
163 SELECT POSITION(B'111010110' IN B'00000011101011111010110'); -- 15
164 SELECT POSITION(B'111010110' IN B'0000000011101011111010110'); -- 17
165 SELECT POSITION(B'111010110' IN B'000000000011101011111010110'); -- 19
167 SELECT POSITION(B'000000000011101011111010110' IN B'000000000011101011111010110'); -- 1
168 SELECT POSITION(B'00000000011101011111010110' IN B'000000000011101011111010110'); -- 2
169 SELECT POSITION(B'0000000000011101011111010110' IN B'000000000011101011111010110'); -- 0
174 CREATE TABLE BIT_SHIFT_TABLE(b BIT(16));
175 INSERT INTO BIT_SHIFT_TABLE VALUES (B'1101100000000000');
176 INSERT INTO BIT_SHIFT_TABLE SELECT b>>1 FROM BIT_SHIFT_TABLE;
177 INSERT INTO BIT_SHIFT_TABLE SELECT b>>2 FROM BIT_SHIFT_TABLE;
178 INSERT INTO BIT_SHIFT_TABLE SELECT b>>4 FROM BIT_SHIFT_TABLE;
179 INSERT INTO BIT_SHIFT_TABLE SELECT b>>8 FROM BIT_SHIFT_TABLE;
180 SELECT POSITION(B'1101' IN b),
181 POSITION(B'11011' IN b),
183 FROM BIT_SHIFT_TABLE ;
184 SELECT b, b >> 1 AS bsr, b << 1 AS bsl
185 FROM BIT_SHIFT_TABLE ;
186 SELECT b, b >> 8 AS bsr8, b << 8 AS bsl8
187 FROM BIT_SHIFT_TABLE ;
188 SELECT b::bit(15), b::bit(15) >> 1 AS bsr, b::bit(15) << 1 AS bsl
189 FROM BIT_SHIFT_TABLE ;
190 SELECT b::bit(15), b::bit(15) >> 8 AS bsr8, b::bit(15) << 8 AS bsl8
191 FROM BIT_SHIFT_TABLE ;
194 CREATE TABLE VARBIT_SHIFT_TABLE(v BIT VARYING(20));
195 INSERT INTO VARBIT_SHIFT_TABLE VALUES (B'11011');
196 INSERT INTO VARBIT_SHIFT_TABLE SELECT CAST(v || B'0' AS BIT VARYING(6)) >>1 FROM VARBIT_SHIFT_TABLE;
197 INSERT INTO VARBIT_SHIFT_TABLE SELECT CAST(v || B'00' AS BIT VARYING(8)) >>2 FROM VARBIT_SHIFT_TABLE;
198 INSERT INTO VARBIT_SHIFT_TABLE SELECT CAST(v || B'0000' AS BIT VARYING(12)) >>4 FROM VARBIT_SHIFT_TABLE;
199 INSERT INTO VARBIT_SHIFT_TABLE SELECT CAST(v || B'00000000' AS BIT VARYING(20)) >>8 FROM VARBIT_SHIFT_TABLE;
200 SELECT POSITION(B'1101' IN v),
201 POSITION(B'11011' IN v),
203 FROM VARBIT_SHIFT_TABLE ;
204 SELECT v, v >> 1 AS vsr, v << 1 AS vsl
205 FROM VARBIT_SHIFT_TABLE ;
206 SELECT v, v >> 8 AS vsr8, v << 8 AS vsl8
207 FROM VARBIT_SHIFT_TABLE ;
209 DROP TABLE BIT_SHIFT_TABLE;
210 DROP TABLE VARBIT_SHIFT_TABLE;
213 SELECT get_bit(B'0101011000100', 10);
214 SELECT set_bit(B'0101011000100100', 15, 1);
215 SELECT set_bit(B'0101011000100100', 16, 1); -- fail
218 SELECT overlay(B'0101011100' placing '001' from 2 for 3);
219 SELECT overlay(B'0101011100' placing '101' from 6);
220 SELECT overlay(B'0101011100' placing '001' from 11);
221 SELECT overlay(B'0101011100' placing '001' from 20);
224 SELECT bit_count(B'0101011100'::bit(10));
225 SELECT bit_count(B'1111111111'::bit(10));
226 SELECT bit_count(repeat('0', 100)::bit(100));
227 SELECT bit_count(repeat('1', 100)::bit(100));
228 SELECT bit_count(repeat('01', 500)::bit(1000));
229 SELECT bit_count(repeat('10101', 200)::bit(1000));
231 -- This table is intentionally left around to exercise pg_dump/pg_upgrade
232 CREATE TABLE bit_defaults(
233 b1 bit(4) DEFAULT '1001',
234 b2 bit(4) DEFAULT B'0101',
235 b3 bit varying(5) DEFAULT '1001',
236 b4 bit varying(5) DEFAULT B'0101'
239 INSERT INTO bit_defaults DEFAULT VALUES;
242 -- test non-error-throwing API for some core types
243 SELECT pg_input_is_valid('01010001', 'bit(10)');
244 SELECT * FROM pg_input_error_info('01010001', 'bit(10)');
245 SELECT pg_input_is_valid('01010Z01', 'bit(8)');
246 SELECT * FROM pg_input_error_info('01010Z01', 'bit(8)');
247 SELECT pg_input_is_valid('x01010Z01', 'bit(32)');
248 SELECT * FROM pg_input_error_info('x01010Z01', 'bit(32)');
250 SELECT pg_input_is_valid('01010Z01', 'varbit');
251 SELECT * FROM pg_input_error_info('01010Z01', 'varbit');
252 SELECT pg_input_is_valid('x01010Z01', 'varbit');
253 SELECT * FROM pg_input_error_info('x01010Z01', 'varbit');