1、數值類型

整數：

mysql中的整數類型和pg相比，兩者有以下區別：

mysql：mysql中支持int 1,2,3,4,8 字節，同時支持有符號，無符號。并且mysql中支持在數值列中指定zerofill，用來將存儲的數值通過填充0的方式達到指定數據類型的長度(mysql8開始不建議使用ZEROFILL屬性，并且在將來的MySQL版本中將不再支持該屬性)。

pg：pg支持 int 2,4,8 字節，且數值都是有符號的。

mysql整數類型：

pg整數類型：

那么對于mysql中的1，3字節整型，或者無符號整型以及zerofill特性，在pg中該如何實現呢？

在pg中我們可以使用domain來實現mysql中的1，3字節整數以及無符號整型。

創建uint8，8字節無符號整型

bill=# create domain uint8 as numeric(20,0) check (value = ((2^64::numeric)::numeric(20,0)-1) and value>=0::numeric(20,0)); 
CREATE DOMAIN

使用domain，插入整型數據，且大于等于0，小于2^64

bill=# create table t5(c1 uint8); 
CREATE TABLE
bill=# insert into t5 values (-1);
ERROR: value for domain uint8 violates check constraint "uint8_check"
bill=# insert into t5 values (0); 
INSERT 0 1
bill=# insert into t5 values (18446744073709551615); 
INSERT 0 1
bill=# insert into t5 values (18446744073709551616); 
ERROR: value for domain uint8 violates check constraint "uint8_check"
bill=# select * from t5; 
  c1  
----------------------
   0
 18446744073709551615
(2 rows)

同樣我們也可以來創建domain實現1,3字節有無符號整型，2,4,8字節無符號等等：

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)); 

而對于mysql中的zerofill，我們可以使用lpad函數來實現，并且這也是mysql官方文檔中推薦的一種方式。

mysql中zerofill使用方式：

mysql> create table t1(id int1 zerofill); 
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(4);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------+
| id |
+------+
| 004 |
+------+
1 row in set (0.00 sec)

pg中使用lpad函數替代：

bill=# create table t1(id int); 
CREATE TABLE
bill=# insert into t1 values (123),(123456); 
INSERT 0 2
bill=# select lpad(id::text, greatest(4, length(id::text)), '0'), id from t1; 
 lpad | id 
--------+--------
 0123 | 123
 123456 | 123456
(2 rows)

numeric類型：

pg和mysql一樣都支持decimal，numeric類型來表示浮點數。兩者的區別在于：mysql中的numeric類型整數和小數部分均最大支持65digits。

而pg中numeric類型支持的最大范圍是：

[左131072,右16383]digits。

例如:

–mysql中

mysql> create table t1(id numeric(66,1));
ERROR 1426 (42000): Too-big precision 66 specified for 'id'. Maximum is 65.
mysql> create table t1(id numeric(65,1)); 
Query OK, 0 rows affected (0.01 sec)

–pg中

bill=# create table t4(id numeric(66,1)); 
CREATE TABLE

浮點類型：

mysql和pg中的浮點數類型基本一致。mysql中4 bytes的浮點數類型有real，float4，4 bytes的浮點數類型double。pg中對應的也有real，float，float4，float8以及double precision，兩者基本兼容。

bit類型：

mysql中bit類型一般都是使用整數類型表示，所以支持的bit位數最大只能是64位。而在pg中有專門的bit類型bit(范圍1～83886080)，以及可變長度的bit類型varbit。

序列：

mysql中創建表時可以使用auto_increment來創建自增列，從而生成一個和該列相關的序列，這個和pg中創建serial類型的列類似，但是兩者仍然有明顯區別：

mysql：使用auto_increment的自增列必須要建索引，不然會報錯。序列的默認初始值是1，步長為1.可以通過修改auto_increment_increment和auto_increment_offset來修改初始值和步長。

pg：pg中創建serial類型的列時會創建相應的序列，支持的數據類型有serial2，serial4，serial8。同時pg創建序列時可以直接指定初始值，步長，maxvalue，cache，circle等參數。其中序列cache預取多個值，可以保證沒有性能問題。circle可以指定序列達到最大值后從初始值開始重新計數。

–mysql

mysql> create table t4 (id int auto_increment primary key);
Query OK, 0 rows affected (0.06 sec)

–PostgreSQL

bill=# create table t4(id serial);
CREATE TABLE

2、時間類型

mysql：mysql中時間相關的類型有日期date、時間time以及datetime、timestamp和year類型。

pg：pg中的時間數據類型基本和mysql一致。區別在于pg中支持timez類型，即帶時區的時間類型，這個mysql中不支持，但是pg中不支持mysql中的year類型，不過我們仍然可以通過創建domain的方式來在pg中實現year類型。

mysql中的year類型表示年份從 1901年到2155。

pg中實現mysql中year類型的方式：

bill=# create domain year as int2 check(value >=1901 and value =2155); 
CREATE DOMAIN
bill=# create table ts4(c1 year); 
CREATE TABLE
bill=# insert into ts4 values (1000);
ERROR: value for domain year violates check constraint "year_check"
bill=# insert into ts4 values (2019);
INSERT 0 1
bill=# insert into ts4 values (2156);
ERROR: value for domain year violates check constraint "year_check"

3、字符串類型

char/varchar類型：

mysql和pg中都支持char類型來表示固定長度的字符串，varchar類型表示可變長度的字符串類型，兩者的區別在于：

mysql：char類型最大255字符，varchar類型最大不超過64字節。

pg：char類型最大10485760字符，varchar類型最大1G字節。同時pg中還支持兩種特殊的字符串類型：name類型，固定64字節長度，char類型(即不指定長度)，固定1字節長度。

binary/varbinary類型：

mysql中binary(n)最大255個字符，varbinary(n)最大不超過64k字節。使用字節流來存儲字符串。而pg中使用bytea類型來表示二進制類型，最大不超過1G字節。

blob/text類型：

mysql中的blob/text類型分別有以下幾種：

tinyblob、tinytext 2^8字節

blob、text 2^16字節

mediumblob、mediumtext 2^24字節

longblob、longtext 2^32字節

pg中對應的使用bytea類型和text類型，兩者最大長度均為1G字節。

enum類型：

mysql中的枚舉類型最大不超過64K個值，而pg中最大為1GB

set類型：

mysql中的集合set類型表示沒有重復值的集合，最大64個值，在pg中雖然沒有set類型，但是可以通過數組類型去代替，最大支持1GB大小。

4、其它類型

json類型：

mysql和pg中的json類型基本一致，區別在于默寫json函數可能稍有區別。不過pg中json類型有2種json和jsonb，不過一般都使用jsonb類型。

除了上面列舉的這些類型之外，pg中還支持很多mysql中不支持的數據類型。例如：pg中支持IP地址類型，這個在mysql中常常都是使用int或者varchar之類的數據類型代替。

除此之外還有很多pg內置的數據類型在mysql中是不支持的：貨幣、interval、平面幾何、全文檢索、uuid、xml、數組、復合類型、范圍類型、域類型等等。

同時pg還有很多外置的數據類型：樹類型、多維類型、化學分子、DNA、postgis等等類型。

補充：Oracle與PostgreSQL使用差異對比與總結

JDBC連接：

Oracle的jdbc連接字符串：db.url=jdbc:oracle:thin:@192.168.1.1:1521:ORCL

Postgresql的連接字符串：db.url=jdbc:postgresql:@192.168.1.1:5432/database

1、基本數據類型差異