Re: Weird problem with aggregate functions

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: 06 November 2001 19:03
> To: andrea.aime@comune.modena.it
> Cc: gustavoboiko@ieg.com.br; Postgres ODBC
> Subject: Re: [ODBC] Weird problem with aggregate functions
>
>
> "Andrea Aime" <aaime@comune.modena.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.