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

bitifet · Accepted Answer · 2016-07-22T18:43:52.540

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

["_bigint for smallint or int arguments, numeric for bigint arguments, otherwise the same as the argument data type_"](https://www.postgresql.org/docs/current/static/functions-aggregate.html) — Abelisto, Jul 22 '16 at 18:19
@bitifet your answer in the EDIT section was correct. I was trying to create a view from a table column and the column type is int. SUM() did return bigint for int. So, I didn't need a casting. The error message was kind of misleading though. It turns out that my problem was changing column of a view without dropping the view first. I was updating a column of a view. So, I will make it as an answer. — user3123690, Jul 22 '16 at 19:36
idk but it doesn't work with pre-casting, I have a column which is `bigint` itself and I'm trying to do `sum(my_column)` but it returns `int`, it returns `bigint` only this way `sum(my_column)::bigint`. Tried on psql v10 and 12. — imike, May 22 '20 at 12:11
I guess you should be overlooking something... Can you provide an example? What happen with `sum(my_column::bigint)`? — bitifet, May 24 '20 at 22:55

PostgreSQL sum typecasting as a bigint when argument types are int

1 Answers1