Postgresql 的 Bit String 資料型態

Bit String 是一般比較少見使用的資料型態.

來看相關運算子
select oprleft::regtype
     , oprname
     , oprright::regtype
     , oprresult::regtype
     , oprcode::regproc
  from pg_catalog.pg_operator
 where oprleft::regtype = 'bit'::regtype
    or oprright::regtype = 'bit'::regtype
    or oprresult::regtype = 'bit'::regtype
 order by oprleft, oprname, oprright, oprresult;

+---------+---------+----------+-----------+-------------------+
| oprleft | oprname | oprright | oprresult |      oprcode      |
+---------+---------+----------+-----------+-------------------+
| -       | ~       | bit      | bit       | bitnot            |
| bit     | #       | bit      | bit       | bitxor            |
| bit     | &       | bit      | bit       | pg_catalog.bitand |
| bit     | <       | bit      | boolean   | bitlt             |
| bit     | <<      | integer  | bit       | bitshiftleft      |
| bit     | <=      | bit      | boolean   | bitle             |
| bit     | <>      | bit      | boolean   | bitne             |
| bit     | =       | bit      | boolean   | biteq             |
| bit     | >       | bit      | boolean   | bitgt             |
| bit     | >=      | bit      | boolean   | bitge             |
| bit     | >>      | integer  | bit       | bitshiftright     |
| bit     | |       | bit      | bit       | bitor             |
+---------+---------+----------+-----------+-------------------+
(12 rows)

這樣就很方便查看有哪些運算子,看 oprcode 的字面意義,也很容易看出其功能.

來看一些簡單的例子

create table ithelp190929 (
  data1 bit(5)
, data2 bit varying (8) -- 變動長度
);

insert into ithelp190929(data1, data2) values
(b'10101', '10101'::varbit);

-- and
select data1
     , data1 & b'00001'
     , data2
     , data2 & b'00001'
  from ithelp190929;
+-------+----------+-------+----------+
| data1 | ?column? | data2 | ?column? |
+-------+----------+-------+----------+
| 10101 | 00001    | 10101 | 00001    |
+-------+----------+-------+----------+

-- or
select data1
     , data1 | b'01011'
     , data2
     , data2 | b'01011'
  from ithelp190929;

+-------+----------+-------+----------+
| data1 | ?column? | data2 | ?column? |
+-------+----------+-------+----------+
| 10101 | 11111    | 10101 | 11111    |
+-------+----------+-------+----------+

-- xor
select data1
     , data1 # b'11111'
     , data2
     , data2 # b'11111'
  from ithelp190929;
+-------+----------+-------+----------+
| data1 | ?column? | data2 | ?column? |
+-------+----------+-------+----------+
| 10101 | 01010    | 10101 | 01010    |
+-------+----------+-------+----------+

-- shift
select data1
     , data1 << 1
     , data2
     , data2 >> 1
  from ithelp190929;
+-------+----------+-------+----------+
| data1 | ?column? | data2 | ?column? |
+-------+----------+-------+----------+
| 10101 | 01010    | 10101 | 01010    |
+-------+----------+-------+----------+

這樣會截位...
還是要以位數較多,且最好使用非變動長度的,在實務上較優.

create table ithelp190929b (
  data1 bit(8)
);

insert into ithelp190929b values
(b'00010100');

利用轉換成整數,查看左移 右移運算的結果.
select data1
     , data1::integer
     , (data1 << 1)::integer
     , (data1 >> 1)::integer
  from ithelp190929b;

+----------+-------+------+------+
|  data1   | data1 | int4 | int4 |
+----------+-------+------+------+
| 00010100 |    20 |   40 |   10 |
+----------+-------+------+------+

<<1 相當於 *2, >> 相當於 /2

尋找第幾位有 1 的方法,先來觀察以下的查詢

select data1
     , data1 & b'00000001'
     , data1 & b'00000100'
  from ithelp190929b;

+----------+----------+----------+
|  data1   | ?column? | ?column? |
+----------+----------+----------+
| 00010100 | 00000000 | 00000100 |
+----------+----------+----------+

透過 & 僅對應位數為1, 就得非 0 的.

這樣就可以再透過 轉換為 integer 當過濾條件了.

insert into ithelp190929b values
(b'00001000');

select data1
  from ithelp190929b
 where (data1 & b'00000100')::integer > 0;
+----------+
|  data1   |
+----------+
| 00010100 |
+----------+

select data1
  from ithelp190929b
 where (data1 & b'00001000')::integer > 0;
+----------+
|  data1   |
+----------+
| 00001000 |
+----------+