Oracle类型number与PG类型numeric对比和转换策略

Oracle 11g

number 任意精度数字类型

http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT313

存储数据的范围

正数：1 x 10-130 to 9.99...9 x 10125 ,数据精度为38个有效数字

负数：-1 x 10-130 to 9.99...99 x 10125 ，数据精度为38个有效数字

零：0

无穷大：仅仅只可以从oracle 5中导入

描述

标度scale代表小数部分的数字位数，scale∈[-84,127]，s缺省为0；

精度precision 代表所有数字的位数，precision∈[1,38]，p缺省为最大限度内的任意值。

一般的精度可以大于等于标度，oracle中精度也可以小于标度，如numeri（3,6），该字段只能插入小于0.001的值，即0.0009,0.0008等等，小数点后尾数超出标度的位数将会四舍五入省去。

几个常见形式

Number 或者Number（*），（user_tab_column查询结果中data_precision =null，data_scale=null），此时可以存储极限内任意Precision和scale的值。

Number（*,scale），（user_tab_column查询结果中data_precision=null,data_scale=scale）此时可以存储极限内任意precision的值；

Number(precision)，此时scale缺省为0，等价于number(precision,0)；

Number(precision,scale)， precision∈[1,38]，scale∈[-84,127]。

例子

Input Data	Specified As	Stored As
7,456,123.89	NUMBER	7456123.89
7,456,123.89	NUMBER(*,1)	7456123.9
7,456,123.89	NUMBER(9)	7456124
7,456,123.89	NUMBER(9,2)	7456123.89
7,456,123.89	NUMBER(9,1)	7456123.9
7,456,123.89	NUMBER(6)	(not accepted, exceeds precision)
7,456,123.89	NUMBER(7,-2)	7456100

我的个人测试例子(oracle Release 10.2.0.1.0)如下，得出小结论：number最多能存储126位整数（超出126位后会溢出报错），能准确存储最高40位的整数（大于40切小于等于126的整数，超出40位的部分四舍五入后补为0），当有整数和小数时，整数部分最多能准确存储38位。

SQL> create table test0(id int,num number);

表已创建。

SQL> insert into test0 values(1,123456789123456789123456789123456789123456789123
456789123456789123456789123456789123456789123456789123456789123456789123456789);--126
已创建 1 行。
提交完成。

SQL> insert into test0 values(2,123456789123456789123456789123456789123456789123
4567891234567891234567891234567891234567891234567891234567891234567891234567891)
;--127
insert into test0 values(2,12345678912345678912345678912345678912345678912345678
91234567891234567891234567891234567891234567891234567891234567891234567891)
                           *
第 1 行出现错误:
ORA-01426: 数字溢出

SQL> insert into test0 values(3,1234567891234567891234567891234567891234);--40
已创建 1 行。
提交完成。

SQL> insert into test0 values(4,12345678912345678912345678912345678912345);--41
已创建 1 行。
提交完成。

SQL> insert into test0 values(6,1234567891234567891234567891234567891234.567);--【40,3】
已创建 1 行。
提交完成。

SQL> insert into test0 values(7,123456789123456789123456789123456789123.4567);--【39,4】
已创建 1 行。
提交完成。

SQL> insert into test0 values(8,12345678912345678912345678912345678912.34567);--【38,5】
已创建 1 行。
提交完成。

SQL> select * from test0;

        ID        NUM
---------- ----------
         1 1.235E+125
         3 1.2346E+39
         4 1.2346E+40
         6 1.2346E+39
         7 1.2346E+38
         8 1.2346E+37

通过JDBC获取到的数据是：

System.out.println(rs.getInt(1)+","+rs.getBigDecimal(2)+","+rs.getBigDecimal(2) .toString().length());

print
-----------------------------------------
1,123456789123456789123456789123456789123500000000000000000000000000000000000000000000000000000000000000000000000000000000000000,126
3,1234567891234567891234567891234567891234,40
4,12345678912345678912345678912345678912300,41
6,1234567891234567891234567891234567891235,40
7,123456789123456789123456789123456789123,39
8,12345678912345678912345678912345678912.35,41

Postgresql 9.3

Numeric 任意精度数字类型

http://www.postgresql.org/docs/9.3/interactive/datatype-numeric.html#DATATYPE-NUMERIC-TABLE

存储空间：变长

描述：用户声明精度，位数精确。

范围：小数点以前最多 131072位，小数点以后最多16383 位。

标度(scale)：是小数部分的位数，scale>=0

精度(precision)：是全部数据位的数目，也就是小数点两边的位数总和。

仅支持精度>=标度。

例子：数字 23.5141 的精度为 6 而标度为 4 。可以认为整数的标度scale为零。

常见形式：

numeric 字段的最大精度和最大标度都是可以配置的。要声明一个字段的类型为 numeric ，可以用下面的语法：

NUMERIC(precision, scale)，其中precision>0，scale>=0。

NUMERIC(precision),此时即scale缺省为0，即等价于NUMERIC(pre3cision,0)。

NUMERIC不带任何精度与标度的声明，则创建一个可以存储一个直到实现精度上限的任意精度和标度的数值，一个这样类型的字段将不会把输入数值转化成任何特定的标度，而带有标度声明的 numeric 字段将把输入值转化为该标度。

我的个人测试例子（postgresql9.3.4）如下，得出小结论：numeric不同于oracle的number，numeric能准确存储126位，甚至更高位的数字。

jnggzy=> create table ttt1(id int, num numeric);
CREATE TABLE

jnggzy=> insert into ttt1 values(1,123456789123456789123456789123456789123456789
12345678912345678912345678912345678912345678912345678912345678912345678912345678
9);---126
INSERT 0 1

jnggzy=> insert into ttt1 values(1,123456789123456789123456789123456789123456789
12345678912345678912345678912345678912345678912345678912345678912345678912345678
9123456789);---135
INSERT 0 1

jnggzy=> insert into ttt1 values(3,123456789123456789123456789123456789123456789123456789123456789123456789123456789123456789123456789123456789123456789123456789123456789.123456789);--【135,9】
INSERT 0 1

jnggzy=> select * from ttt1;
 id |                                                                   num

----+---------------------------------------------------------------------------
  1 | 123456789123456789123456789123456789123456789123456789123456789123456789123456789123456789123456789123456789123456789123456789
  1 | 123456789123456789123456789123456789123456789123456789123456789123456789123456789123456789123456789123456789123456789123456789123456789
  3 | 123456789123456789123456789123456789123456789123456789123456789123456789123456789123456789123456789123456789123456789123456789123456789.123456789
(3 行记录)