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-
- or other ideas, how to avoid this
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
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
link to sqlfiddle example