Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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
                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
                Sep 7, 2011 at 21:02
                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
                Aug 5, 2022 at 11:10
                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].
– edward_wong
                Aug 5, 2022 at 11:24
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.
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.