PostgreSQL：如何解决“数字字段溢出”问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7340215/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL: how to resolve "numeric field overflow" problem
提问by daydreamer
I have a table with the following schema
我有一个具有以下架构的表
COLUMN_NAME, ORDINAL_POSITION,...., NUMERIC_PRECISION_INTEGER
"year";1;"";"YES";"numeric";;;17;10;17 "month_num";2;"";"YES";"numeric";;;17;10;17 "month_name";3;"";"YES";"text";;1073741824;;;
"week_of_month";4;"";"YES";"numeric";;;17;10;17
"count_of_contracts";5;"";"YES";"bigint";;;64;2;0
COLUMN_NAME, ORDINAL_POSITION,...., NUMERIC_PRECISION_INTEGER
"year";1;"";"YES";"numeric";;;17;10;17 "month_num";2;"";"YES";"numeric" ;;;17;10;17 "month_name";3;"";"YES";"text";;1073741824;;;
"week_of_month";4;"";"YES";"numeric";;;17;10;17
"count_of_contracts";5;"";"YES";"bigint";;;64;2;0
but when I insert the following into it
但是当我将以下内容插入其中时
insert into contract_fact values(2011, 8, 'Aug', 1, 367)
I see the following error
我看到以下错误
ERROR: numeric field overflow
SQL state: 22003
Detail: A field with precision 17, scale 17 must round to an absolute value less than 1.
错误：数字字段溢出
SQL 状态：22003
详细信息：精度为 17、小数位数为17 的字段必须四舍五入为小于 1 的绝对值。
回答by Peter Eisentraut
It looks like you have your yearand week_of_monthcolumns defined as numeric(17,17), which means 17 digits, 17 of which are behind the decimal point. So the value has to be between 0 and 1. You probably meant numeric(17,0), or perhaps you should use an integer type.
看起来您的year和week_of_month列定义为numeric(17,17)，这意味着 17 位数字，其中 17 位在小数点后面。因此该值必须介于 0 和 1 之间。您可能是指numeric(17,0)，或者您应该使用整数类型。
回答by Natim
I had a similar problem even without having set an upper limit. If this happens to you, you might want to look at the global PostgreSQL limits here: https://www.postgresql.org/docs/9.6/static/datatype-numeric.html
即使没有设置上限，我也遇到了类似的问题。如果您遇到这种情况，您可能需要在此处查看全局 PostgreSQL 限制：https: //www.postgresql.org/docs/9.6/static/datatype-numeric.html
For instance TIMESTAMP are a kind of BIGINT with a limit of 9223372036854775807so you might want to validate that the integer your are passing in your query is below that value.
例如 TIMESTAMP 是一种 BIGINT，其限制为 ，9223372036854775807因此您可能想要验证您在查询中传递的整数是否低于该值。
