Thread: Casting issue with aggregated function

Hi,

I'm facing a cast issue when using sub queries and I 'd like your opinion if this should be submittede as a Bug.

(Postgres version 8.1.0)

Compare:

A)

select sum(25665800) as c

        => 25665800 (int8)

B)

select sum(c) from
(
select sum(25665800) as c
)foo

        => 25665800 (numeric)

I find the cast form int8 to numeric being strange.

Cheers,

Marc

On Mon, Jan 02, 2006 at 11:18:53AM +0100, Marc Mamin wrote:
> select sum(c) from
> (
> select sum(25665800) as c
> )foo
>
>     => 25665800 (numeric)
>
> I find the cast form int8 to numeric being strange.

It's documented to work that way:

http://www.postgresql.org/docs/8.1/interactive/functions-aggregate.html

The return type for sum() is "bigint for smallint or int arguments,
numeric for bigint arguments, double precision for floating-point
arguments, otherwise the same as the argument data type."  Your
subquery has an integer argument so its sum() returns bigint; you
then feed that bigint to sum() so the result is numeric.  Presumably
the promotions are done to avoid overflow.

--
Michael Fuhr