Menu
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.
837
What does the error 'Numeric Field Overflow' mean? Cause: This error message can occur for a couple reasons: 1: The table or spreadsheet being imported has exceeded the maximum number of characters allowed in an Atlas .
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. (Emphasismine)
If we try to insert the larger values than the allowed/specified size of the numeric column, Teradata will throw the numeric overflow error. Lets see the numeric overflow error for Decimal column with example.
The insert query is trying to select and insert the record with the login count as 18292892 + 19292929982 for the customer id=455. Since the login count value is exceeds the 8 digits during computation in the SELECT CASE statement, it is throwing the numeric overflow error as below.
Summary: in this tutorial, you will learn about the PostgreSQL NUMERIC type for storing numeric data. The NUMERIC type can store numbers with a lot of digits. Typically, you use the NUMERIC type for numbers that require exactness such as monetary amounts or quantities.
The PostgreSQL logs will give you a pretty clear indication of the problem (for example, in the log snippet shown below the archive_command is failing repeatedly). The log will look similar to below: A. Thoroughly read the PostgreSQL documentation on how to set up archiving.
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.
88
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.
38
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
