Thread: avg() bug?

Hi!

I'm not sure if this is a bug, but it's sure unpleasant (for me).

(pseudocode)
create table t (a int2);

insert into t values (random number); <-- performed many, many times

now the following will return very unpredictable values:

select avg(a) from t;

while the following behaves as I would expect:

select avg(a::int4) from t;

I suppose avg() does a sum()/count() where sum returns a int2,
which will be too small to contain the sum if the table contains
many values?

Is this something I must know as a user, or is it a bug?


Regards,
Patrik Kudo

--
ech`echo xiun|tr nu oc|sed 'sx\([sx]\)\([xoi]\)xo un\2\1 is xg'`ol
Känns det oklart? Fråga på!

Patrik Kudo <kudo@partitur.se> writes:
> I suppose avg() does a sum()/count() where sum returns a int2,
> which will be too small to contain the sum if the table contains
> many values?

Yes.  This is changed in 7.1 --- sum() and avg() of int values now
return type 'numeric'.

            regards, tom lane

Thanks Tom!

I'll use sum(a::int4) until 7.1 then.

Regards,
Patrik Kudo

ech`echo xiun|tr nu oc|sed 'sx\([sx]\)\([xoi]\)xo un\2\1 is xg'`ol
Känns det oklart? Fråga på!

On Mon, 5 Mar 2001, Tom Lane wrote:

> Patrik Kudo <kudo@partitur.se> writes:
> > I suppose avg() does a sum()/count() where sum returns a int2,
> > which will be too small to contain the sum if the table contains
> > many values?
>
> Yes.  This is changed in 7.1 --- sum() and avg() of int values now
> return type 'numeric'.
>
>             regards, tom lane
>