PostgreSQL:关于 "Numeric Field Overflow" 报错

今天发现一生产库报出大量 “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.

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

参考

最后推荐和张文升共同编写的《PostgreSQL实战》,本书基于PostgreSQL 10 编写,共18章,重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等,涵盖大量实战用例!

购买链接:https://item.jd.com/12405774.html

PostgreSQL实战
感谢支持!
0%