How to avoid floating point overflows in PostgreSQL?

user_521799 0
Viewed 157

In PostgreSQL, when running a computation like this:

select 1E300::float * 1E300::float

I'd like to receive an 'Infinity'::float value, similar to when I do that in Java or CockroachDB. Instead, I'm getting:

SQL Error [22003]: ERROR: value out of range: overflow

SQLFiddle here. How can I do this?

floating-point postgresql sql
2 Answers

if you can encapsulate your select statement in a plpgsql function then you can manage the error like this :

CREATE OR REPLACE FUNCTION multiply_by_float(a float, b float)
RETURNS float LANGUAGE plpgsql IMMUTABLE AS
$$
DECLARE 
  res float ;
BEGIN
  SELECT a * b INTO res ;
  RETURN res ;
EXCEPTION WHEN OTHERS THEN
  CASE
    WHEN (a > 0 and b < 0) or (a < 0 and b > 0)
    THEN RETURN '-Infinity';
    ELSE RETURN 'Infinity' ;
  END CASE ;
END ;
$$ ;

Then SELECT multiply_by_float(1E300::float, 1E300::float) returns Infinity.

user_80600170

you can use numeric and decimal types PG doc

select 1E300::decimal * 1E300::decimal;

result

select 1E300::decimal * 1E300::decimal;
                                                                                                                                                                                                                                                                                                         ?column?                                                                                                                                                                                                                                                                                                       
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 1000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
user_171283500
Related