Alex Rivera | Logout

PostgreSQL: how to resolve "numeric field overflow" problem

Asked 2011-09-07T20:35:49.090
42

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

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.

Edit
Report

1 Answer

87

It looks like you have your year and week_of_month columns 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.

answered 2011-09-08T04:09:24.343

Your Answer