Is there a workaround when an underflow error occurs in PostgreSQL?

Question

I have a table containing in each row the a, b and c parameters to an exponential curve in the following formula:

a*exp(-b*x)+c

All columns are of type double precision. I wish to sample each curve at a specific value of x and record its value in a new column. However, doing so causes an underflow error.

>update curve_by_wkt set samplepoint05 = a*exp(-b*0.05)+c;
ERROR:  value out of range: underflow

There is no way I can manually check which values of a, b, c are causing the calculation to fail. What I do know is that extreme precision is not needed, and that values outside a certain range are likely irrelevant.

I would like to know if there is any way I could get the calculation to complete, given my disregard for precision.

Thanks

http://stackoverflow.com/questions/413940/how-can-i-prevent-detect-an-underflow-in-a-postgresql-calculation-that-uses-exp — Nick Barnes, Dec 03 '15 at 20:43

score 2 · Accepted Answer · answered Dec 03 '15 at 23:13

Create a function and catch an exception. You can raise your own error message:

create or replace function calculate_with_error
    (a double precision, b double precision, c double precision, x double precision)
returns double precision language plpgsql as $$
begin
    return a * exp(-b * x) + c;
    exception when numeric_value_out_of_range then 
        raise exception 'Value out of range for a = % b= % c= % x= %', a, b, c, x;
end $$;

select calculate_with_error(1, 1, 0, 10000);

ERROR:  Value out of range for a = 1 b= 1 c= 0 x= 10000

or set some default value:

create or replace function calculate_with_default
    (a double precision, b double precision, c double precision, x double precision)
returns double precision language plpgsql as $$
begin
    return a * exp(-b * x) + c;
    exception when numeric_value_out_of_range then 
        return 0;
end $$;

select calculate_with_default(1, 1, 0, 10000);

 calculate_with_default 
------------------------
                      0
(1 row)

Usage:

update curve_by_wkt 
set samplepoint05 = calculate_with_error(a, b, c, 0.05);

score -1 · Answer 2 · answered May 01 '17 at 14:27

-1

A better solution (most likely) is to just use the greatest function to set a minimum value. e.g.,

 greatest(a*exp(-b*x)+c, -50)

Most of the time 0 or some arbitrarily low number is good enough.

answered May 01 '17 at 14:27

Max Candocia

4,294
35
58

1

This doesn't help. `exp(-1000)` produces an underlflow error, and so does `greatest(exp(-1000),1e-15)`. – Matt May 08 '18 at 19:36

Is there a workaround when an underflow error occurs in PostgreSQL?

2 Answers2