Re: Weird problem with aggregate functions

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: 06 November 2001 19:03
> To: andrea(dot)aime(at)comune(dot)modena(dot)it
> Cc: gustavoboiko(at)ieg(dot)com(dot)br; Postgres ODBC
> Subject: Re: [ODBC] Weird problem with aggregate functions
>
>
> "Andrea Aime" <aaime(at)comune(dot)modena(dot)it> writes:
> > Thanks, it works!!! But why doesn't it work without the
> > cast? This is a good workaround, but the standard behaviour
> > is a bug, isn't it?
>
> No, it isn't. We *used* to return int4 as the result of
> sum(int4), etc, but that tends to suffer from overflow. And
> the deficiency of using int4 as the result of avg() should be
> pretty obvious ;-)
>
> The SQL spec says that we can use any exact numeric datatype
> we please for these functions:
>
> b) If SUM is specified and DT is exact numeric with scale
> S, then the data type of the result is exact
> numeric with
> implementation-defined precision and scale S.
>
> c) If AVG is specified and DT is exact numeric,
> then the data
> type of the result is exact numeric with implementation-
> defined precision not less than the precision of DT and
> implementation-defined scale not less than the
> scale of DT.
>
> From what I've heard, VB simply does not recognize Postgres'
> NUMERIC datatype at all. I don't know if that's VB's fault
> or a problem in our ODBC driver.

As I recall the problem is not with VB specifically, but with ADO (ActiveX
Data Objects) so this problem would affect other tools such as VC++, MS
Access (2000/XP use ADO I believe) and Visual Foxpro(?). It would probably
also affect Active Server Pages on IIS come to think of it...

I'll stick a note about it in the FAQ.

Regards, Dave.