今天发现一生产库报出大量 “numeric field overflow” 错误，详细报错日志如下：

数据库日志

取数据库报错日志中的一行，如下。

1

2013-09-29 15:12:05.830 CST,"db_name","db_name",7195,"XXXX.XXX.XXX.XX51344",5247d21f.1c1b,19,"INSERT",2013-09-29 15:09:19 CST,512/17000814,0,ERROR,22003,"numeric field overflow","A field with precision 10, scale 0 must round to an absolute value less than 10^10."

备注：这里仅取一行，而且还省略了日志中的 SQL 语句。这个报错说明是整型字段超出指定精度，接着模拟下。

模拟测试

创建测试表

1
2
3
4
5
6
7
8

rancs=> create table test_num (rate numeric(4,1));
CREATE TABLE
  
francs=> /d test_num
   Table "francs.test_num"
Column |   Type   | Modifiers
--------+--------------+-----------
rate  | numeric(4,1) |

插入数据测试

1
2
3
4
5
6
7
8

francs=> insert into test_num (rate) values (100);
INSERT 0 1
  
francs=> insert into test_num (rate) values (100.3);
INSERT 0 1
  
francs=> insert into test_num (rate) values (1000.3);
ERROR: numeric field overflow

备注：预期错误出现。

数据库报错

2013-09-29 15:41:55.471 CST,"francs","francs",27916,"[local]",5247d912.6d0c,4,"INSERT",2013-09-29 15:38:58 CST,3/1036,0,ERROR,22003,"numeric field overflow","A field with precision 4, scale 1 must round to an absolute value less than 10^3.",,,,,"insert into test_num (rate) values (1000.3);",,,"psql"

备注：关于 NUMERIC(precision, scale)，文档中有详细说明，precision 表示整个 numeric 的长度，scale 表示小数部分的长度，手册中说明如下：

手册中说明

NUMERIC(precision, scale)
We use the following terms below: The scale of a numeric is the count of decimal digits in the fractional part, to the right of the decimal point. The precision of a numeric is the total count of significant digits in the whole number, that is, the number of digits to both sides of the decimal point. So the number 23.5141 has a precision of 6 and a scale of 4. Integers can be considered to have a scale of zero.

备注：原因已经很清楚了，接下来联系项目组，修复这个错误。

参考

• http://www.postgresql.org/docs/9.3/static/datatype-numeric.html