PostgreSQL Aggregation – How to Handle Out-of-Range Errors

aggregatepostgresql

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

Best Answer

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'

Related Question