> > Hi all,
> >
> > i found a strange behaviour in Postgresql 7.0 on FreeBSD 4.0
> >
> > $ createdb foo
> > $psql foo
> > foo=# create table number (a_number int);
> > foo=# insert into number values(1500000000);
> > foo=# insert into number values(1600000000);
> > select avg(a_number) from number;
> > avg
> > ------------
> > -597483648
> >
>
> test=# select avg(a_number::int8) from number;
> avg
> ------------
> 1550000000
> (1 row)
>
> The parser select function from column type. You use 'int' --- the parser
> use avg(int4)....
> Karel
Dear Karel,
thank you for your solution. You can do it this way, but if you have really
big numbers or many rows, you cannot be sure, at which point you have to use
your solution or you have to use it every time, just to be sure.
I was thinking about a solution that doesn't cause an overflow. Please
consider this situation: At the time you will start the query, you will know
how many rows are affected. So you can calculate the average using the formula
row_1 / number_of_rows + row_2 / number_of_rows + ...
you will have to use floats for this calculation and i'm not sure on the
effect of processing time, but you will never get an overflow.
Norbert