×
Community Blog Making PostgreSQL Compatible with MySQL Tinyint, Unsigned Int, Zerofill – Domain and Lpad

Making PostgreSQL Compatible with MySQL Tinyint, Unsigned Int, Zerofill – Domain and Lpad

This short article explains how to make PostgreSQL compatible with MySQL Tinyint, Unsigned Int, Zerofill, and more.

By Digoal

This short article explains how to make PostgreSQL compatible with MySQL Tinyint, Unsigned Int, Zerofill, and more.

Background

The numeric types of PostgreSQL and MySQL are described on the links below:

The differences between fixed integers are listed below:

  • MySQL supports 1, 2, 3, 4, 8 bytes of int, zerofill, signed, and unsigned integers.
  • PG supports 2, 4, 8 bytes of int and signed integers.

Regardless of the amount of capacity consumed in the database, what should you do in a situation that requires the user to use an integer of 1 or 3 bytes or an unsigned integer?

PostgreSQL Domain

PG uses domain to create a new type and implements integers of 1 and 3 bytes and unsigned integers.

  1. For example, create the uint8 type, which is the 8-byte unsigned integer:
db1=# create domain uint8 as numeric(20,0) check (value <= ((2^64::numeric)::numeric(20,0)-1) and value>=0::numeric(20,0));  
CREATE DOMAIN  

Use domain to restrict the integer that should be greater than or equal to 0 and within the range of 2^64:

db1=# create table t5(c1 uint8);  
CREATE TABLE  
  
db1=# insert into t5 values (-1);  
ERROR:  value for domain uint8 violates check constraint "uint8_check"  
  
db1=# insert into t5 values (0);  
INSERT 0 1  
  
db1=# insert into t5 values (2^64::numeric);  
ERROR:  value for domain uint8 violates check constraint "uint8_check"  
  
db1=# insert into t5 values (2^64::numeric-1);  
INSERT 0 1  
db1=# select 2^64::numeric-1;  
               ?column?                  
---------------------------------------  
 18446744073709551615.0000000000000000  
(1 row)  
  
db1=# insert into t5 values (18446744073709551615);  
INSERT 0 1  
  
db1=# insert into t5 values (18446744073709551616);  
ERROR:  value for domain uint8 violates check constraint "uint8_check"  
  
  
db1=# select * from t5;  
          c1            
----------------------  
                    0  
 18446744073709551615  
 18446744073709551615  
(3 rows)  

All domains: 1 and 3 bytes are unsigned integers, and 2, 4, and 8 bytes are unsigned.

create domain int1 as int2 CHECK (VALUE <= 127 AND VALUE >= (-128));  
create domain uint1 as int2 CHECK (VALUE <= 255 AND VALUE >= 0);  
create domain uint2 as int4 CHECK (VALUE <= 65535 AND VALUE >= 0);  
create domain int3 as int4 CHECK (VALUE <= 8388607 AND VALUE >= (-8388608));  
create domain uint3 as int4 CHECK (VALUE <= 16777215 AND VALUE >= 0);  
create domain uint4 as int8 CHECK (VALUE <= 4294967295 AND VALUE >= 0);  
create domain uint8 as numeric(20,0) check (value <= ((2^64::numeric)::numeric(20,0)-1) and value>=0::numeric(20,0));  

db1=# \dD  
                                                                               List of domains  
 Schema |  Name   |     Type      | Collation | Nullable | Default |                                                  Check                                                    
--------+---------+---------------+-----------+----------+---------+---------------------------------------------------------------------------------------------------------  
 public | int1    | smallint      |           |          |         | CHECK (VALUE <= 127 AND VALUE >= '-128'::integer)  
 public | int3    | integer       |           |          |         | CHECK (VALUE <= 8388607 AND VALUE >= '-8388608'::integer)  
 public | uint1   | smallint      |           |          |         | CHECK (VALUE <= 255 AND VALUE >= 0)  
 public | uint2   | integer       |           |          |         | CHECK (VALUE <= 65535 AND VALUE >= 0)  
 public | uint3   | integer       |           |          |         | CHECK (VALUE <= 16777215 AND VALUE >= 0)  
 public | uint4   | bigint        |           |          |         | CHECK (VALUE <= '4294967295'::bigint AND VALUE >= 0)  
 public | uint8   | numeric(20,0) |           |          |         | CHECK (VALUE <= ((2::numeric ^ 64::numeric)::numeric(20,0) - 1::numeric) AND VALUE >= 0::numeric(20,0))  

Lpad Complements the Compatibility with Zerofill

If users need to fill 0 on the left for not reaching the length during output, they can use lpad to fill in the missing part when returning. The actual length is returned if it exceeds the length.

For example, when the length is less than 4, fill in 0 on the left:

db1=# select lpad(10::text,greatest(4,length(10::text)),'0');  
 lpad   
------  
 0010  
(1 row)  
  
db1=# select lpad(199::text,greatest(4,length(199::text)),'0');  
 lpad   
------  
 0199  
(1 row)  
  
db1=# create table t6(id int);  
CREATE TABLE  
db1=# insert into t6 values (123),(123456);  
  
db1=# select lpad(id::text, greatest(4, length(id::text)), '0'), id from t6;  
  lpad  |   id     
--------+--------  
 0123   |    123  
 123456 | 123456  
(2 rows)  

The lpad function is used below:

db1=# \df lpad  
                         List of functions  
   Schema   | Name | Result data type | Argument data types | Type   
------------+------+------------------+---------------------+------  
 pg_catalog | lpad | text             | text, integer       | func  
 pg_catalog | lpad | text             | text, integer, text | func  
(2 rows)  

References

0 0 0
Share on

digoal

210 posts | 13 followers

You may also like

Comments