《卸甲笔记》-PostgreSQL和Oracle的数据类型的对比系列二:数字类型

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
上一篇:《卸甲笔记》-PostgreSQL和Oracle的数据类型的对比系列四:大数据类型


下一篇:[Android]Android端ORM框架——RapidORM(v2.1)