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 |
+----------+