4

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

  • 1
    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
  • 1
    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
0

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'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.