How to avoid floating point overflows in PostgreSQL?-CodePudding

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?

CodePudding user response：

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
  RETURN 'Infinity';
END ;
$$ ;

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

CodePudding user response：

you can use numeric and decimal types PG doc

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

result

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