Examples of DECIMAL Calculations

This example shows the resulting precision and scale when two DECIMAL values are multiplied:

premdb=# select avg_att, avg_att*1.1 att11 from team where avg_att>50;
 avg_att |  att11  
---------+---------
  59.944 | 65.9384
  54.041 | 59.4451
  75.286 | 82.8146
(3 rows)

Assume that a table named dec385 contains a DECIMAL(38,5) column. The column contains two values:

premdb=# select * from dec385;
                   c1                    
-----------------------------------------
 955555555555555555555555555555555.55553
 999999999999888777666555444333222.11111
(2 rows)

An attempt to multiply these values with a DECIMAL value greater than 1 returns an overflow error. The resulting precision and scale for the multiplication is (40,6), computed from (38,5)+(2,1). Yellowbrick DECIMAL values have a maximum precision of 38, of which 6 digits are retained for the scale (for multiplication). The calculated values in this example do not fit into a (38,6) column, so the query returns an overflow error.

premdb=# select c1*1.1 from dec385;
ERROR:  numeric value out of range

This example adds (38,5) column values to a (38,9) constant value. The scale of the result is not reduced, and the query returns an overflow error.

premdb=# select c1+12345678912345678912345678912.123456789 from dec385;
ERROR:  numeric value out of range

This query will run if you manually cast the result:

premdb=# select c1+12345678912345678912345678912.123456789::decimal(38,5) from dec385;
                 ?column?                 
------------------------------------------
  955567901234467901234467901234467.67899
 1000012345678801123345467790012134.23457
(2 rows)