CODEWITHSUNDEEP
postgresqlpgadminpostgresql-12
Jon Fillip
Jon Fillip

Reputation: 103

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

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)

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;

Upvotes: 2

Views: 10523

Answers (2)

Jon Fillip
Jon Fillip

Reputation: 103

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)

listing 1.1 results

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;

listing 1.2

Upvotes: 0

Ken
Ken

Reputation: 463

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:

  1. Perform the cast after you compute SUM(imls2016.visits) - SUM(imls2010.visits)
  2. 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

Upvotes: 2

Related Questions