Question 114244 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL calculated column not displaying numbers after decimal and rounding value to 0

I’m trying to perform a following calculation in a query:

t0.value1::decimal * 10 ^ (-1 * t2.value2::decimal) * t0.value3::decimal as total_value

However since (-1 * t2.value2::decimal) returns -18 all the results are rounding up to 0. I tried explicitly stating decimal scale and precision//changing it to numeric but got the same output.
It works just fine as long as (-1 * t2.value2::decimal) is more than -10 and less than 10 (if the number of decimals is less than 10).

How can it be solved?

UPD: here is the data sample

value1 value2 value3
4600000000000000000 18 4631.012195988508
5000000000000000000 18 3273.4212494812623
18 2422.052197425213
25000000000000000000 18 2549.800132829858
9690000000000000000 18 4109.547860742057
5980000000000000000 18 2957.913800830533
4200000000000000000 18 3410.6366004760075
8000000000000000000 18 3902.894047163281
6000000000000000000 18 4604.587023538565
Tags:

2

Answers


  1. 0

    x^(-y) = 1/x^y and that works.

    select value1::decimal * (1/10 ^(value2::decimal)) * value3::decimal as total_value
from   t
    total_value
    21302.656101547136800000000000000000000000000000000000
    16367.1062474063115000000000000000000000000000000000000
    63745.003320746450000000000000000000000000000000000000
    39821.518770590532330000000000000000000000000000000000
    17688.324528966587340000000000000000000000000000000000
    14324.6737219992315000000000000000000000000000000000000
    31223.152377306248000000000000000000000000000000000000
    27627.522141231390000000000000000000000000000000000000

    Fiddle

    Login or Signup to reply.
  2. 0

    10 is interpreted as a decimal with scale 20. Give it higher precision and scale:

    t0.value1::decimal * 10::decimal(40,20) ^ (-1 * t2.value2::decimal) * t0.value3::decimal as total_value

    This behavior was considered a bug by PostgreSQL development and has been fixed in this commit in PostgreSQL v16. The fix will not be applied to older versions, to avoid "the risk of changing query
    results in stable branches".

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search