Avoiding numeric overflows in Redshift decimal multiplication

The size of the resulting data type of calculations with DECIMAL (or NUMERIC) types in Redshift depends on the operands. For multiplication the precision of the result equals the sum of the precision of the two operands plus 1 and the scale of the result equals the sum of the scale of the operands. For example

SELECT CAST(9.9 AS DECIMAL(2, 1)) * CAST(9.9 AS DECIMAL(2, 1))

returns a result of 98.01 with a data type of DECIMAL(5, 2). This page documents how the size of the result data type is calculated*.

This change in data type can cause numeric overflow. This following example causes an overflow.

SELECT CAST(1 AS DECIMAL(38, 19)) * CAST(2 AS DECIMAL(38, 19))

The resulting data type would be DECIMAL(77, 34), which causes an overflow as it exceeds the maximum precision and scale (38 and 37 respectively). You might think this example trivial, the scale of the numbers is too excessive, but as you add more operands the scale drops. Also the actual numbers in a 38 precision column are likely to be larger than 1 or 2 and the larger they are the smaller the maximum scale.

This behaviour is good when the size data type is smaller in the result (the first example), but frustrating when you get an error for a result that fits in the data type (the second example). Luckily there is another way to perform the calculation to avoid the error.

The workaround

Multiplication can be converted to division e.g. 2 x 2 becomes 2 / (1 / 2). Calculations that error can be converted to division instead of multiplication to avoid numeric overflows for example.

SELECT CAST(1 AS DECIMAL(38, 19)) / (1 / CAST(2 AS DECIMAL(38, 19)))

Be aware that fractions that cannot be represented in decimal (such as 1 / 3) will be a fraction off e.g.

SELECT CAST(1 AS DECIMAL(38, 19)) / (1 / CAST(3 AS DECIMAL(38, 19)))

returns 3.0000000000000003.

Additionally to make the calculation act like a multiplication and prevent divide by zero errors I recommend you make use of NVL and NULLIF e.g.

SELECT NVL(CAST(“column1” AS DECIMAL(38, 19)) / (1 / CAST(NULLIF(“column2”, 0) AS DECIMAL(38, 19))), 0)

Footnote

* The Redshift documentation has formulas for calculating the result size of a divide, but I don’t think they are accurate as division does not produce numeric overflows like multiplication does. According to the documentation

SELECT CAST(1 AS DECIMAL(38, 19)) / (CAST(1 AS DECIMAL(38, 19)) / CAST(2 AS DECIMAL(38, 19)))

would have a result data type of NUMERIC(155, 78), which is far greater than the size of the multiplications result data type, but no numeric overflow occurs.

Advertisements
Avoiding numeric overflows in Redshift decimal multiplication