bit.sql
text/plain
--
-- BIT types
--
--
-- Build tables for testing
--
CREATE TABLE ZPBIT_TABLE(b BIT(11));
INSERT INTO ZPBIT_TABLE VALUES ('B');
INSERT INTO ZPBIT_TABLE VALUES ('B0');
INSERT INTO ZPBIT_TABLE VALUES ('B010101');
INSERT INTO ZPBIT_TABLE VALUES ('B01010101010');
INSERT INTO ZPBIT_TABLE VALUES ('B010101010101');
INSERT INTO ZPBIT_TABLE VALUES ('X554');
INSERT INTO ZPBIT_TABLE VALUES ('X555');
SELECT * FROM ZPBIT_TABLE;
CREATE TABLE VARBIT_TABLE(v BIT VARYING(11));
INSERT INTO VARBIT_TABLE VALUES ('B');
INSERT INTO VARBIT_TABLE VALUES ('B0');
INSERT INTO VARBIT_TABLE VALUES ('B010101');
INSERT INTO VARBIT_TABLE VALUES ('B01010101010');
INSERT INTO VARBIT_TABLE VALUES ('B010101010101');
INSERT INTO VARBIT_TABLE VALUES ('X554');
INSERT INTO VARBIT_TABLE VALUES ('X555');
SELECT * FROM VARBIT_TABLE;
-- Delete from tables
DROP TABLE ZPBIT_TABLE;
CREATE TABLE ZPBIT_TABLE(b BIT(16));
INSERT INTO ZPBIT_TABLE VALUES ('B11011');
INSERT INTO ZPBIT_TABLE SELECT b>>1 FROM ZPBIT_TABLE;
INSERT INTO ZPBIT_TABLE SELECT b>>2 FROM ZPBIT_TABLE;
INSERT INTO ZPBIT_TABLE SELECT b>>4 FROM ZPBIT_TABLE;
INSERT INTO ZPBIT_TABLE SELECT b>>8 FROM ZPBIT_TABLE;
SELECT POSITION('B1101'::bit IN b) as pos,
POSITION('B11011'::bit IN b) as pos,
b
FROM ZPBIT_TABLE ;
DROP TABLE VARBIT_TABLE;
CREATE TABLE VARBIT_TABLE(v BIT VARYING(19));
INSERT INTO VARBIT_TABLE VALUES ('B11011');
INSERT INTO VARBIT_TABLE SELECT v>>1 FROM VARBIT_TABLE;
INSERT INTO VARBIT_TABLE SELECT v>>2 FROM VARBIT_TABLE;
INSERT INTO VARBIT_TABLE SELECT v>>4 FROM VARBIT_TABLE;
INSERT INTO VARBIT_TABLE SELECT v>>8 FROM VARBIT_TABLE;
SELECT POSITION('B1101'::bit IN v) as pos,
POSITION('B11011'::bit IN v) as pos,
v
FROM VARBIT_TABLE ;
-- Concatenation
SELECT v, b::varbit AS B, (v || b)::varbit AS C
FROM ZPBIT_TABLE, VARBIT_TABLE
WHERE v::bit(16)=b
ORDER BY C;
-- Length
SELECT b, length(b) AS lb
FROM ZPBIT_TABLE;
SELECT v, length(v) AS lv
FROM VARBIT_TABLE;
-- Substring
SELECT b::varbit,
SUBSTRING(b FROM 2 FOR 4)::varbit AS sub1,
SUBSTRING(b FROM 7 FOR 13)::varbit AS sub2,
SUBSTRING(b FROM 6)::varbit AS sub3
FROM ZPBIT_TABLE;
SELECT v,
SUBSTRING(v FROM 2 FOR 4)::varbit AS sub1,
SUBSTRING(v FROM 7 FOR 13)::varbit AS sub2,
SUBSTRING(v FROM 6)::varbit AS sub3
FROM VARBIT_TABLE;
-- Drop the tables
DROP TABLE ZPBIT_TABLE;
DROP TABLE VARBIT_TABLE;
--- Bit operations
CREATE TABLE varbit_table (a BIT VARYING(16), b BIT VARYING(16));
COPY varbit_table FROM stdin;
X0F X10
X1F X11
X2F X12
X3F X13
X8F X04
X000F X0010
X0123 XFFFF
X2468 X2468
XFA50 X05AF
X1234 XFFF5
\.
SELECT a,b,~a AS "~ a",a & b AS "a & b",
a|b AS "a | b", a^b AS "a ^ b" FROM varbit_table;
SELECT a,b,a<b AS "a<b",a<=b AS "a<=b",a=b AS "a=b",
a>=b AS "a>=b",a>b AS "a>b",a<=>b AS "a<=>b" FROM varbit_table;
SELECT a,a<<4 AS "a<<4",b,b>>2 AS "b>>2" FROM varbit_table;
DROP TABLE varbit_table;
--- Bit operations
CREATE TABLE zpbit (a BIT(16), b BIT(16));
COPY zpbit FROM stdin;
X0F X10
X1F X11
X2F X12
X3F X13
X8F X04
X000F X0010
X0123 XFFFF
X2468 X2468
XFA50 X05AF
X1234 XFFF5
\.
SELECT a,b,~a AS "~ a",a & b AS "a & b",
a|b AS "a | b", a^b AS "a ^ b" FROM zpbit;
SELECT a,b,a<b AS "a<b",a<=b AS "a<=b",a=b AS "a=b",
a>=b AS "a>=b",a>b AS "a>b",a<>b AS "a<>b" FROM zpbit;
SELECT a,a<<4 AS "a<<4",b,b>>2 AS "b>>2" FROM zpbit;
DROP TABLE zpbit;
-- The following should fail
select 'X123'::bit & 'X12'::bit;
select 'B0111'::bit | 'B011'::bit;
select 'X023'::bit ^ 'B011101'::bit;
-- More position tests, checking all the boundary cases
SELECT POSITION('B1010'::bit IN 'B0000101'::bit); -- 0
SELECT POSITION('B1010'::bit IN 'B00001010'::bit); -- 5
SELECT POSITION('B1010'::bit IN 'B00000101'::bit); -- 0
SELECT POSITION('B1010'::bit IN 'B000001010'::bit); -- 6
SELECT POSITION('B'::bit IN 'B00001010'::bit); -- 1
SELECT POSITION('B0'::bit IN 'B'::bit); -- 0
SELECT POSITION('B'::bit IN 'B'::bit); -- 0
SELECT POSITION('B101101'::bit IN 'B001011011011011000'::bit); -- 3
SELECT POSITION('B10110110'::bit IN 'B001011011011010'::bit); -- 3
SELECT POSITION('B1011011011011'::bit IN 'B001011011011011'::bit); -- 3
SELECT POSITION('B1011011011011'::bit IN 'B00001011011011011'::bit); -- 5
SELECT POSITION('B11101011'::bit IN 'B11101011'::bit); -- 1
SELECT POSITION('B11101011'::bit IN 'B011101011'::bit); -- 2
SELECT POSITION('B11101011'::bit IN 'B00011101011'::bit); -- 4
SELECT POSITION('B11101011'::bit IN 'B0000011101011'::bit); -- 6
SELECT POSITION('B111010110'::bit IN 'B111010110'::bit); -- 1
SELECT POSITION('B111010110'::bit IN 'B0111010110'::bit); -- 2
SELECT POSITION('B111010110'::bit IN 'B000111010110'::bit); -- 4
SELECT POSITION('B111010110'::bit IN 'B00000111010110'::bit); -- 6
SELECT POSITION('B111010110'::bit IN 'B11101011'::bit); -- 0
SELECT POSITION('B111010110'::bit IN 'B011101011'::bit); -- 0
SELECT POSITION('B111010110'::bit IN 'B00011101011'::bit); -- 0
SELECT POSITION('B111010110'::bit IN 'B0000011101011'::bit); -- 0
SELECT POSITION('B111010110'::bit IN 'B111010110'::bit); -- 1
SELECT POSITION('B111010110'::bit IN 'B0111010110'::bit); -- 2
SELECT POSITION('B111010110'::bit IN 'B000111010110'::bit); -- 4
SELECT POSITION('B111010110'::bit IN 'B00000111010110'::bit); -- 6
SELECT POSITION('B111010110'::bit IN 'B000001110101111101011'::bit); -- 0
SELECT POSITION('B111010110'::bit IN 'B0000001110101111101011'::bit); -- 0
SELECT POSITION('B111010110'::bit IN 'B000000001110101111101011'::bit); -- 0
SELECT POSITION('B111010110'::bit IN 'B00000000001110101111101011'::bit); -- 0
SELECT POSITION('B111010110'::bit IN 'B0000011101011111010110'::bit); -- 14
SELECT POSITION('B111010110'::bit IN 'B00000011101011111010110'::bit); -- 15
SELECT POSITION('B111010110'::bit IN 'B0000000011101011111010110'::bit); -- 17
SELECT POSITION('B111010110'::bit IN 'B000000000011101011111010110'::bit); -- 19
SELECT POSITION('B000000000011101011111010110'::bit IN 'B000000000011101011111010110'::bit); -- 1
SELECT POSITION('B00000000011101011111010110'::bit IN 'B000000000011101011111010110'::bit); -- 2
SELECT POSITION('B0000000000011101011111010110'::bit IN 'B000000000011101011111010110'::bit); -- 0