5
-- Build tables for testing
7
CREATE TABLE BIT_TABLE(b BIT(11));
8
INSERT INTO BIT_TABLE VALUES (B'10'); -- too short
9
ERROR: bit string length 2 does not match type bit(11)
10
INSERT INTO BIT_TABLE VALUES (B'00000000000');
11
INSERT INTO BIT_TABLE VALUES (B'11011000000');
12
INSERT INTO BIT_TABLE VALUES (B'01010101010');
13
INSERT INTO BIT_TABLE VALUES (B'101011111010'); -- too long
14
ERROR: bit string length 12 does not match type bit(11)
15
--INSERT INTO BIT_TABLE VALUES ('X554');
16
--INSERT INTO BIT_TABLE VALUES ('X555');
17
SELECT * FROM BIT_TABLE;
25
CREATE TABLE VARBIT_TABLE(v BIT VARYING(11));
26
INSERT INTO VARBIT_TABLE VALUES (B'');
27
INSERT INTO VARBIT_TABLE VALUES (B'0');
28
INSERT INTO VARBIT_TABLE VALUES (B'010101');
29
INSERT INTO VARBIT_TABLE VALUES (B'01010101010');
30
INSERT INTO VARBIT_TABLE VALUES (B'101011111010'); -- too long
31
ERROR: bit string too long for type bit varying(11)
32
--INSERT INTO VARBIT_TABLE VALUES ('X554');
33
--INSERT INTO VARBIT_TABLE VALUES ('X555');
34
SELECT * FROM VARBIT_TABLE;
44
SELECT v, b, (v || b) AS concat
45
FROM BIT_TABLE, VARBIT_TABLE
48
-------------+-------------+------------------------
49
| 00000000000 | 00000000000
50
0 | 00000000000 | 000000000000
51
0 | 01010101010 | 001010101010
52
010101 | 00000000000 | 01010100000000000
53
| 01010101010 | 01010101010
54
01010101010 | 00000000000 | 0101010101000000000000
55
01010101010 | 01010101010 | 0101010101001010101010
56
010101 | 01010101010 | 01010101010101010
57
01010101010 | 11011000000 | 0101010101011011000000
58
010101 | 11011000000 | 01010111011000000
59
0 | 11011000000 | 011011000000
60
| 11011000000 | 11011000000
64
SELECT b, length(b) AS lb
73
SELECT v, length(v) AS lv
85
SUBSTRING(b FROM 2 FOR 4) AS sub_2_4,
86
SUBSTRING(b FROM 7 FOR 13) AS sub_7_13,
87
SUBSTRING(b FROM 6) AS sub_6
89
b | sub_2_4 | sub_7_13 | sub_6
90
-------------+---------+----------+--------
91
00000000000 | 0000 | 00000 | 000000
92
11011000000 | 1011 | 00000 | 000000
93
01010101010 | 1010 | 01010 | 101010
97
SUBSTRING(v FROM 2 FOR 4) AS sub_2_4,
98
SUBSTRING(v FROM 7 FOR 13) AS sub_7_13,
99
SUBSTRING(v FROM 6) AS sub_6
101
v | sub_2_4 | sub_7_13 | sub_6
102
-------------+---------+----------+--------
106
01010101010 | 1010 | 01010 | 101010
110
DROP TABLE varbit_table;
111
CREATE TABLE varbit_table (a BIT VARYING(16), b BIT VARYING(16));
112
COPY varbit_table FROM stdin;
113
SELECT a, b, ~a AS "~ a", a & b AS "a & b",
114
a | b AS "a | b", a # b AS "a # b" FROM varbit_table;
115
a | b | ~ a | a & b | a | b | a # b
116
------------------+------------------+------------------+------------------+------------------+------------------
117
00001111 | 00010000 | 11110000 | 00000000 | 00011111 | 00011111
118
00011111 | 00010001 | 11100000 | 00010001 | 00011111 | 00001110
119
00101111 | 00010010 | 11010000 | 00000010 | 00111111 | 00111101
120
00111111 | 00010011 | 11000000 | 00010011 | 00111111 | 00101100
121
10001111 | 00000100 | 01110000 | 00000100 | 10001111 | 10001011
122
0000000000001111 | 0000000000010000 | 1111111111110000 | 0000000000000000 | 0000000000011111 | 0000000000011111
123
0000000100100011 | 1111111111111111 | 1111111011011100 | 0000000100100011 | 1111111111111111 | 1111111011011100
124
0010010001101000 | 0010010001101000 | 1101101110010111 | 0010010001101000 | 0010010001101000 | 0000000000000000
125
1111101001010000 | 0000010110101111 | 0000010110101111 | 0000000000000000 | 1111111111111111 | 1111111111111111
126
0001001000110100 | 1111111111110101 | 1110110111001011 | 0001001000110100 | 1111111111110101 | 1110110111000001
129
SELECT a,b,a<b AS "a<b",a<=b AS "a<=b",a=b AS "a=b",
130
a>=b AS "a>=b",a>b AS "a>b",a<>b AS "a<>b" FROM varbit_table;
131
a | b | a<b | a<=b | a=b | a>=b | a>b | a<>b
132
------------------+------------------+-----+------+-----+------+-----+------
133
00001111 | 00010000 | t | t | f | f | f | t
134
00011111 | 00010001 | f | f | f | t | t | t
135
00101111 | 00010010 | f | f | f | t | t | t
136
00111111 | 00010011 | f | f | f | t | t | t
137
10001111 | 00000100 | f | f | f | t | t | t
138
0000000000001111 | 0000000000010000 | t | t | f | f | f | t
139
0000000100100011 | 1111111111111111 | t | t | f | f | f | t
140
0010010001101000 | 0010010001101000 | f | t | t | t | f | f
141
1111101001010000 | 0000010110101111 | f | f | f | t | t | t
142
0001001000110100 | 1111111111110101 | t | t | f | f | f | t
145
SELECT a,a<<4 AS "a<<4",b,b>>2 AS "b>>2" FROM varbit_table;
147
------------------+------------------+------------------+------------------
148
00001111 | 11110000 | 00010000 | 00000100
149
00011111 | 11110000 | 00010001 | 00000100
150
00101111 | 11110000 | 00010010 | 00000100
151
00111111 | 11110000 | 00010011 | 00000100
152
10001111 | 11110000 | 00000100 | 00000001
153
0000000000001111 | 0000000011110000 | 0000000000010000 | 0000000000000100
154
0000000100100011 | 0001001000110000 | 1111111111111111 | 0011111111111111
155
0010010001101000 | 0100011010000000 | 0010010001101000 | 0000100100011010
156
1111101001010000 | 1010010100000000 | 0000010110101111 | 0000000101101011
157
0001001000110100 | 0010001101000000 | 1111111111110101 | 0011111111111101
160
DROP TABLE varbit_table;
162
DROP TABLE bit_table;
163
CREATE TABLE bit_table (a BIT(16), b BIT(16));
164
COPY bit_table FROM stdin;
165
SELECT a,b,~a AS "~ a",a & b AS "a & b",
166
a|b AS "a | b", a # b AS "a # b" FROM bit_table;
167
a | b | ~ a | a & b | a | b | a # b
168
------------------+------------------+------------------+------------------+------------------+------------------
169
0000111100000000 | 0001000000000000 | 1111000011111111 | 0000000000000000 | 0001111100000000 | 0001111100000000
170
0001111100000000 | 0001000100000000 | 1110000011111111 | 0001000100000000 | 0001111100000000 | 0000111000000000
171
0010111100000000 | 0001001000000000 | 1101000011111111 | 0000001000000000 | 0011111100000000 | 0011110100000000
172
0011111100000000 | 0001001100000000 | 1100000011111111 | 0001001100000000 | 0011111100000000 | 0010110000000000
173
1000111100000000 | 0000010000000000 | 0111000011111111 | 0000010000000000 | 1000111100000000 | 1000101100000000
174
0000000000001111 | 0000000000010000 | 1111111111110000 | 0000000000000000 | 0000000000011111 | 0000000000011111
175
0000000100100011 | 1111111111111111 | 1111111011011100 | 0000000100100011 | 1111111111111111 | 1111111011011100
176
0010010001101000 | 0010010001101000 | 1101101110010111 | 0010010001101000 | 0010010001101000 | 0000000000000000
177
1111101001010000 | 0000010110101111 | 0000010110101111 | 0000000000000000 | 1111111111111111 | 1111111111111111
178
0001001000110100 | 1111111111110101 | 1110110111001011 | 0001001000110100 | 1111111111110101 | 1110110111000001
181
SELECT a,b,a<b AS "a<b",a<=b AS "a<=b",a=b AS "a=b",
182
a>=b AS "a>=b",a>b AS "a>b",a<>b AS "a<>b" FROM bit_table;
183
a | b | a<b | a<=b | a=b | a>=b | a>b | a<>b
184
------------------+------------------+-----+------+-----+------+-----+------
185
0000111100000000 | 0001000000000000 | t | t | f | f | f | t
186
0001111100000000 | 0001000100000000 | f | f | f | t | t | t
187
0010111100000000 | 0001001000000000 | f | f | f | t | t | t
188
0011111100000000 | 0001001100000000 | f | f | f | t | t | t
189
1000111100000000 | 0000010000000000 | f | f | f | t | t | t
190
0000000000001111 | 0000000000010000 | t | t | f | f | f | t
191
0000000100100011 | 1111111111111111 | t | t | f | f | f | t
192
0010010001101000 | 0010010001101000 | f | t | t | t | f | f
193
1111101001010000 | 0000010110101111 | f | f | f | t | t | t
194
0001001000110100 | 1111111111110101 | t | t | f | f | f | t
197
SELECT a,a<<4 AS "a<<4",b,b>>2 AS "b>>2" FROM bit_table;
199
------------------+------------------+------------------+------------------
200
0000111100000000 | 1111000000000000 | 0001000000000000 | 0000010000000000
201
0001111100000000 | 1111000000000000 | 0001000100000000 | 0000010001000000
202
0010111100000000 | 1111000000000000 | 0001001000000000 | 0000010010000000
203
0011111100000000 | 1111000000000000 | 0001001100000000 | 0000010011000000
204
1000111100000000 | 1111000000000000 | 0000010000000000 | 0000000100000000
205
0000000000001111 | 0000000011110000 | 0000000000010000 | 0000000000000100
206
0000000100100011 | 0001001000110000 | 1111111111111111 | 0011111111111111
207
0010010001101000 | 0100011010000000 | 0010010001101000 | 0000100100011010
208
1111101001010000 | 1010010100000000 | 0000010110101111 | 0000000101101011
209
0001001000110100 | 0010001101000000 | 1111111111110101 | 0011111111111101
212
DROP TABLE bit_table;
213
-- The following should fail
214
select B'001' & B'10';
215
ERROR: cannot AND bit strings of different sizes
216
select B'0111' | B'011';
217
ERROR: cannot OR bit strings of different sizes
218
select B'0010' # B'011101';
219
ERROR: cannot XOR bit strings of different sizes
220
-- More position tests, checking all the boundary cases
221
SELECT POSITION(B'1010' IN B'0000101'); -- 0
227
SELECT POSITION(B'1010' IN B'00001010'); -- 5
233
SELECT POSITION(B'1010' IN B'00000101'); -- 0
239
SELECT POSITION(B'1010' IN B'000001010'); -- 6
245
SELECT POSITION(B'' IN B'00001010'); -- 1
251
SELECT POSITION(B'0' IN B''); -- 0
257
SELECT POSITION(B'' IN B''); -- 0
263
SELECT POSITION(B'101101' IN B'001011011011011000'); -- 3
269
SELECT POSITION(B'10110110' IN B'001011011011010'); -- 3
275
SELECT POSITION(B'1011011011011' IN B'001011011011011'); -- 3
281
SELECT POSITION(B'1011011011011' IN B'00001011011011011'); -- 5
287
SELECT POSITION(B'11101011' IN B'11101011'); -- 1
293
SELECT POSITION(B'11101011' IN B'011101011'); -- 2
299
SELECT POSITION(B'11101011' IN B'00011101011'); -- 4
305
SELECT POSITION(B'11101011' IN B'0000011101011'); -- 6
311
SELECT POSITION(B'111010110' IN B'111010110'); -- 1
317
SELECT POSITION(B'111010110' IN B'0111010110'); -- 2
323
SELECT POSITION(B'111010110' IN B'000111010110'); -- 4
329
SELECT POSITION(B'111010110' IN B'00000111010110'); -- 6
335
SELECT POSITION(B'111010110' IN B'11101011'); -- 0
341
SELECT POSITION(B'111010110' IN B'011101011'); -- 0
347
SELECT POSITION(B'111010110' IN B'00011101011'); -- 0
353
SELECT POSITION(B'111010110' IN B'0000011101011'); -- 0
359
SELECT POSITION(B'111010110' IN B'111010110'); -- 1
365
SELECT POSITION(B'111010110' IN B'0111010110'); -- 2
371
SELECT POSITION(B'111010110' IN B'000111010110'); -- 4
377
SELECT POSITION(B'111010110' IN B'00000111010110'); -- 6
383
SELECT POSITION(B'111010110' IN B'000001110101111101011'); -- 0
389
SELECT POSITION(B'111010110' IN B'0000001110101111101011'); -- 0
395
SELECT POSITION(B'111010110' IN B'000000001110101111101011'); -- 0
401
SELECT POSITION(B'111010110' IN B'00000000001110101111101011'); -- 0
407
SELECT POSITION(B'111010110' IN B'0000011101011111010110'); -- 14
413
SELECT POSITION(B'111010110' IN B'00000011101011111010110'); -- 15
419
SELECT POSITION(B'111010110' IN B'0000000011101011111010110'); -- 17
425
SELECT POSITION(B'111010110' IN B'000000000011101011111010110'); -- 19
431
SELECT POSITION(B'000000000011101011111010110' IN B'000000000011101011111010110'); -- 1
437
SELECT POSITION(B'00000000011101011111010110' IN B'000000000011101011111010110'); -- 2
443
SELECT POSITION(B'0000000000011101011111010110' IN B'000000000011101011111010110'); -- 0
450
CREATE TABLE BIT_SHIFT_TABLE(b BIT(16));
451
INSERT INTO BIT_SHIFT_TABLE VALUES (B'1101100000000000');
452
INSERT INTO BIT_SHIFT_TABLE SELECT b>>1 FROM BIT_SHIFT_TABLE;
453
INSERT INTO BIT_SHIFT_TABLE SELECT b>>2 FROM BIT_SHIFT_TABLE;
454
INSERT INTO BIT_SHIFT_TABLE SELECT b>>4 FROM BIT_SHIFT_TABLE;
455
INSERT INTO BIT_SHIFT_TABLE SELECT b>>8 FROM BIT_SHIFT_TABLE;
456
SELECT POSITION(B'1101' IN b),
457
POSITION(B'11011' IN b),
459
FROM BIT_SHIFT_TABLE ;
460
position | position | b
461
----------+----------+------------------
462
1 | 1 | 1101100000000000
463
2 | 2 | 0110110000000000
464
3 | 3 | 0011011000000000
465
4 | 4 | 0001101100000000
466
5 | 5 | 0000110110000000
467
6 | 6 | 0000011011000000
468
7 | 7 | 0000001101100000
469
8 | 8 | 0000000110110000
470
9 | 9 | 0000000011011000
471
10 | 10 | 0000000001101100
472
11 | 11 | 0000000000110110
473
12 | 12 | 0000000000011011
474
13 | 0 | 0000000000001101
475
0 | 0 | 0000000000000110
476
0 | 0 | 0000000000000011
477
0 | 0 | 0000000000000001
480
CREATE TABLE VARBIT_SHIFT_TABLE(v BIT VARYING(20));
481
INSERT INTO VARBIT_SHIFT_TABLE VALUES (B'11011');
482
INSERT INTO VARBIT_SHIFT_TABLE SELECT CAST(v || B'0' AS BIT VARYING(6)) >>1 FROM VARBIT_SHIFT_TABLE;
483
INSERT INTO VARBIT_SHIFT_TABLE SELECT CAST(v || B'00' AS BIT VARYING(8)) >>2 FROM VARBIT_SHIFT_TABLE;
484
INSERT INTO VARBIT_SHIFT_TABLE SELECT CAST(v || B'0000' AS BIT VARYING(12)) >>4 FROM VARBIT_SHIFT_TABLE;
485
INSERT INTO VARBIT_SHIFT_TABLE SELECT CAST(v || B'00000000' AS BIT VARYING(20)) >>8 FROM VARBIT_SHIFT_TABLE;
486
SELECT POSITION(B'1101' IN v),
487
POSITION(B'11011' IN v),
489
FROM VARBIT_SHIFT_TABLE ;
490
position | position | v
491
----------+----------+----------------------
500
9 | 9 | 0000000011011
501
10 | 10 | 00000000011011
502
11 | 11 | 000000000011011
503
12 | 12 | 0000000000011011
504
13 | 13 | 00000000000011011
505
14 | 14 | 000000000000011011
506
15 | 15 | 0000000000000011011
507
16 | 16 | 00000000000000011011
510
DROP TABLE BIT_SHIFT_TABLE;
511
DROP TABLE VARBIT_SHIFT_TABLE;