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的数据类型的对比系列二：数字类型

数字类型

1、精确类型(整数和任意精度数值)

1.1、当小数位数(s)=0时

1.1.1、当p <= 4时，使用integer或者smallint

1.1.2、当4 < p <= 9时，应该使用integer

1.1.3、当9 < p <= 18时，使用bigint

1.1.4、当p > 18时，使用numeric(p, 0)

1.2、当小数位数(s) > 0时

1.2.1、当0 < s <=p的时候，使用numeric(p, s)

1.2.2、当 p < s的时候，使用numeric(s, s)

1.3、当小数位数(s) < 0时

1.3.1、当p + |s| <= 4时，使用integer或者smallint

1.3.2、当4 < p+ |s| <= 9时，使用Integer

1.3.3、当9 < p+ |s| <= 18时，使用bigint

1.3.4、当p+ |s| >18时，使用numeric(p + |s|, 0)

2、非精确类型(浮点数类型)

2.1、Oracle float(p)

2.2、Binary_float类型

2.3、Binary_double类型

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

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

数字类型

1、精确类型(整数和任意精度数值)

1.1、当小数位数(s)=0时

1.1.1、当p <= 4时，使用integer或者smallint

1.1.2、当4 < p <= 9时，应该使用integer

1.1.3、当9 < p <= 18时，使用bigint

1.1.4、当p > 18时，使用numeric(p, 0)

1.2、当小数位数(s) > 0时

1.2.1、当0 < s <=p的时候，使用numeric(p, s)

1.2.2、当 p < s的时候，使用numeric(s, s)

1.3、当小数位数(s) < 0时

1.3.1、当p + |s| <= 4时，使用integer或者smallint

1.3.2、当4 < p+ |s| <= 9时，使用Integer

1.3.3、当9 < p+ |s| <= 18时，使用bigint

1.3.4、当p+ |s| >18时，使用numeric(p + |s|, 0)

2、非精确类型(浮点数类型)

2.1、Oracle float(p)

2.2、Binary_float类型

2.3、Binary_double类型

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像