PostgreSQL sum typecasting as a bigint when argument types are int

Question

I know someone asked the same question from PostgreSQL sum typecasting as a bigint a while ago, but I don't see it was answered. I am adding value of a column whose type is integer using sum function, but it will overflow when I adding two 1.5 billion. I want the sum result to be bigint. Is there anyway to achieve it? Thanks in advance. I tried following but didn't work.

sum(count)::bigint AS total

If I do as following I am still getting error sum(count::bigint) AS total

Caused by: org.postgresql.util.PSQLException: ERROR: cannot change data type of column "total" from integer to numeric

Answer 1

You should cast before to sum it. That is:

sum(count::bigint) as total

In postgres sum(integer) and sum(bigint) are different functions which returns, respectively, integer and big integer.

In fact, all postgres functions are identified not only by its name but by the combination of its name and its argument types.

If you don't cast before, then you end up using integer version of sum() which always return integer. Even if you later cast it to bigint. If it's result is an overflow, you can't cast overflow to bigint.

EDIT: As abelisto rightly points, sum() yet returns bigint for smallint and integer. But, as I can see, your error message says that "cannot change type of column total from integer to numeric". But as far as I understand, "total" is the result of the whole operation, so it should be bigint (even if overflow).

...Not sure if it tries to point to the "count" column which (after operation) is labeled as "total" (but it stucks me...) or if it simply saying that it can't cast numeric to bigint (which seems more feasible to me). It depends of the actual type of count column. Is it already bigint or numeric?

If it is, the problem is probably in trying to cast as bigint a very huge numeric (of numeric type I mean) value.

Can you tell us the exact type of "count" colunm? And better than that: can you provide a failing example with a literal value?

Something like (but I only got an "bigint out of range" error...):

somedb=> with foo as (
    select 1000000000 as a
    union select 231234241234123
    union select 99999999999999999999999
) select sum(a) from foo;
           sum            
--------------------------
 100000000231235241234122
(1 row)

somedb=> with foo as (
    select 1000000000 as a
    union select 231234241234123
    union select 99999999999999999999999
) select sum(a)::bigint from foo;
ERROR:  bigint out of range