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

Answer 1

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