A Method to Make MySQL Bit(n) Compatible with PostgreSQL

By Digoal

This short article explains methods on how to make MySQL Bit(n) compatible with PostgreSQL by filling 1 when out of range and 0 when within range.

Background

The PostgreSQL (PG) bit type allows a maximum length of 83,886,080, and the MySQL bit allows a maximum length of 64. PG also supports the varbit type with up to 1 GB of supported storage, which covers a wider range.

However, MySQL is not compatible with PostgreSQL when processing bit, for example:

When the data (integer value) written by MySQL exceeds the maximum range of bit(n), the bit value is all 1. Otherwise, take the exact value, and fill 0 when not reaching the specific length.

Example:

create table t2(c1 bit(3));  
insert into t2 values (123);  
insert into t2 values (2);  
select * from t2;  
  c     
------  
 111  
 010  
(2 rows)

If exceeding the length, PG reports an error:

db1=# create table t3(c bit(4));  
  
db1=# insert into t3 values (b'11111');  
ERROR:  bit string length 5 does not match type bit(4)

Making PG Compatible with MySQL Bit(n)

When the written data (Integer value) Exceeds the maximum range of bit(N), take all 1 as bit value, otherwise, take exact value, and fill 0 when not reaching the length.

A function can be defined to achieve the same result:

db1=# create or replace function itob(int,int) returns varbit as $$  
  select   
  case   
  when $1>=(2^$2-1)   
  then substring((2^$2-1)::int8::bit(64)::text,64-$2+1)::varbit   
  else substring($1::bit(64)::text,64-$2+1)::varbit   
  end ;   
$$ language sql strict;  
CREATE FUNCTION

The first parameter is a numeric value, and the second parameter is a bit length. They are processed by a function logic: all bit values are taken as 1 if the written data exceeds the maximum value range of bit(n). Otherwise, the exact value is taken:

db1=# insert into t3 values (itob(111,4));  
INSERT 0 1  
db1=# insert into t3 values (itob(11,4));  
INSERT 0 1  
db1=# select * from t3;  
  c     
------  
 1111  
 1011  
(2 rows)  
  
db1=# select itob(12,4);  
 itob   
------  
 1100  
(1 row)  
  
db1=# select itob(8,4);  
 itob   
------  
 1000  
(1 row)  
  
db1=# select itob(123,4);  
 itob   
------  
 1111  
(1 row)

Convert bit(n) to int. Note: The length must be correct. Otherwise, 0 will be filled on the right, causing the incorrect conversion result:

db1=# select itob(12,4)::bit(4)::int;  -- 32位整型  
 itob   
------  
   12  
(1 row)  

-- 前面长度为4，后面bit(n)也必须是4
  
db1=# select itob(12,5)::bit(5)::int;  -- 32位整型  
 itob   
------  
   12  
(1 row)  
  
db1=# select itob(12,5)::bit(5)::int8;  -- 64位整型  
 itob   
------  
   12  
(1 row)

Other Examples:

In addition, it is possible to cast integral values to and from type bit. Some examples:

44::bit(10)                    0000101100  
44::bit(3)                     100  
cast(-44 as bit(12))           111111010100  
'1110'::bit(4)::integer        14

Note: Casting to just "bit" means casting to bit(1), and it will deliver only the least significant bit of the integer.

Other Implementations

PG supports new data types through which the logics can be put into the handlers of types for processing, such as mysqlbit(n).

https://www.postgresql.org/docs/current/xtypes.html

The following MySQL-compatible plug-ins are also supported:

https://pgxn.org/dist/mysqlcompat/

Community

A Method to Make MySQL Bit(n) Compatible with PostgreSQL

Background

Making PG Compatible with MySQL Bit(n)

Other Implementations

References

Read previous post:

Read next post:

digoal

You may also like

Comments

digoal

Related Products

Database Overview

PolarDB for PostgreSQL

ApsaraDB RDS for PostgreSQL

AnalyticDB for PostgreSQL