Examples of DECIMAL Calculations ​

This section contains some examples that demonstrate how Yellowbrick handles calculations that involve DECIMAL columns and constants.

Multiplication Examples ​

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)

The avg_att column is a 5,3 column. 1.1 is a 2,1 constant value. The resulting precision and scale is 6,4.

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

Addition Examples ​

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)

Assume that the dectest table has two DECIMAL(7,2) columns, dec1 and dec2. The following functions yield results that are DECIMAL(38,2):

sum(dec1) = decimal(38,2)
sum(dec2) = decimal(38,2)

The addition of these two columns also yields a DECIMAL(38,2) column:

sum(decimal(38,2)) + sum(decimal(38,2)) = decimal(38,2)

Parent topic:DECIMAL