Exponential issue in postgresql

Question

I'm facing an issue with the exponential function in postgresql. If I use this select statement select exp(5999), I'm getting:

ERROR: value out of range: overflow 
SQL state: 22003

If I use this select statement select exp(5999.1), I'm getting the exponential result.

In other case if I use this statement select exp(9999.1), I'm getting the following error:

ERROR: argument for function "exp" too big
SQL state: 22003

Please let me know why this issue happening and what is the solution for this kind of issue?

Answer 1

I think your first problem is caused by the fact that the output type of exp() is the same as the input type. Because you're using an integral value, it's complaining that the result won't fit in an integer.

That's likely why exp(5999.1), and probably exp(5999.0) works, the floating point type has a larger range.

Your second error is slightly different, it's complaining not of overflow during the calculation, but of the fact the input argument is too large. It's possible that it has some sanity checking on the input somewhere.

Even floating point values run out of range eventually. e⁹⁹⁹⁹ is somewhere around 10⁴³⁰⁰, a rather large number and probably well beyond what you'd expect to see in a database application.

In fact, I'd be interested in the use case of such large numbers in a database application. This sounds like something better suited to a bignum package like MPIR.

Answer 2

If you pass an INTEGER argument the exp() function will try to return double precision value. Just above value n=709 it will reach the limit of 64-bit floating point number (about 10^308) and fail to calculate the e^n. The solution is to pass your argument with NUMERIC type:

SELECT EXP(710); -- failure!
SELECT EXP(710::NUMERIC); -- OK
SELECT EXP(5999.1::NUMERIC); -- huge but OK

---

EDIT!

As for the ERROR: argument for function "exp" too big SQL state: 22003. I've tried to write a work-around. Just run this:

SELECT n, POWER(EXP(1::NUMERIC), n) FROM (VALUES(9998), (9999), (10000)) AS foo (n)

and it will work. But then change 9999 to 9999.1 and you will get that stupid error again. This is ridiculous! 9999.1 is too big but 10000 is fine :D It looks like Postgres doesn't like decimal point in POWER()'s argument. Sorry but I can't fix that.

One solution would be to use the arithmetic property of power and write POWER(POWER(EXP(1::NUMERIC), n*10), 0.1) but that value combination is still too big for Postgres' implementation of power. Good luck with your battle.