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;
(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;
(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)