PostgreSQL是世界上功能最强大的开源数据库,在国内得到了越来越多机构和开发者的青睐和应用。随着PostgreSQL的应用越来越广泛,Oracle向PostgreSQL数据库的数据迁移需求也越来越多。数据库之间数据迁移的时候,首先遇到的,并且也是最重要的,就是数据类型之间的转换。下面根据自己的理解和测试,写了一些数据类型之间的差异以及迁移时的注意事项的文章,不足之处,尚请多多指教。
数字类型
Oracle内建的数字类型有四种,分别是number,float,binary_float,binary_double。由于和其余数据库的数据兼容,而产生了smallint,int,Integer,Decimal等多种数字类型,其实内部都是使用内建的四种实现的。因此我们只讨论内建的四种数据类型。
PostgreSQL的数字类型有三类。分别是 整数类型,包括smallint,integer和bigint。任意精度类型,包括numeric和decimal两种(其中decimal等同于numeric),以及浮点数类型,包括real和double。它们之间的区别和迁移时应该注意什么呢?下面按照精确类型(整数类型和任意精度类型)和非精确类型(浮点数类型)两类来进行讨论。
1、精确类型(整数和任意精度数值)
Oracle的number(p, s) 类型,根据精度(p)和小数位数(s)的不同,分别对应PostgreSQL中的多种情况。
1.1、当小数位数(s)=0时
当小数位数(s)=0时,可以对应PostgreSQL的smallint, integer, bigint, numeric(p)等多种数据类型。由于smallint,Integer,bigint的算术运算效率比numberic高的多,所以迁移时建议如下处理:
1.1.1、当p <= 4时,使用integer或者smallint
当p <= 4时,可以使用smallint和Integer,推荐使用Integer,因为它在取值范围、存储空间、性能之间最为平衡。只有磁盘空间紧张的情况下,才建议使用smallint。
Oracle number(p, 0) p <=4
SQL> create table o_test1( value number(4,0));
表已创建。
SQL> insert into o_test1 values(99999);
insert into o_test1 values(99999)
*
第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度
SQL> insert into o_test1 values(9999);
已创建 1 行。
PostgreSQL smallint
postgres=# create table p_test1(value smallint);
CREATE TABLE
postgres=# insert into p_test1 values(99999);
错误: smallint 超出范围
postgres=# insert into p_test1 values(9999);
INSERT 0 1
1.1.2、当4 < p <= 9时,应该使用integer
Oracle number(p, 0) 4 < p <= 9
SQL> create table o_test1(value number(9,0));
表已创建。
SQL> insert into o_test1 values(9999999999);
insert into o_test1 values(9999999999)
*
第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度
SQL> insert into o_test1 values(999999999);
已创建 1 行。
PostgreSQL integer
postgres=# create table p_test1(value integer);
CREATE TABLE
postgres=# insert into p_test1 values(9999999999);
错误: 整数超出范围
postgres=# insert into p_test1 values(999999999);
INSERT 0 1
1.1.3、当9 < p <= 18时,使用bigint
当9 < p <= 18时,这个时候integer的精度已经不够了,应该使用bigint
Oracle number(p, 0) 9 < p <= 18
SQL> create table o_test1(value number(18,0));
表已创建。
SQL> insert into o_test1 values(9999999999999999999);
insert into o_test1 values(9999999999999999999)
*
第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度
SQL> insert into o_test1 values(999999999999999999);
已创建 1 行。
PostgreSQL bigint
postgres=# create table p_test1 (value bigint);
CREATE TABLE
postgres=# insert into p_test1 values(9999999999999999999);
错误: bigint 超出范围
postgres=# insert into p_test1 values(999999999999999999);
INSERT 0 1
1.1.4、当p > 18时,使用numeric(p, 0)
当p>18的时候,需要使用numeric(p, 0)。才能保证数据转换的精度。
Oracle number(p, 0) p > 18
SQL> create table o_test1(value number(19,0));
表已创建。
SQL> insert into o_test1 values(9999999999999999999);
已创建 1 行。
PostgreSQL numeric(19, 0)
postgres=# create table p_test1 (value numeric(19, 0));
CREATE TABLE
postgres=# insert into p_test1 values(9999999999999999999);
INSERT 0 1
1.2、当小数位数(s) > 0时
当小数位数(s) > 0时,由于有小数,只能够使用numeric(p, s)。
1.2.1、当0 < s <=p的时候,使用numeric(p, s)
Oracle number(p, s) 0 < s <= p
SQL> create table o_test1(value number(5,3));
表已创建。
SQL> insert into o_test1 values(123.567);
insert into o_test1 values(123.567)
*
第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度
SQL> insert into o_test1 values(12.56789);
已创建 1 行。
SQL> select * from o_test1;
VALUE
----------
12.568
PostgreSQL numeric(p, s) p >= s
postgres=# create table p_test1( value numeric(5, 3));
CREATE TABLE
postgres=# insert into p_test1 values(123.456);
错误: 数字字段溢出
描述: 精度为5,范围是3的字段必须四舍五入到小于10^2的绝对值.
postgres=# insert into p_test1 values(12.456789);
INSERT 0 1
postgres=# select * from p_test1;
value
--------
12.457
(1 行记录)
1.2.2、当 p < s的时候,使用numeric(s, s)
当 p < s的时候,使用numeric(s, s)。此时,Oracle可以控制小数点后0的个数,而PostgreSQL不可。由于数据迁移,只需要把数据按照原来的样式迁移过来即可,所以不存在问题。
Oracle number(p, s) s > p
SQL> create table o_test1(value number(3,5));
表已创建。
SQL> insert into o_test1 values(0.056789);
insert into o_test1 values(0.056789)
*
第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度
SQL> insert into o_test1 values(0.0056789);
已创建 1 行。
SQL> select * from o_test1;
VALUE
----------
.00568
PostgreSQL numeric(p, s) s > p
postgres=# create table p_test1( value numeric(5, 5));
CREATE TABLE
postgres=# insert into p_test1 values(0.056789);
INSERT 0 1
postgres=# insert into p_test1 values(0.0056789);
INSERT 0 1
postgres=# select * from p_test1;
value
---------
0.05679
0.00568
(2 行记录)
1.3、当小数位数(s) < 0时
当小数位数(s) < 0的时候,由于此时p代表精度的位数,s位决定了在何处进行位数的舍入。s < 0的时候,这种根据s来进行数据舍入的功能PostgreSQL尚不具备,但由于数据迁移时只要能将Oracle的数据完全转换过来就行了。所以对于数据迁移来说也不存在问题。
PostgreSQL中,小数位数必须大于等于0。此时的迁移,需要把p + |s| 作为数据的精度来判断。其余和s=0的时候相同。
1.3.1、当p + |s| <= 4时,使用integer或者smallint
当p+ |s| <= 4时,可以使用smallint和Integer,推荐使用Integer,只有磁盘空间紧张的情况下,才建议使用smallInt。
Oracle number(p, s) p+ |s| <= 4
SQL> create table o_test1( value number(2,-2));
表已创建。
SQL> insert into o_test1 values(9950);
insert into o_test1 values(9950)
*
第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度
SQL> insert into o_test1 values(9940);
已创建 1 行。
SQL> select * from o_test1;
VALUE
----------
9900
PostgreSQL smallint
postgres=# create table p_test1( value smallint);
CREATE TABLE
postgres=# insert into p_test1 values(9950);
INSERT 0 1
postgres=# insert into p_test1 values(9940);
INSERT 0 1
postgres=# select * from p_test1;
value
-------
9950
9940
(2 行记录)
1.3.2、当4 < p+ |s| <= 9时,使用Integer
当4 < p+ |s| <= 9时,应该使用Integer。
Oracle number(p, s) 4 < p+ |s| <= 9
SQL> create table o_test1( value number(6,-3));
表已创建。
SQL> insert into o_test1 values(999999500);
insert into o_test1 values(999999500)
*
第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度
SQL> insert into o_test1 values(999999400);
已创建 1 行。
SQL> select * from o_test1;
VALUE
----------
999999000
PostgreSQL integer
postgres=# create table p_test1( value integer);
CREATE TABLE
postgres=# insert into p_test1 values(999999500);
INSERT 0 1
postgres=# insert into p_test1 values(999999400);
INSERT 0 1
postgres=# select * from p_test1;
value
-----------
999999500
999999400
(2 行记录)
1.3.3、当9 < p+ |s| <= 18时,使用bigint
当9 < p+ |s| <= 18时,这个时候Integer的精度已经不够了,可以使用bigint。
Oracle 9 < p+ |s| <= 18
SQL> create table o_test1( value number(12,-6));
表已创建。
SQL> insert into o_test1 values(999999999999500000);
insert into o_test1 values(999999999999500000)
*
第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度
SQL> insert into o_test1 values(999999999999400000);
已创建 1 行。
PostgreSQL bigint
postgres=# create table p_test1( value bigint);
CREATE TABLE
postgres=# insert into p_test1 values(999999999999500000);
INSERT 0 1
postgres=# insert into p_test1 values(999999999999400000);
INSERT 0 1
postgres=# select * from p_test1;
value
--------------------
999999999999500000
999999999999400000
(2 行记录)
1.3.4、当p+ |s| >18时,使用numeric(p + |s|, 0)
当p+ |s| >18的时候,只能使用numeric(p + |s|, 0)。
Oracle p+ |s| > 18
SQL> create table o_test1( value number(15,-6));
表已创建。
SQL> insert into o_test1 values(999999999999999500000);
insert into o_test1 values(999999999999999500000)
*
第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度
SQL> insert into o_test1 values(999999999999999400000);
已创建 1 行。
SQL> select * from o_test1;
VALUE
------------------------------
999999999999999000000
PostgreSQL numeric(p + |s|)
postgres=# create table p_test1( value numeric(21));
CREATE TABLE
postgres=# insert into p_test1 values(999999999999999500000);
INSERT 0 1
postgres=# insert into p_test1 values(999999999999999400000);
INSERT 0 1
postgres=# select * from p_test1;
value
-----------------------
999999999999999500000
999999999999999400000
(2 行记录)
2、非精确类型(浮点数类型)
浮点数是不精确的、变精度的数字类型。由于有下层处理器、操作系统和编译器对它的支持,所以很多情况下处理速度会快的多。但是由于只是以近似值存储的,对于想得到精确值的情况,不可以使用。
Oracle的float类型包括float[(p)], Binary_float, Binary_double三种类型。基本上和PostgreSQL的real和double precision相对应。PostgreSQL也提供了float(p)类型,但是和real和double precision是基本相同的,所以此处只讨论real和double precision两种。
2.1、Oracle float(p)
Oracle的float类型,根据值的范围区间,可以简单认为在1E-37 ~1E+37之间时,和PostgreSQL的real对应。超出这个区间的时候,则和PostgreSQL的double precision对应。但是精度方面,PostgreSQL尚达不到Oracle的float能达到的精度。
Oracle的 float的精度p是用二进制的1-126表示,若转化为10进制,则需要乘以0.30103。所以,大致相当于1-126*0.30103 ~ 1- 38。而PostgreSQL的real精度是6位,而double precision精度大概是在15位,可以通过修改pg_settings的extra_float_digits参数的值增加-15~3位的精度。但还支持不到更大的位数。但在实际应用中,不会有人把浮点数放到那么大的精度。PostgreSQL默认的精度已经足够。
Oracle float(p)
SQL> create table o_test1( value float(126));
表已创建。
SQL> insert into o_test1 values(12345678901234567890123456789012345678901234567890);
已创建 1 行。
SQL> select * from o_test1;
VALUE
--------------------------------------------------
12345678901234567890123456789012345679000000000000
PostgreSQL double precision
postgres=# create table p_test1(value double precision);
CREATE TABLE
postgres=# insert into p_test1 values(12345678901234567890123456789012345678901234567890);
INSERT 0 1
postgres=# select * from p_test1;
value
-----------------------
1.23456789012346e+049
(1 行记录)
2.2、Binary_float类型
Binary_float类型,对应于PostgreSQL的real类型。
Binary_float的值在1.17549E-38F 至3.40282E+38F之间,对应于PostgreSQL的real类型。real类型的精度至少是6位小数。可以通过修改pg_settings的extra_float_digits参数的值,调整real的精度和Binary_float相符合。
Oracle Binary_Float
SQL> create table o_test1(col1 BINARY_float);
表已创建。
SQL> insert into o_test1 values (123456789012345678901234567890123456789);
已创建 1 行。
SQL> insert into o_test1 values (1234567890123456789012345678901234567890);
已创建 1 行。
SQL> select * from o_test1;
COL1
--------------------
1.23456786E+038
Inf
PostgreSQL real
postgres=# create table p_test1(value real);
CREATE TABLE
postgres=# insert into p_test1 values(123456789012345678901234567890123456789);
INSERT 0 1
postgres=# insert into p_test1 values(1234567890123456789012345678901234567890);
错误: 值超出范围: 上溢
postgres=# select * from p_test1;
value
--------------
1.23457e+038
(1 行记录)
2.3、Binary_double类型
Binary_double类型,对应于PostgreSQL的double precision类型。
Binary_double的值在2.22507485850720E-308 至 1.79769313486231E+308之间。对应的PostgreSQL 的double precision的范围通常是1E-307到1E+308, 精度至少15位数字。
Oracle Binary_double
SQL> create table o_test1(col1 Binary_double);
表已创建。
SQL> insert into o_test1 values (12345678901234567890123456789012345678901234567
890);
已创建 1 行。
SQL> select * from o_test1;
COL1
--------------------
1.2345678901235E+049
PostgreSQL double precision
postgres=# create table p_test1(value double precision);
CREATE TABLE
postgres=# insert into p_test1 values(12345678901234567890123456789012345678901234567890);
INSERT 0 1
postgres=# select * from p_test1;
value
-----------------------
1.23456789012346e+049
(1 行记录)
Oracle的数字类型向PostgreSQL的数据迁移过程中,只要根据Oracle的数据的精度,在PostgreSQL中选择相同或者更大精度的类型。数据就能够迁移过来。但为了转换过来后数据库的效率(特别是整数的时候),需要选择合适的数据类型,才能够完整、正确并且高效的完成Oracle数字类型向PostgreSQL数字类型的迁移。
参考文档:
PostgreSQL 9.4.4 中文手册:数值类型
http://www.postgres.cn/docs/9.4/datatype-numeric.html
Database SQL Language Reference:Data Types
http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements001.htm#i45441