bit.out

PostgreSQL 8.1.4的源码 适用于Linux下的开源数据库系统

--
-- BIT types
--
--
-- Build tables for testing
--
CREATE TABLE BIT_TABLE(b BIT(11));
INSERT INTO BIT_TABLE VALUES (B'10'); -- too short
ERROR:  bit string length 2 does not match type bit(11)
INSERT INTO BIT_TABLE VALUES (B'00000000000');
INSERT INTO BIT_TABLE VALUES (B'11011000000');
INSERT INTO BIT_TABLE VALUES (B'01010101010');
INSERT INTO BIT_TABLE VALUES (B'101011111010'); -- too long
ERROR:  bit string length 12 does not match type bit(11)
--INSERT INTO BIT_TABLE VALUES ('X554');
--INSERT INTO BIT_TABLE VALUES ('X555');
SELECT * FROM BIT_TABLE; 
      b      
-------------
 00000000000
 11011000000
 01010101010
(3 rows)

CREATE TABLE VARBIT_TABLE(v BIT VARYING(11));
INSERT INTO VARBIT_TABLE VALUES (B'');
INSERT INTO VARBIT_TABLE VALUES (B'0');
INSERT INTO VARBIT_TABLE VALUES (B'010101');
INSERT INTO VARBIT_TABLE VALUES (B'01010101010');
INSERT INTO VARBIT_TABLE VALUES (B'101011111010'); -- too long
ERROR:  bit string too long for type bit varying(11)
--INSERT INTO VARBIT_TABLE VALUES ('X554');
--INSERT INTO VARBIT_TABLE VALUES ('X555');
SELECT * FROM VARBIT_TABLE; 
      v      
-------------
 
 0
 010101
 01010101010
(4 rows)

-- Concatenation
SELECT v, b, (v || b) AS concat
       FROM BIT_TABLE, VARBIT_TABLE 
       ORDER BY 3;
      v      |      b      |         concat         
-------------+-------------+------------------------
             | 00000000000 | 00000000000
 0           | 00000000000 | 000000000000
 0           | 01010101010 | 001010101010
 010101      | 00000000000 | 01010100000000000
             | 01010101010 | 01010101010
 01010101010 | 00000000000 | 0101010101000000000000
 01010101010 | 01010101010 | 0101010101001010101010
 010101      | 01010101010 | 01010101010101010
 01010101010 | 11011000000 | 0101010101011011000000
 010101      | 11011000000 | 01010111011000000
 0           | 11011000000 | 011011000000
             | 11011000000 | 11011000000
(12 rows)

-- Length
SELECT b, length(b) AS lb
       FROM BIT_TABLE;
      b      | lb 
-------------+----
 00000000000 | 11
 11011000000 | 11
 01010101010 | 11
(3 rows)

SELECT v, length(v) AS lv
       FROM VARBIT_TABLE;
      v      | lv 
-------------+----
             |  0
 0           |  1
 010101      |  6
 01010101010 | 11
(4 rows)

-- Substring
SELECT b,
       SUBSTRING(b FROM 2 FOR 4) AS sub_2_4,
       SUBSTRING(b FROM 7 FOR 13) AS sub_7_13,
       SUBSTRING(b FROM 6) AS sub_6
       FROM BIT_TABLE;
      b      | sub_2_4 | sub_7_13 | sub_6  
-------------+---------+----------+--------
 00000000000 | 0000    | 00000    | 000000
 11011000000 | 1011    | 00000    | 000000
 01010101010 | 1010    | 01010    | 101010
(3 rows)

SELECT v,
       SUBSTRING(v FROM 2 FOR 4) AS sub_2_4,
       SUBSTRING(v FROM 7 FOR 13) AS sub_7_13,
       SUBSTRING(v FROM 6) AS sub_6
       FROM VARBIT_TABLE;
      v      | sub_2_4 | sub_7_13 | sub_6  
-------------+---------+----------+--------
             |         |          | 
 0           |         |          | 
 010101      | 1010    |          | 1
 01010101010 | 1010    | 01010    | 101010
(4 rows)

--- Bit operations
DROP TABLE varbit_table;
CREATE TABLE varbit_table (a BIT VARYING(16), b BIT VARYING(16));
COPY varbit_table FROM stdin;
SELECT a, b, ~a AS "~ a", a & b AS "a & b", 
       a | b AS "a | b", a # b AS "a # b" FROM varbit_table;
        a         |        b         |       ~ a        |      a & b       |      a | b       |      a # b       
------------------+------------------+------------------+------------------+------------------+------------------
 00001111         | 00010000         | 11110000         | 00000000         | 00011111         | 00011111
 00011111         | 00010001         | 11100000         | 00010001         | 00011111         | 00001110
 00101111         | 00010010         | 11010000         | 00000010         | 00111111         | 00111101
 00111111         | 00010011         | 11000000         | 00010011         | 00111111         | 00101100
 10001111         | 00000100         | 01110000         | 00000100         | 10001111         | 10001011
 0000000000001111 | 0000000000010000 | 1111111111110000 | 0000000000000000 | 0000000000011111 | 0000000000011111
 0000000100100011 | 1111111111111111 | 1111111011011100 | 0000000100100011 | 1111111111111111 | 1111111011011100
 0010010001101000 | 0010010001101000 | 1101101110010111 | 0010010001101000 | 0010010001101000 | 0000000000000000
 1111101001010000 | 0000010110101111 | 0000010110101111 | 0000000000000000 | 1111111111111111 | 1111111111111111
 0001001000110100 | 1111111111110101 | 1110110111001011 | 0001001000110100 | 1111111111110101 | 1110110111000001
(10 rows)

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;
        a         |        b         | a<b | a<=b | a=b | a>=b | a>b | a<>b 
------------------+------------------+-----+------+-----+------+-----+------
 00001111         | 00010000         | t   | t    | f   | f    | f   | t
 00011111         | 00010001         | f   | f    | f   | t    | t   | t
 00101111         | 00010010         | f   | f    | f   | t    | t   | t
 00111111         | 00010011         | f   | f    | f   | t    | t   | t
 10001111         | 00000100         | f   | f    | f   | t    | t   | t
 0000000000001111 | 0000000000010000 | t   | t    | f   | f    | f   | t
 0000000100100011 | 1111111111111111 | t   | t    | f   | f    | f   | t
 0010010001101000 | 0010010001101000 | f   | t    | t   | t    | f   | f
 1111101001010000 | 0000010110101111 | f   | f    | f   | t    | t   | t
 0001001000110100 | 1111111111110101 | t   | t    | f   | f    | f   | t
(10 rows)

SELECT a,a<<4 AS "a<<4",b,b>>2 AS "b>>2" FROM varbit_table;
        a         |       a<<4       |        b         |       b>>2       
------------------+------------------+------------------+------------------
 00001111         | 11110000         | 00010000         | 00000100
 00011111         | 11110000         | 00010001         | 00000100
 00101111         | 11110000         | 00010010         | 00000100
 00111111         | 11110000         | 00010011         | 00000100
 10001111         | 11110000         | 00000100         | 00000001
 0000000000001111 | 0000000011110000 | 0000000000010000 | 0000000000000100
 0000000100100011 | 0001001000110000 | 1111111111111111 | 0011111111111111
 0010010001101000 | 0100011010000000 | 0010010001101000 | 0000100100011010
 1111101001010000 | 1010010100000000 | 0000010110101111 | 0000000101101011
 0001001000110100 | 0010001101000000 | 1111111111110101 | 0011111111111101
(10 rows)

DROP TABLE varbit_table;
--- Bit operations
DROP TABLE bit_table;
CREATE TABLE bit_table (a BIT(16), b BIT(16));
COPY bit_table FROM stdin;
SELECT a,b,~a AS "~ a",a & b AS "a & b", 
	a|b AS "a | b", a # b AS "a # b" FROM bit_table;
        a         |        b         |       ~ a        |      a & b       |      a | b       |      a # b       
------------------+------------------+------------------+------------------+------------------+------------------
 0000111100000000 | 0001000000000000 | 1111000011111111 | 0000000000000000 | 0001111100000000 | 0001111100000000
 0001111100000000 | 0001000100000000 | 1110000011111111 | 0001000100000000 | 0001111100000000 | 0000111000000000
 0010111100000000 | 0001001000000000 | 1101000011111111 | 0000001000000000 | 0011111100000000 | 0011110100000000
 0011111100000000 | 0001001100000000 | 1100000011111111 | 0001001100000000 | 0011111100000000 | 0010110000000000
 1000111100000000 | 0000010000000000 | 0111000011111111 | 0000010000000000 | 1000111100000000 | 1000101100000000
 0000000000001111 | 0000000000010000 | 1111111111110000 | 0000000000000000 | 0000000000011111 | 0000000000011111
 0000000100100011 | 1111111111111111 | 1111111011011100 | 0000000100100011 | 1111111111111111 | 1111111011011100
 0010010001101000 | 0010010001101000 | 1101101110010111 | 0010010001101000 | 0010010001101000 | 0000000000000000
 1111101001010000 | 0000010110101111 | 0000010110101111 | 0000000000000000 | 1111111111111111 | 1111111111111111
 0001001000110100 | 1111111111110101 | 1110110111001011 | 0001001000110100 | 1111111111110101 | 1110110111000001
(10 rows)

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 bit_table;
        a         |        b         | a<b | a<=b | a=b | a>=b | a>b | a<>b 
------------------+------------------+-----+------+-----+------+-----+------
 0000111100000000 | 0001000000000000 | t   | t    | f   | f    | f   | t
 0001111100000000 | 0001000100000000 | f   | f    | f   | t    | t   | t
 0010111100000000 | 0001001000000000 | f   | f    | f   | t    | t   | t
 0011111100000000 | 0001001100000000 | f   | f    | f   | t    | t   | t
 1000111100000000 | 0000010000000000 | f   | f    | f   | t    | t   | t
 0000000000001111 | 0000000000010000 | t   | t    | f   | f    | f   | t
 0000000100100011 | 1111111111111111 | t   | t    | f   | f    | f   | t
 0010010001101000 | 0010010001101000 | f   | t    | t   | t    | f   | f
 1111101001010000 | 0000010110101111 | f   | f    | f   | t    | t   | t
 0001001000110100 | 1111111111110101 | t   | t    | f   | f    | f   | t
(10 rows)

SELECT a,a<<4 AS "a<<4",b,b>>2 AS "b>>2" FROM bit_table;
        a         |       a<<4       |        b         |       b>>2       
------------------+------------------+------------------+------------------
 0000111100000000 | 1111000000000000 | 0001000000000000 | 0000010000000000
 0001111100000000 | 1111000000000000 | 0001000100000000 | 0000010001000000
 0010111100000000 | 1111000000000000 | 0001001000000000 | 0000010010000000
 0011111100000000 | 1111000000000000 | 0001001100000000 | 0000010011000000
 1000111100000000 | 1111000000000000 | 0000010000000000 | 0000000100000000
 0000000000001111 | 0000000011110000 | 0000000000010000 | 0000000000000100
 0000000100100011 | 0001001000110000 | 1111111111111111 | 0011111111111111
 0010010001101000 | 0100011010000000 | 0010010001101000 | 0000100100011010
 1111101001010000 | 1010010100000000 | 0000010110101111 | 0000000101101011
 0001001000110100 | 0010001101000000 | 1111111111110101 | 0011111111111101
(10 rows)

DROP TABLE bit_table;
-- The following should fail
select B'001' & B'10';
ERROR:  cannot AND bit strings of different sizes
select B'0111' | B'011';
ERROR:  cannot OR bit strings of different sizes
select B'0010' # B'011101';
ERROR:  cannot XOR bit strings of different sizes
-- More position tests, checking all the boundary cases
SELECT POSITION(B'1010' IN B'0000101');   -- 0
 position 
----------
        0
(1 row)

SELECT POSITION(B'1010' IN B'00001010');  -- 5
 position 
----------
        5
(1 row)

SELECT POSITION(B'1010' IN B'00000101');  -- 0
 position 
----------
        0
(1 row)

SELECT POSITION(B'1010' IN B'000001010');  -- 6
 position 
----------
        6
(1 row)

SELECT POSITION(B'' IN B'00001010');  -- 1
 position 
----------
        1
(1 row)

SELECT POSITION(B'0' IN B'');  -- 0
 position 
----------
        0
(1 row)