PostgreSQL: how to resolve "numeric field overflow" problem

Question

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.

This not a schema but a dump from some internal postgres table. If you could have the courtesy to produce something readable, like the output from \d <tablename> or the CREATE TABLE statement that was used to create this table (pgAdmin3 can help you with this)? Also, a field declared numeric(X,Y) can use up to X places, with Y decimals right to the comma. Which means that if X=Y you can only store values < 1. — Nordic Mainframe
– Nordic Mainframe, Commented Sep 7, 2011 at 20:54
Also, please include the column list in your INSERTs, depending on any particular column order is a bad idea. — mu is too short
– mu is too short, Commented Sep 7, 2011 at 21:02

Peter Eisentraut · Accepted Answer · 2011-09-08 04:09:24Z

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 Sep 8, 2011 at 4:09

Peter Eisentraut

37.1k12 gold badges91 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

edward_wong Over a year ago

i know that you are correct, but i honestly can't understand. how constraint of 17 digits before and 17 digits behind the decimal point leads to [0,1] possible value range?

edward_wong Over a year ago

for those who got confused a little bit as i did. precision - number of digits totally; scale - number of decimal places. If you get number(17, 17) - you say: number will store 17 digits as a whole AND reserve 17 digits for decimal points. Leaving with range [0, 1].

Natim · Accepted Answer · 2017-02-14 13:57:30Z

1

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

For instance TIMESTAMP are a kind of BIGINT with a limit of 9223372036854775807 so you might want to validate that the integer your are passing in your query is below that value.

answered Feb 14, 2017 at 13:57

Natim

18.2k25 gold badges101 silver badges150 bronze badges

Collectives™ on Stack Overflow

PostgreSQL: how to resolve "numeric field overflow" problem

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related