# How can we handle out-of-range errors when aggregating data?

For example we have some data in `int8` fields and want to build aggregations: e.g. the sum of squares. The result should also be `int8` (not a float).

So obviously calculating the sum of squares for `int8`-values can overflow the `int8` range and we could get `bigint out of range` errors.

How can we handle that case?

• e.g. is there a way to "catch" this error and return a default (e.g. max-`int8`)?
• or other ideas, how to avoid this

Example:

``````create table test
( val int8);

insert into test VALUES (9223372036854775807), (9223372036854775807);
``````

This query will fail with `bigint out of range`:

``````select sum(val*val) from test;
``````

To make the sum work, we could cast to numeric:

``````select sum(val::numeric*val) from test;
``````

but our application needs a result of type `int8`, so we could use `least` and `greatest`

``````select greatest(+9223372036854775807, least(-9223372036854775808, sum(val::numeric*val))) from test;
``````

Is there some easy built-in way to "cast"/"convert"/"clamp" the `numeric` back to the `int8` range?

• `least(-9223372036854775808...` is not needed, because the product of `val*val` will always be positive, or did you 'over-simplify' the problem? – Luuk Nov 2 at 12:09
• @Luuk you are right, that in the case of sum-squares, this is not needed - but for others (e.g. sum), it is required – TmTron Nov 2 at 12:15
• our application needs a result of type int8: your application needs to deal with the possibility of an error. Otherwise it implies it's more important to have an int8 result than a correct result, which sounds very wrong. Plus any query can fail, anyway. – Daniel Vérité Nov 3 at 15:15
• @DanielVérité: Good point: I totally agree: whenever possible the errors should be handled by the outermost caller: i.e. pass the large numeric value to the app and the app should decide if it wants to throw an error or clamp the value. – TmTron Nov 4 at 8:14

If you create a user function (or 2), some parts of your 'problem' can be solved:

``````DROP FUNCTION IF EXISTS clamp(val numeric);
CREATE FUNCTION clamp(val numeric) RETURNS bigint AS \$\$
BEGIN
RETURN CASE WHEN val>+9223372036854775807 THEN +9223372036854775807 WHEN val<-9223372036854775808 THEN -9223372036854775808 ELSE val::bigint END;
END; \$\$
LANGUAGE PLPGSQL;

DROP FUNCTION IF EXISTS clamp(val double precision);
CREATE FUNCTION clamp(val double precision) RETURNS bigint AS \$\$
BEGIN
RETURN CASE WHEN val>+9223372036854775807 THEN +9223372036854775807 WHEN val<-9223372036854775808 THEN -9223372036854775808 ELSE val::bigint END;
END; \$\$
LANGUAGE PLPGSQL;
``````

After this, you can do:

``````select clamp(9223372036854775807 * 9223372036854775807::float);
``````

This will return the bigint `9223372036854775807`, which is 'Infinity'