3.1. 数据类型
本节主要介绍统一SQL Oracle2PostgreSQL 支持原生Oracle数据库中的SQL数据类型的对比信息。
序号 |
数据类型分类 |
Oracle 数据类型 |
统一SQL |
转换后 PostgreSQL 数据类型 |
|---|---|---|---|---|
1 |
数字 |
BINARY_FLOAT |
支持 |
real |
2 |
数字 |
BINARY_DOUBLE |
支持 |
double precision |
3 |
大对象 |
BFILE |
不支持 |
|
4 |
大对象 |
BLOB |
支持 |
bytea |
5 |
字符 |
CHAR(n),CHARACTER(n) |
支持 |
char(n),char(n) |
6 |
大对象 |
CLOB |
支持 |
text |
7 |
时间&日期 |
DATE |
支持 |
timestamp(0) |
8 |
数字 |
DECIMAL(p,s),DEC(p,s) |
支持 |
参见下文 |
9 |
数字 |
DOUBLE PRECISION |
支持 |
double precision |
10 |
数字 |
FLOAT(p) |
支持 |
double precision |
11 |
数字 |
INTEGER,INT |
支持 |
bigint,bigint |
12 |
时间&日期 |
INTERVAL YEAR(p) TO MONTH |
支持 |
interval year to month |
13 |
时间&日期 |
INTERVAL DAY(p) TO SECOND(s) |
支持 |
interval day to second(s) |
14 |
其他类型 |
LONG |
支持 |
text |
15 |
其他类型 |
LONG RAW |
支持 |
bytea |
16 |
字符 |
NCHAR(n) |
支持 |
char(n) |
17 |
字符 |
NCHAR VARYING(n) |
支持 |
varchar(n) |
18 |
大对象 |
NCLOB |
支持 |
text |
19 |
数字 |
NUMBER(p,s),NUMERIC(p,s) |
支持 |
参见下文 |
20 |
字符 |
NVARCHAR2(n) |
支持 |
varchar(n) |
21 |
其他类型 |
RAW(n) |
支持 |
bytea |
22 |
其他类型 |
REAL |
支持 |
double precision |
23 |
其他类型 |
ROWID |
支持 |
char(10) |
24 |
数字 |
SMALLINT |
支持 |
bigint |
25 |
时间&日期 |
TIMESTAMP(p) |
支持 |
timestamp(p) |
26 |
时间&日期 |
TIMESTAMP(p) WITH TIME ZONE |
支持 |
timestamp(p) with time zone |
27 |
其他类型 |
UROWID(n) |
支持 |
varchar(n) |
28 |
字符 |
VARCHAR(n) |
支持 |
varchar(n) |
29 |
字符 |
VARCHAR2(n) |
支持 |
varchar(n) |
30 |
XML类型 |
XMLTYPE |
支持 |
xml |
警告
- 在Oracle中，对于数据类型NUMBER，DECIMAL，DEC，NUMERIC的精度标度范围：
精度范围(p)：1~38
标度范围(s)：-84~127
- 统一SQL转换时支持的精度标度范围如下(且p>=s)：
精度范围(p)：1~38
标度范围(s)：0~38
在使用上述数据类型时，请确保数据类型精度标度范围在支持范围内。
对于目标库数据类型无法处理的数据长度，在运行时会报错。
在处理数字类型数据时，源库和目标库会存在截取或四舍五入的情况，可能会导致精度的损失(参考备注示例内容)
对于decimal/dec/numeric,decimal/dec/numeric(*),number/decimal/dec/numeric(,0),number/decimal/dec/numeric(,s)转换到目标库后默认精度(p=38)，对于超过38位的数据，在目标库执行将报错，此种情况下请谨慎使用。
在CREATE建表时，如果使用了GENERATED ALWAYS AS IDENTITY的特性，转换到PostgreSQL时都会被转换成bigint类型。
备注
1.在oracle中，对于以下类型语法，在处理数据时存在数字截取的情况,举例比较：
1.1. number/decimal/dec/numeric
1.2. number(*)/decimal(*)/dec(*)/numeric(*)
1.3. number(*,0)/decimal(*,0)/dec(*,0)/numeric(*,0)
-- Oracle SQL
DROP TABLE unisql_number_t;
CREATE TABLE unisql_number_t(id int,c NUMBER,cx NUMBER(*));
-- 插入数据
INSERT INTO unisql_number_t(id,c) values(0,999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
INSERT INTO unisql_number_t(id,c) values(1,999999999999999999999999999999999999999910000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
INSERT INTO unisql_number_t(id,c) values(2,999999999999999999999999999999999999999920000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
INSERT INTO unisql_number_t(id,c) values(3,999999999999999999999999999999999999999930000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
INSERT INTO unisql_number_t(id,c) values(4,999999999999999999999999999999999999999940000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
-- 执行报错，数字溢出
INSERT INTO unisql_number_t(id,c) values(5,999999999999999999999999999999999999999950000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
INSERT INTO unisql_number_t(id,cx) values(10,999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
INSERT INTO unisql_number_t(id,cx) values(11,999999999999999999999999999999999999999910000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
INSERT INTO unisql_number_t(id,cx) values(12,999999999999999999999999999999999999999920000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
INSERT INTO unisql_number_t(id,cx) values(13,999999999999999999999999999999999999999930000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
INSERT INTO unisql_number_t(id,cx) values(14,999999999999999999999999999999999999999940000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
-- 执行报错，数字溢出
INSERT INTO unisql_number_t(id,cx) values(15,999999999999999999999999999999999999999950000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
--查询数据
SELECT * FROM unisql_number_t;
ID|C |CX |
--+------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------+
0|999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000| |
1|999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000| |
2|999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000| |
3|999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000| |
4|999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000| |
10| |999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000|
11| |999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000|
12| |999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000|
13| |999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000|
14| |999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000|
-- PostgreSQL SQL
DROP TABLE unisql_number_t;
CREATE TABLE unisql_number_t(id int,c decimal,cx decimal);
SELECT * FROM unisql_number_t;
-- 插入数据
INSERT INTO unisql_number_t(id,c) values(0,999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
INSERT INTO unisql_number_t(id,c) values(1,999999999999999999999999999999999999999910000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
INSERT INTO unisql_number_t(id,c) values(2,999999999999999999999999999999999999999920000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
INSERT INTO unisql_number_t(id,c) values(3,999999999999999999999999999999999999999930000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
INSERT INTO unisql_number_t(id,c) values(4,999999999999999999999999999999999999999940000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
-- 执行不会报错
INSERT INTO unisql_number_t(id,c) values(5,999999999999999999999999999999999999999950000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
INSERT INTO unisql_number_t(id,cx) values(10,999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
INSERT INTO unisql_number_t(id,cx) values(11,999999999999999999999999999999999999999910000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
INSERT INTO unisql_number_t(id,cx) values(12,999999999999999999999999999999999999999920000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
INSERT INTO unisql_number_t(id,cx) values(13,999999999999999999999999999999999999999930000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
INSERT INTO unisql_number_t(id,cx) values(14,999999999999999999999999999999999999999940000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
-- 执行不会报错
INSERT INTO unisql_number_t(id,cx) values(15,999999999999999999999999999999999999999950000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
-- 查询数据
SELECT * FROM unisql_number_t;
id|c |cx |
--+------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------+
0|999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000| |
1|999999999999999999999999999999999999999910000000000000000000000000000000000000000000000000000000000000000000000000000000000000| |
2|999999999999999999999999999999999999999920000000000000000000000000000000000000000000000000000000000000000000000000000000000000| |
3|999999999999999999999999999999999999999930000000000000000000000000000000000000000000000000000000000000000000000000000000000000| |
4|999999999999999999999999999999999999999940000000000000000000000000000000000000000000000000000000000000000000000000000000000000| |
5|999999999999999999999999999999999999999950000000000000000000000000000000000000000000000000000000000000000000000000000000000000| |
10| |999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000|
11| |999999999999999999999999999999999999999910000000000000000000000000000000000000000000000000000000000000000000000000000000000000|
12| |999999999999999999999999999999999999999920000000000000000000000000000000000000000000000000000000000000000000000000000000000000|
13| |999999999999999999999999999999999999999930000000000000000000000000000000000000000000000000000000000000000000000000000000000000|
14| |999999999999999999999999999999999999999940000000000000000000000000000000000000000000000000000000000000000000000000000000000000|
15| |999999999999999999999999999999999999999950000000000000000000000000000000000000000000000000000000000000000000000000000000000000|
2. 插入数据时四舍五入的场景,举例说明：
-- Oracle SQL
DROP TABLE unisql_number_t;
CREATE TABLE unisql_number_t(c1 NUMBER(*,0),c2 NUMBER(2,0),c3 NUMBER(3,2));
INSERT INTO unisql_number_t(c1) values(1.11);
INSERT INTO unisql_number_t(c1) values(1.51);
INSERT INTO unisql_number_t(c2) values(1.11);
INSERT INTO unisql_number_t(c2) values(1.51);
INSERT INTO unisql_number_t(c3) values(1.111);
INSERT INTO unisql_number_t(c3) values(1.115);
SELECT * FROM unisql_number_t;
C1|C2|C3 |
--+--+----+
1| | |
2| | |
| 1| |
| 2| |
| |1.11|
| |1.12|
-- PostgreSQL SQL
DROP TABLE unisql_number_t;
CREATE TABLE unisql_number_t(c1 decimal(38,0),c2 decimal(2),c3 decimal(3,2));
INSERT INTO unisql_number_t(c1) values(1.11);
INSERT INTO unisql_number_t(c1) values(1.51);
INSERT INTO unisql_number_t(c2) values(1.11);
INSERT INTO unisql_number_t(c2) values(1.51);
INSERT INTO unisql_number_t(c3) values(1.111);
INSERT INTO unisql_number_t(c3) values(1.115);
SELECT * FROM unisql_number_t;
c1|c2|c3 |
--+--+----+
1| | |
2| | |
| 1| |
| 2| |
| |1.11|
| |1.12|
3.1.1. NUMBER
Oracle 数据类型 |
精度(p) |
标度(s) |
转换后 PostgreSQL 数据类型 |
示例 |
|---|---|---|---|---|
number |
decimal |
number —– decimal |
||
number(*) |
decimal |
number(*) —– decimal |
||
number(*,0) |
decimal(38,0) |
number(*,0)—-decimal(38,0) |
||
number(*,s) |
s > 0 |
decimal(38,s) |
number(*,2)—-decimal(38,2) |
|
number(p) / number(p,0) |
p>0 |
空 或 0 |
decimal(p) |
number(4,0)—–decimal(4) |
number(p,s) |
p>0 |
s>0 |
decimal(p,s) |
number(10,2)—–decimal(10,2) |
示例
-- 转换前Oracle SQL:
CREATE TABLE unisql_number_t(c1 NUMBER,c2 NUMBER(*),c3 NUMBER(*,0),c4 NUMBER(*,2),c5 NUMBER(4),c6 NUMBER(4,0),c7 NUMBER(10,2));
-- 转换后PostgreSQL SQL：
CREATE TABLE unisql_number_t (c1 decimal,c2 decimal,c3 decimal(38,0),c4 decimal(38,2),c5 decimal(4),c6 decimal(4),c7 decimal(10,2))
3.1.2. DECIMAL
Oracle 数据类型 |
精度(p) |
标度(s) |
转换后 PostgreSQL 数据类型 |
示例 |
|---|---|---|---|---|
decimal |
decimal(38,0) |
decimal —– decimal(38,0) |
||
decimal(*) |
decimal(38,0) |
decimal(*) —– decimal(38,0) |
||
decimal(*,0) |
decimal(38,0) |
decimal(*,0)—-decimal(38,0) |
||
decimal(*,s) |
s > 0 |
decimal(38,s) |
decimal(*,2)—-decimal(38,2) |
|
decimal(p) / decimal(p,0) |
p>0 |
空 或 0 |
decimal(p) |
decimal(4,0)—–decimal(4) |
decimal(p,s) |
p>0 |
s>0 |
decimal(p,s) |
decimal(10,2)—–decimal(10,2) |
示例
-- 转换前Oracle SQL:
CREATE TABLE unisql_decimal_t(c1 decimal,c2 decimal(*),c3 decimal(*,0),c4 decimal(*,2),c5 decimal(4),c6 decimal(4,0),c7 decimal(10,2));
-- 转换后PostgreSQL SQL：
CREATE TABLE unisql_decimal_t (c1 decimal(38,0),c2 decimal(38,0),c3 decimal(38,0),c4 decimal(38,2),c5 decimal(4),c6 decimal(4),c7 decimal(10,2))
3.1.3. DEC
Oracle 数据类型 |
精度(p) |
标度(s) |
转换后 PostgreSQL 数据类型 |
示例 |
|---|---|---|---|---|
dec |
dec(38,0) |
dec —– dec(38,0) |
||
dec(*) |
dec(38,0) |
dec(*) —– dec(38,0) |
||
dec(*,0) |
dec(38,0) |
dec(*,0)—-dec(38,0) |
||
dec(*,s) |
s > 0 |
dec(38,s) |
dec(*,2)—-dec(38,2) |
|
dec(p) / dec(p,0) |
p>0 |
空 或 0 |
dec(p) |
dec(4,0)—–dec(4) |
dec(p,s) |
p>0 |
s>0 |
dec(p,s) |
dec(10,2)—–dec(10,2) |
示例
-- 转换前Oracle SQL:
CREATE TABLE unisql_dec_t(c1 dec,c2 dec(*),c3 dec(*,0),c4 dec(*,2),c5 dec(4),c6 dec(4,0),c7 dec(10,2));
-- 转换后PostgreSQL SQL：
CREATE TABLE unisql_dec_t (c1 dec(38,0),c2 dec(38,0),c3 dec(38,0),c4 dec(38,2),c5 dec(4),c6 dec(4),c7 dec(10,2))
3.1.4. NUMERIC
Oracle 数据类型 |
精度(p) |
标度(s) |
转换后 PostgreSQL 数据类型 |
示例 |
|---|---|---|---|---|
numeric |
numeric(38,0) |
numeric —– numeric(38,0) |
||
numeric(*) |
numeric(38,0) |
numeric(*) —– numeric(38,0) |
||
numeric(*,0) |
numeric(38,0) |
numeric(*,0)—-numeric(38,0) |
||
numeric(*,s) |
s > 0 |
numeric(38,s) |
numeric(*,2)—-numeric(38,2) |
|
numeric(p) / numeric(p,0) |
p>0 |
空 或 0 |
numeric(p) |
numeric(4,0)—–numeric(4) |
numeric(p,s) |
p>0 |
s>0 |
numeric(p,s) |
numeric(10,2)—–numeric(10,2) |
示例
-- 转换前Oracle SQL:
CREATE TABLE unisql_numeric_t(c1 numeric,c2 numeric(*),c3 numeric(*,0),c4 numeric(*,2),c5 numeric(4),c6 numeric(4,0),c7 numeric(10,2));
-- 转换后PostgreSQL SQL：
CREATE TABLE unisql_numeric_t (c1 numeric(38,0),c2 numeric(38,0),c3 numeric(38,0),c4 numeric(38,2),c5 numeric(4),c6 numeric(4),c7 numeric(10,2))
3.1.5. CHAR(size [BYTE | CHAR])
参数说明
参数 |
说明 |
|---|---|
size |
表示指定的固定长度。 |
BYTE |
表示以字节为单位提供列的长度。 |
CHAR |
表示以字符为单位提供列的长度。 |
警告
因为目标库不支持BYTE、CHAR参数，当前BYTE、CHAR仅作为语法糖支持转换，非语义一致。
示例
-- 转换前Oracle SQL：
CREATE TABLE unisql_char_byte_test (col char(10 BYTE));
CREATE TABLE unisql_char_char_test (col char(10 CHAR));
-- 转换后PostgreSQL SQL：
CREATE TABLE unisql_char_byte_test (col char(10))
CREATE TABLE unisql_char_char_test (col char(10))
3.1.6. VARCHAR2(size [BYTE | CHAR])
参数说明
参数 |
说明 |
|---|---|
size |
表示存储的字节数或字符数的长度大小。 |
BYTE |
表示以字节为单位提供列的长度。 VARCHAR2 的默认类型为 BYTE。 |
CHAR |
表示以字符为单位提供列的长度。 |
警告
因为目标库不支持BYTE、CHAR参数，当前BYTE、CHAR仅作为语法糖支持转换，非语义一致。
示例
-- 转换前Oracle SQL：
CREATE TABLE unisql_varchar2_byte_test (col varchar2(10 BYTE));
CREATE TABLE unisql_varchar2_char_test (col varchar2(10 CHAR));
-- 转换后PostgreSQL SQL：
CREATE TABLE unisql_varchar2_byte_test (col varchar(10))
CREATE TABLE unisql_varchar2_char_test (col varchar(10))