PostgreSQL sum typecasting as a bigint

Question

I am doing the sum() of an integer column and I want to typecast the result to be a bigint - to avoid an error. However when I try to use sum(myvalue)::bigint it still gives me an out of range error.

Is there anything that I can do to the query to get this to work? Or do I have to change the column type to a bigint?

`sum(myvalue)::bigint` means "calculate the aggregate function `sum` over `myvalue`, then cast the result of that to `bigint`" - so by the time the cast happens, `sum()` has already chosen its result type. — IMSoP, Nov 25 '13 at 21:10
From the doc, sum returns `bigint for smallint or int arguments, numeric for bigint arguments, double precision for floating-point arguments, otherwise the same as the argument data type`, so the internal cast may not be necessary anyway. Realistically, the number of rows it would take for a sum of ints to overflow bigint would likely fail on other things first (like disk space, IO bandwidth, etc...). Although I have seen a few fringe cases where it actually does overflow. — Nisan.H, Nov 25 '13 at 21:33

Erwin Brandstetter · Answer 1 · 2022-06-19T21:37:36.173

5

The current manual is more explicit than it used to be in 2013:

sum ( integer ) → bigint

If your column myvalue indeed has the type integer like you say, then the result is bigint anyway, and the added cast in sum(myvalue)::bigint is just noise.

Either way, to get an "out of range" error, the result would have to be bigger than what bigint can hold:

-9223372036854775808 to +9223372036854775807

One would have to aggregate a huge number of big integer values (>= 2^32 * 2^31). If so, cast the base column to bigint, thereby forcing the result to be numeric:

SELECT sum(myvalue::int8) ...

The more likely explanation is that your column has, in fact, a different data type, or the error originates from something else. Not enough detail in the question.

edited Jun 19 '22 at 21:37

answered Nov 25 '13 at 21:01

Erwin Brandstetter

605,456
145
1,078
1,228

Good guess, but I should have specified that the values were within the bigint range. – Aaron Kreider Nov 25 '13 at 21:14
@AaronKreider: Generally you should provide the table definition (what you get from psql with `\d mytable`) and be more specific. "The values" is still ambiguous ... – Erwin Brandstetter Nov 25 '13 at 21:20

score 1 · Answer 2 · answered Sep 07 '17 at 04:24

1

I solved my problem using following statement

SUM(CAST(gross_amount AS Integer))

This is give the result of the column as SUm bigint,

Note:My column gross_amount was double type.

answered Sep 07 '17 at 04:24

Prabin Poudel

73
1
1
9

If the `sum (gross_amount)` is out of range for `bigint`, but `SUM(CAST(gross_amount AS Integer))` is not, then the cast must have truncated values, leading to false results. Besides, highly unlikely. – Erwin Brandstetter Jun 19 '22 at 21:44

score 0 · Answer 3 · answered Feb 14 '19 at 17:44

0

You need to cast it before doing the operation:

SUM(myvalue::bigint)

answered Feb 14 '19 at 17:44

Gunar Gessner

2,331
23
23

PostgreSQL sum typecasting as a bigint

3 Answers3

Linked