How can we handle out-of-range errors when aggregating data?
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'