How to avoid floating point overflows in PostgreSQL?

Question

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?

Edouard · Accepted Answer · 2021-11-14 09:09:13Z

1

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.

edited Nov 14, 2021 at 9:09

answered Nov 13, 2021 at 17:49

Edouard

7,1291 gold badge11 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Serg Over a year ago

You can use the above function in a select as well select multiply_by_float(1E300::float, 1E300::float)

arutar · Accepted Answer · 2021-11-13 22:41:52Z

you can use numeric and decimal types PG doc

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

result

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

That just delays the inevitable. Try this: select 1E300::decimal ^ 1000

Collectives™ on Stack Overflow

How to avoid floating point overflows in PostgreSQL?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related