By Digoal
This short article explains how to make PostgreSQL compatible with MySQL Tinyint, Unsigned Int, Zerofill, and more.
The numeric types of PostgreSQL and MySQL are described on the links below:
The differences between fixed integers are listed below:
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?
PG uses domain
to create a new type and implements integers of 1 and 3 bytes and unsigned integers.
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))
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)
Alibaba Cloud MaxCompute - January 22, 2024
ApsaraDB - October 22, 2020
digoal - May 16, 2019
ApsaraDB - February 22, 2022
digoal - February 5, 2020
Morningking - September 26, 2023
ApsaraDB: Faster, Stronger, More Secure
Learn MoreAlibaba Cloud PolarDB for PostgreSQL is an in-house relational database service 100% compatible with PostgreSQL and highly compatible with the Oracle syntax.
Learn MoreAlibaba Cloud PolarDB for MySQL is a cloud-native relational database service 100% compatible with MySQL.
Learn MoreAn online MPP warehousing service based on the Greenplum Database open source program
Learn MoreMore Posts by digoal