openGauss6.0的numeric数据类型精度优化，你注意到了么？

在我看到官网文档中对于numeric数据类型精度的优化后，本以为是一件比较简单的测试验证，但是在展开测试后，不得不逐渐增加各种测试场景，来验证对于精度修改后带来的各种疑问。在这个介绍中，我们把numeric数据类型精度设置功能分为两个，分别为：

• 允许precision大于scale，原来就是这样的吧？
• 允许scale为负数
  

允许precision大于scale

当看到允许precision大于scale这个功能描述的时候，就在想这个不应该是正常逻辑么？以前版本也是这样的要求吧。 出于谨慎考虑的原则，在这里我们先验证允许precision大于scale，看看有啥不一样的地方；然后就大胆假设小心求证，是否有可能说的是precision小于scale的场景。

允许precision大于scale–发版验证

在这个场景下，对numeric的精度进行了设置，precision设置为4，scale设置为2。

create table numeric_type_t3(
nt numeric(4,2)
);
insert into numeric_type_t3 values(65.3548);

在openGauss5.0.3版本执行建表语句

testdb=> create table numeric_type_t3(
nt numeric(4,2)
);testdb(> testdb(>
CREATE TABLE
testdb=>
testdb=> insert into numeric_type_t3 values(65.3548);
INSERT 0 1
testdb=>
testdb=> select * from numeric_type_t3;
  nt
-------
 65.35
(1 row)

在openGauss6.0版本执行建表语句

testdb=> create table numeric_type_t3(
nt numeric(4,2)
);testdb(> testdb(>
CREATE TABLE
testdb=> insert into numeric_type_t3 values(65.3548);
INSERT 0 1
testdb=>
testdb=> select * from numeric_type_t3;
  nt
-------
 65.35
(1 row)

从上面的测试结果可知，当numeric数据类型precision大于scale的时候，不论在openGauss5.0.3版本还是openGauss6.0，执行结果都一直并不任何差别，在这里暂时不考虑测试用例与官网要求的功能不一致的情况。

允许precision小于scale–猜测验证

由于考虑到既然发版中有对于numeric数据类型精度设置，因此就猜测是否有可能是precision小于scale的场景呢。因此在下面的验证场景中，我们把precision设置为2，scale设置为4，尝试验证一下。

create table numeric_type_t2(
nt numeric(2,4)
);
insert into numeric_type_t2 values(65.3548);
insert into numeric_type_t2 values(0.46);
insert into numeric_type_t2 values(0.00461);

在openGauss5.0.3版本执行建表语句

testdb=> create table numeric_type_t2(
nt numeric(2,4)
);testdb(> testdb(>
ERROR:  NUMERIC scale 4 must be between 0 and precision 2
LINE 2: nt numeric(2,4)

从测试结果可知，对于openGauss5.0.3版本numeric数据类型的精度，precision必须大于等于scale。

在openGauss6.0版本执行建表语句

testdb=> create table numeric_type_t2(
nt numeric(2,4)
);testdb(> testdb(>
CREATE TABLE

插入数据并查询

testdb=> insert into numeric_type_t2 values(65.3548);
ERROR:  numeric field overflow
DETAIL:  A field with precision 2, scale 4 must round to an absolute value less than 10^-2.
CONTEXT:  referenced column: nt
testdb=> insert into numeric_type_t2 values(0.46);
ERROR:  numeric field overflow
DETAIL:  A field with precision 2, scale 4 must round to an absolute value less than 10^-2.
CONTEXT:  referenced column: nt
testdb=> insert into numeric_type_t2 values(0.00461)
testdb-> ;
INSERT 0 1
testdb=>
testdb=> select * from numeric_type_t2;
  nt
-------
 .0046
(1 row)

从测试结果可知，在openGauss6.0版本下，当numeric数据类型precision小于scale的时候，允许建表，但是对于precision 2, scale 4的设置，要求插入的数据要求小于10^{-2即-0.01。**那么对于numeric(p,s)，当p小于s时即s也为正数，对于插入的数据就要求必须小于10}-(s-p)值**。

允许scale为负数

允许scale为负数，scale的绝对值小于了precision

在这个验证场景中中，我们要求scale为负数，然后观察openGauss5.0.3和openGauss6.0版本的执行情况。

create table numeric_type_t1(
nt numeric(6,-2)
);

在openGauss5.0.3版本执行建表语句

testdb=> create table numeric_type_t1(
testdb(> nt numeric(6,-2)
testdb(> );
ERROR:  NUMERIC scale -2 must be between 0 and precision 6
LINE 2: nt numeric(6,-2)

在openGauss6.0版本执行建表语句

create table numeric_type_t1(
nt numeric(6,-2)
);
insert into numeric_type_t1 values(63.3548);
insert into numeric_type_t1 values(23344563.3548);
insert into numeric_type_t1 values(323344563.3548);

插入数据并查询

testdb=> select * from numeric_type_t1 ;
 nt
-----
 100
(1 row)

testdb=> insert into numeric_type_t1 values(23344563.3548);
INSERT 0 1
testdb=> select * from numeric_type_t1;
    nt
----------
 23344600
(1 row)

testdb=> insert into numeric_type_t1 values(323344563.3548);
ERROR:  numeric field overflow
DETAIL:  A field with precision 6, scale -2 must round to an absolute value less than 10^8.
CONTEXT:  referenced column: nt
testdb=>

从测试的结果，在openGauss6.0版本中允许scale为负数，其实这时是对整数位的精度进行了限制，当numeric数据类型precision 6, scale -2，最大值应该小于10^{8。**那么对于numeric(p,s)，s为负数，对于插入的数据就要求必须小于10}(p+|s|)值**。

允许scale为负数，但是scale的绝对值大于了precision

在这个验证场景中中，我们要求scale为负数，但是scale的绝对值大于了precision，在这里precision 为4，scale 为-6， 然后观察openGauss6.0版本的执行情况。

create table numeric_type_t5(
nt numeric(4,-6)
);
--验证小于scale绝对值位数的整数值
insert into numeric_type_t5 values(63.35);
insert into numeric_type_t5 values(78863000);
insert into numeric_type_t5 values(478863000);
--验证最大值
insert into numeric_type_t5 values(32478863000);
insert into numeric_type_t5 values(-78863000);

在openGauss6.0版本执行建表语句

testdb=> create table numeric_type_t5(
nt numeric(4,-6)
);testdb(> testdb(>
CREATE TABLE

插入数据并查询

testdb=> insert into numeric_type_t5 values(63.35);
INSERT 0 1
testdb=> insert into numeric_type_t5 values(78863000);
INSERT 0 1
testdb=> insert into numeric_type_t5 values(478863000);
INSERT 0 1
testdb=> insert into numeric_type_t5 values(2478863000);
INSERT 0 1
testdb=>
testdb=> insert into numeric_type_t5 values(32478863000);
ERROR:  numeric field overflow
DETAIL:  A field with precision 4, scale -6 must round to an absolute value less than 10^10.
CONTEXT:  referenced column: nt
testdb=> select * from numeric_type_t5;
     nt
------------
          0
   79000000
  479000000
 2479000000
(4 rows)

从测试的结果，在openGauss6.0版本中允许scale为负数且当numeric数据类型precision 4, scale -6，最大值应该小于10^{10，最小值应该大于10}6。

总结

数值类型的精度的调整，应该是比较麻烦的事情，有时真的牵一发而动全身，有时涉及的点挺多的。在这里我们只对允许scale为负数的测试结果总结一下，对于numeric(p,s)，s允许为负数，对于插入的数据就要求必须小于10^{(p+|s|)值，最小值建议应该大于10}(|s|)值，否则对于小于10^(|s|)值，在数据库中保存的都是0。对于允许precision大于scale，在这里不做总结，可能需要等待openGauss开源社区核实一下具体情况。