PostgreSQL Oracle 兼容性之 - BIT

背景

比特类型转换为整型，整型转换为比特类型，以及整型的比特运算。

在数据分析时被经常使用，例如对多个用0和1表示的标签字段叠加，使用一个整型表示。

又或者将数字表述的标签信息转换为比特位，以获取分散的标签信息。

在Oracle中可以使用bit_to_num将多个0,1转换为number，使用bitand对两个number进行比特与运算得到另一个number。

bit_to_num(exp1, exp2, ...., expn)

BIN_TO_NUM converts a bit vector to its equivalent number. Each argument to this function represents a bit in the bit vector.

This function takes as arguments any numeric datatype, or any nonnumeric datatype that can be implicitly converted to NUMBER.

Each expr must evaluate to 0 or 1.

This function returns Oracle NUMBER.

例子

SELECT BIN_TO_NUM(1,0,1,0) FROM DUAL; BIN_TO_NUM(1,0,1,0) ------------------- 10

BITAND(exp1, exp2)

The BITAND function treats its inputs and its output as vectors of bits; the output is the bitwise AND of the inputs.

The types of expr1 and expr2 are NUMBER, and the result is of type NUMBER. If either argument to BITAND is NULL, the result is NULL.

The arguments must be in the range -(2(n-1)) .. ((2(n-1))-1). If an argument is out of this range, the result is undefined.

The result is computed in several steps. First, each argument A is replaced with the value SIGN(A)*FLOOR(ABS(A)).

This conversion has the effect of truncating each argument towards zero. Next, each argument A (which must now be an integer value) is converted to an n-bit two's complement binary integer value.

The two bit values are combined using a bitwise AND operation. Finally, the resulting n-bit two's complement value is converted back to NUMBER.

例子

SELECT BITAND(6,3) FROM DUAL; BITAND(6,3) ----------- 2 SELECT BITAND( BIN_TO_NUM(1,1,0), BIN_TO_NUM(0,1,1)) "Binary" FROM DUAL; Binary ---------- 2

PostgreSQL BIT_TO_NUM

PostgreSQL的bit转换为整型有隐式的转换，与显示的转换。

固定长度的bit到整型是可以使用隐式转换的，而varbit到整型则需要显示转换。

隐式转换例子

postgres=# select '1010'::bit(4)::int; int4 ------ 10 (1 row) postgres=# select '1010'::varbit::int; ERROR: cannot cast type bit varying to integer LINE 1: select '1010'::varbit::int; ^

显示转换例子

postgres=# select int4(bit(4) '1010'); int4 ------ 10 (1 row) postgres=# select int4(varbit(4) '1010'); int4 ------ 10 (1 row) postgres=# select int4(varbit '1010'); int4 ------ 10 (1 row)

自定义bit_to_num函数，注意它是动态输入变量

postgres=# create or replace function bit_to_num(VARIADIC arr int[]) returns numeric as $$ declare mid int2; res numeric; begin foreach mid in array arr loop if mid not in (0,1) then raise 'must 1 or 0'; end if; end loop; res := (int8(array_to_string(arr,'')::varbit))::numeric; return res; end; $$ language plpgsql; CREATE FUNCTION

测试

postgres=# select bit_to_num(1,0,1,0,1,0,2); ERROR: must 1 or 0 postgres=# select bit_to_num(1,0,1,0); bit_to_num ------------ 10 (1 row)

PostgreSQL 多种类型 bitnot bitand bitor bitxor bit 左右移动

PostgreSQL 有操作符直接支持整型、BIT类型以及MAC地址类型的比特操作，如下

List of operators Schema | Name | Left arg type | Right arg type | Result type | Function | Description ------------+------+-----------------------------+-----------------------------+-----------------------------+--------------------------+------------------------------------------------------------------ pg_catalog | # | bigint | bigint | bigint | int8xor | bitwise exclusive or pg_catalog | # | bit | bit | bit | bitxor | bitwise exclusive or pg_catalog | # | integer | integer | integer | int4xor | bitwise exclusive or pg_catalog | # | smallint | smallint | smallint | int2xor | bitwise exclusive or pg_catalog | & | bigint | bigint | bigint | int8and | bitwise and pg_catalog | & | bit | bit | bit | bitand | bitwise and pg_catalog | & | inet | inet | inet | inetand | bitwise and pg_catalog | & | integer | integer | integer | int4and | bitwise and pg_catalog | & | macaddr | macaddr | macaddr | macaddr_and | bitwise and pg_catalog | & | smallint | smallint | smallint | int2and | bitwise and pg_catalog | << | bigint | integer | bigint | int8shl | bitwise shift left pg_catalog | << | bit | integer | bit | bitshiftleft | bitwise shift left pg_catalog | << | integer | integer | integer | int4shl | bitwise shift left pg_catalog | << | smallint | integer | smallint | int2shl | bitwise shift left pg_catalog | >> | bigint | integer | bigint | int8shr | bitwise shift right pg_catalog | >> | bit | integer | bit | bitshiftright | bitwise shift right pg_catalog | >> | integer | integer | integer | int4shr | bitwise shift right pg_catalog | >> | smallint | integer | smallint | int2shr | bitwise shift right pg_catalog | | | bigint | bigint | bigint | int8or | bitwise or pg_catalog | | | bit | bit | bit | bitor | bitwise or pg_catalog | | | inet | inet | inet | inetor | bitwise or pg_catalog | | | integer | integer | integer | int4or | bitwise or pg_catalog | | | macaddr | macaddr | macaddr | macaddr_or | bitwise or pg_catalog | | | smallint | smallint | smallint | int2or | bitwise or pg_catalog | ~ | | bigint | bigint | int8not | bitwise not pg_catalog | ~ | | bit | bit | bitnot | bitwise not pg_catalog | ~ | | inet | inet | inetnot | bitwise not pg_catalog | ~ | | integer | integer | int4not | bitwise not pg_catalog | ~ | | macaddr | macaddr | macaddr_not | bitwise not pg_catalog | ~ | | smallint | smallint | int2not | bitwise not

例子

postgres=# select 6&3; ?column? ---------- 2 (1 row) postgres=# select 6|3; ?column? ---------- 7 (1 row) postgres=# select 6#3; ?column? ---------- 5 (1 row) postgres=# select ~6; ?column? ---------- -7 (1 row) postgres=# select 6<<1; ?column? ---------- 12 (1 row) postgres=# select 6>>1; ?column? ---------- 3 (1 row)

如果你要和ORACLE用法完全一致，可以自定义bitand函数，或者使用orafce插件来支持.

例子

postgres=# create or replace function bitand(numeric,numeric) returns numeric as $$ select ($1::int8 & $2::int8)::numeric ; $$ language sql strict; CREATE FUNCTION postgres=# select bitand(6,3); bitand -------- 2 (1 row)

PostgreSQL 如何将numeric转换为varbit

第一种方法如下, 涉及符号与类型宽度(假设int为signed int).

do language plpgsql $$ declare o_bit text; o_len int; i_num int; i_conv int; i_num_abs int; i_res int; i_mod int; begin o_len := 32; i_num := -10234; i_conv := 2; i_num_abs := abs(i_num); i_res := i_num_abs/i_conv; i_mod := mod(i_num_abs,i_conv); o_bit := i_mod::text; loop if i_res = 0 then exit; end if; i_mod := mod(i_res,i_conv); i_res := i_res/i_conv; o_bit := i_mod||o_bit; end loop; o_len := o_len - char_length(o_bit) - 1; o_bit := repeat('0', o_len)||o_bit; if i_num >=0 then o_bit := '0'||o_bit; else o_bit := '1'||o_bit; end if; raise notice '%', o_bit; end; $$;

创建函数如下 :

create or replace function i4tob(i_num int) returns varbit as $$ declare o_bit text; o_len int; i_conv int; i_num_abs int; i_res int; i_mod int; begin o_len := 32; i_conv := 2; i_num_abs := abs(i_num); i_res := i_num_abs/i_conv; i_mod := mod(i_num_abs,i_conv); o_bit := i_mod::text; loop if i_res = 0 then exit; end if; i_mod := mod(i_res,i_conv); i_res := i_res/i_conv; o_bit := i_mod||o_bit; end loop; o_len := o_len - char_length(o_bit) - 1; if i_num >=0 then null; else o_bit := repeat('0', o_len)||o_bit; o_bit := '1'||o_bit; end if; raise notice '%', o_bit; return o_bit::varbit; end; $$ language plpgsql;

测试 :

postgres=# select i4tob(-100); NOTICE: 10000000000000000000000001100100 i4tob ---------------------------------- 10000000000000000000000001100100 (1 row) postgres=# select i4tob(100); NOTICE: 1100100 i4tob --------- 1100100 (1 row) postgres=# select i4tob(256); NOTICE: 100000000 i4tob ----------- 100000000 (1 row) postgres=# select i4tob(-256); NOTICE: 10000000000000000000000100000000 i4tob ---------------------------------- 10000000000000000000000100000000 (1 row)

另一种方法是使用位移算法 :

do language plpgsql $$ declare o_bit text; o_len int; i_num int; i_conv int; i_num_abs int; i_pos int; begin o_len := 32; i_num := -1024; i_conv := 2; i_num_abs := abs(i_num); i_pos := trunc((dlog1(i_num_abs))/0.693147180559945); o_bit := mod(i_num_abs,i_conv)::text; if i_pos >= 1 then for i in 1..i_pos loop o_bit := mod(i_num_abs>>i, i_conv)||o_bit; end loop; end if; if i_num >=0 then null; else o_len := o_len - char_length(o_bit) - 1; o_bit := repeat('0', o_len)||o_bit; o_bit := '1'||o_bit; end if; raise notice '%', o_bit; end; $$;

改成函数 :

create or replace function si32tob(i_num int) returns varbit as $$ declare o_bit text; o_len int; i_conv int; i_num_abs int; i_pos int; begin o_len := 32; i_conv := 2; i_num_abs := abs(i_num); i_pos := trunc((dlog1(i_num_abs))/0.693147180559945); o_bit := mod(i_num_abs,i_conv)::text; if i_pos >= 1 then for i in 1..i_pos loop o_bit := mod(i_num_abs>>i, i_conv)||o_bit; end loop; end if; if i_num >=0 then null; else o_len := o_len - char_length(o_bit) - 1; o_bit := repeat('0', o_len)||o_bit; o_bit := '1'||o_bit; end if; raise notice '%', o_bit; return o_bit::varbit; end; $$ language plpgsql;

测试 :

digoal=# select si32tob(1); NOTICE: 1 si32tob --------- 1 (1 row) Time: 1.715 ms digoal=# select si32tob(2); NOTICE: 10 si32tob --------- 10 (1 row) Time: 1.049 ms digoal=# select si32tob(-2); NOTICE: 10000000000000000000000000000010 si32tob ---------------------------------- 10000000000000000000000000000010 (1 row) Time: 1.520 ms digoal=# select si32tob(234); NOTICE: 11101010 si32tob ---------- 11101010 (1 row) Time: 1.094 ms digoal=# select si32tob(-234); NOTICE: 10000000000000000000000011101010 si32tob ---------------------------------- 10000000000000000000000011101010 (1 row) Time: 1.124 ms

以上函数参考wikihow提供的算法编写.

其他转换可参考本文末尾的函数进行修改.

参考

1. http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions013.htm

2. http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions014.htm

3. http://www.wikihow.com/Convert-from-Decimal-to-Binary

4. http://psoug.org/snippet/Convert-between-Decimal-Binary-Octal-and-Hex_536.htm

5. src/backend/optimizer/path/costsize.c

00096 #define LOG2(x) (log(x) / 0.693147180559945)

6. oracle pl/sql 相关转换函数

SET serveroutput ON CREATE OR REPLACE PACKAGE dbms_numsystem AS FUNCTION bin2dec (binval IN CHAR ) RETURN NUMBER; FUNCTION dec2bin (N IN NUMBER) RETURN VARCHAR2; FUNCTION oct2dec (octval IN CHAR ) RETURN NUMBER; FUNCTION dec2oct (N IN NUMBER) RETURN VARCHAR2; FUNCTION hex2dec (hexval IN CHAR ) RETURN NUMBER; FUNCTION dec2hex (N IN NUMBER) RETURN VARCHAR2; END dbms_numsystem; / show errors CREATE OR REPLACE PACKAGE BODY dbms_numsystem AS FUNCTION bin2dec (binval IN CHAR) RETURN NUMBER IS i NUMBER; digits NUMBER; result NUMBER := 0; current_digit CHAR(1); current_digit_dec NUMBER; BEGIN digits := LENGTH(binval); FOR i IN 1..digits LOOP current_digit := SUBSTR(binval, i, 1); current_digit_dec := TO_NUMBER(current_digit); result := (result * 2) + current_digit_dec; END LOOP; RETURN result; END bin2dec; FUNCTION dec2bin (N IN NUMBER) RETURN VARCHAR2 IS binval VARCHAR2(64); N2 NUMBER := N; BEGIN WHILE ( N2 > 0 ) LOOP binval := MOD(N2, 2) || binval; N2 := TRUNC( N2 / 2 ); END LOOP; RETURN binval; END dec2bin; FUNCTION oct2dec (octval IN CHAR) RETURN NUMBER IS i NUMBER; digits NUMBER; result NUMBER := 0; current_digit CHAR(1); current_digit_dec NUMBER; BEGIN digits := LENGTH(octval); FOR i IN 1..digits LOOP current_digit := SUBSTR(octval, i, 1); current_digit_dec := TO_NUMBER(current_digit); result := (result * 8) + current_digit_dec; END LOOP; RETURN result; END oct2dec; FUNCTION dec2oct (N IN NUMBER) RETURN VARCHAR2 IS octval VARCHAR2(64); N2 NUMBER := N; BEGIN WHILE ( N2 > 0 ) LOOP octval := MOD(N2, 8) || octval; N2 := TRUNC( N2 / 8 ); END LOOP; RETURN octval; END dec2oct; FUNCTION hex2dec (hexval IN CHAR) RETURN NUMBER IS i NUMBER; digits NUMBER; result NUMBER := 0; current_digit CHAR(1); current_digit_dec NUMBER; BEGIN digits := LENGTH(hexval); FOR i IN 1..digits LOOP current_digit := SUBSTR(hexval, i, 1); IF current_digit IN ('A','B','C','D','E','F') THEN current_digit_dec := ASCII(current_digit) - ASCII('A') + 10; ELSE current_digit_dec := TO_NUMBER(current_digit); END IF; result := (result * 16) + current_digit_dec; END LOOP; RETURN result; END hex2dec; FUNCTION dec2hex (N IN NUMBER) RETURN VARCHAR2 IS hexval VARCHAR2(64); N2 NUMBER := N; digit NUMBER; hexdigit CHAR; BEGIN WHILE ( N2 > 0 ) LOOP digit := MOD(N2, 16); IF digit > 9 THEN hexdigit := CHR(ASCII('A') + digit - 10); ELSE hexdigit := TO_CHAR(digit); END IF; hexval := hexdigit || hexval; N2 := TRUNC( N2 / 16 ); END LOOP; RETURN hexval; END dec2hex; END dbms_numsystem; / show errors

Count