PostgreSQL Numeric Field Overflow Error - A field with precision 10, scale 1 must round to an absolute value less than 10^9

Question

Please I'm trying to find the percentage difference between two values sum of imls2016.visits and sum of imls2010.visits in each row. Which was previously working fine before I added a third table in my joint statement (the table being imls_regions). But anytime I run the new code it throws a numeric overflow error. I have adjusted the precision and scale but I haven't been able to rectify this error. Please what should I change in my code: (PostgreSQL 12)

Error Message: > "ERROR: numeric field overflow DETAIL: A field with precision 10, scale 1 must round to an absolute value less than 10^9. SQL state: 22003"

My code:

SELECT imlsreg.obereg,
        imlsreg.obereg_name,
        SUM(imls2016.visits) AS total_visits_2016,
        SUM(imls2010.visits) AS total_visits_2010,
        ROUND((CAST(SUM(imls2016.visits) AS decimal(10, 1)) - SUM(imls2010.visits)) / SUM(imls2010.visits) * 100, 1) AS pct_change_in_visits
FROM imls_regions AS imlsreg INNER JOIN imls_library_survey_2016 AS imls2016
ON imlsreg.obereg = imls2016.obereg
INNER JOIN imls_library_survey_2010 AS imls2010
ON imlsreg.obereg = imls2010.obereg
WHERE imls2016.visits >= 0 AND imls2010.visits >= 0
GROUP BY imlsreg.obereg, imlsreg.obereg_name
ORDER BY pct_change_in_visits ASC;

Can you provide some additional information about the `imls2010.visits` and `imls2016.visits` columns? What are the types of those columns? Is there a reason you are casting the 2016 visits column `AS decimal(10,1)` but not the 2010 column? — Ken, Mar 27 '20 at 16:51
The imls2016.visits and imls2010.visits are of integer data types and represents the numbers of visits made to a library the course of the year. My reason for casting imls2016.visits is to a the remainder of the division as decimal and not an integer which only returns the whole number divisible by. — Jon Fillip, Mar 27 '20 at 17:14

score 2 · Answer 1 · answered Mar 27 '20 at 17:45

When you cast to decimal(10,1), you are reducing the range of allowable values of SUM(imls2016.visits) in exchange for greater precision, but this is causing your result to overflow.

There are two things you can do:

Perform the cast after you compute SUM(imls2016.visits) - SUM(imls2010.visits)
Reduce the precision of the cast.

Implementing the first one may suffice, and you would define pct_change_in_visits as:

ROUND(CAST(SUM(imls2016.visits) - SUM(imls2010.visits) AS decimal(10, 1)) / SUM(imls2010.visits) * 100, 1) AS pct_change_in_visits

Jon Fillip · Accepted Answer · 2020-03-27T18:25:30.123

Okay I found the problem. My sum value had a precision that was more than 10 (meaning that the sum which when still in integer format had a length of 12 values) hence when casting it throws a Numeric field overflow error. To find out where I went wrong I removed the ROUND() AND CAST() functions in listing 1.1:

listing 1.1:

SELECT imlsreg.obereg,
        imlsreg.obereg_name,
        SUM(imls2016.visits) AS total_visits_2016,
        SUM(imls2010.visits) AS total_visits_2010,
        (SUM(imls2016.visits) - SUM(imls2010.visits)) / SUM(imls2010.visits) * 100 AS pct_change_in_visits
FROM imls_regions AS imlsreg INNER JOIN imls_library_survey_2016 AS imls2016
ON imlsreg.obereg = imls2016.obereg
INNER JOIN imls_library_survey_2010 AS imls2010
ON imlsreg.obereg = imls2010.obereg
WHERE imls2016.visits >= 0 AND imls2010.visits >= 0
GROUP BY imlsreg.obereg, imlsreg.obereg_name
ORDER BY pct_change_in_visits ASC;

This gave me the sum of visits I wanted to see but ignore the pct_change for now (most of the pct_change is 0 because dividing an two integers in sql gives you an integer hence it is important to cast an operating value to be a decimal or double precision numeric data-type)

After counting the number of length of the max visits value (12 in length) I was able to find an accurate precision value. I then reverted to my previous code and adjusted the precision to numeric (14, 1). See code in listing 1.2:

Listing 1.2:

SELECT imlsreg.obereg,
        imlsreg.obereg_name,
        SUM(imls2016.visits) AS total_visits_2016,
        SUM(imls2010.visits) AS total_visits_2010,
        ROUND((CAST(SUM(imls2016.visits) AS decimal(14, 1)) - SUM(imls2010.visits)) / SUM(imls2010.visits) * 100, 2) AS pct_change_in_visits
FROM imls_regions AS imlsreg INNER JOIN imls_library_survey_2016 AS imls2016
ON imlsreg.obereg = imls2016.obereg
INNER JOIN imls_library_survey_2010 AS imls2010
ON imlsreg.obereg = imls2010.obereg
WHERE imls2016.visits >= 0 AND imls2010.visits >= 0
GROUP BY imlsreg.obereg, imlsreg.obereg_name
ORDER BY pct_change_in_visits ASC;

PostgreSQL Numeric Field Overflow Error - A field with precision 10, scale 1 must round to an absolute value less than 10^9

2 Answers2