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?

link to sqlfiddle example
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&gt;+9223372036854775807 THEN +9223372036854775807 WHEN val&lt;-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&gt;+9223372036854775807 THEN +9223372036854775807 WHEN val&lt;-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'