Re: round behavior differs between 8.1.5 and 8.3.7

On Monday 20 April 2009 5:20:47 pm Adrian Klaver wrote:
> On Monday 20 April 2009 2:21:31 pm Robert Morton wrote:
> > Howdy,
> > None of the discussions about rounding so far have addressed what appears
> > to be a significant change that occurred at some point between PostgreSQL
> > v8.1.5 and v8.3.7.  Can someone explain to me the difference between the
> > two resultsets below?  Additionally I would like to understand what
> > option will consistently provide a banker's-round in v8.3.7, if possible.
> >
> > Here is the query, followed by the resultset for each version:
> >  SELECT
> >    round(3.5::numeric) as "rn3.5",
> >    round(3.5::float8) as "rf3.5",
> >    dround(3.5::numeric) as "dn3.5",
> >    dround(3.5::float8) as "df3.5",
> >    CAST(3.5::numeric as INTEGER) as "cn3.5",
> >    CAST(3.5::float8 as INTEGER) as "cf3.5",
> >    round(4.5::numeric) as "rn4.5",
> >    round(4.5::float8) as "rf4.5",
> >    dround(4.5::numeric) as "dn4.5",
> >    dround(4.5::float8) as "df4.5",
> >    CAST(4.5::numeric as INTEGER) as "cn4.5",
> >    CAST(4.5::float8 as INTEGER) as "cf4.5"
> >
> > v8.1.5:
> > rn3.5,rf3.5,dn3.5,df3.5,cn3.5,cf3.5,rn4.5,rf4.5,dn4.5,df4.5,cn4.5,cf4.5
> > 4,4,4,4,4,4,5,4,4,4,5,4
> > v8.3.7:
> > rn3.5,rf3.5,dn3.5,df3.5,cn3.5,cf3.5,rn4.5,rf4.5,dn4.5,df4.5,cn4.5,cf4.5
> > 4,3,3,3,4,3,5,4,4,4,5,4
> >
> >
> > Thanks,
> > Robert
>
> Well it wasn't 8.3.5 :) because:
>
> postgres=# SELECT version();
>                                             version
> ---------------------------------------------------------------------------
>--------------------- PostgreSQL 8.3.5 on i686-pc-linux-gnu, compiled by GCC
> gcc (GCC) 4.2.4 (Ubuntu 4.2.4-1ubuntu3)
> (1 row)
>
>
> postgres=# SELECT
> postgres-# round(3.5::numeric) as "rn3.5",
> postgres-# round(3.5::float8) as "rf3.5",
> postgres-# dround(3.5::numeric) as "dn3.5",
> postgres-# dround(3.5::float8) as "df3.5",
> postgres-# CAST(3.5::numeric as INTEGER) as "cn3.5",
> postgres-# CAST(3.5::float8 as INTEGER) as "cf3.5",
> postgres-# round(4.5::numeric) as "rn4.5",
> postgres-# round(4.5::float8) as "rf4.5",
> postgres-# dround(4.5::numeric) as "dn4.5",
> postgres-# dround(4.5::float8) as "df4.5",
> postgres-# CAST(4.5::numeric as INTEGER) as "cn4.5",
> postgres-# CAST(4.5::float8 as INTEGER) as "cf4.5"
> postgres-# ;
>  rn3.5 | rf3.5 | dn3.5 | df3.5 | cn3.5 | cf3.5 | rn4.5 | rf4.5 | dn4.5 |
> df4.5 | cn4.5 | cf4.5
> -------+-------+-------+-------+-------+-------+-------+-------+-------+---
>----+-------+------- 4 |     4 |     4 |     4 |     4 |     4 |     5 |
> 4 |     4 |     4 | 5 |     4
> (1 row)
>
>
>
>
> --
> Adrian Klaver
> aklaver@comcast.net

I upgraded to 8.3.7 and I still don't see what you see. There must be something
else going here.

postgres=# SELECT version();
                                           version
------------------------------------------------------------------------------------------------
 PostgreSQL 8.3.7 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.2.4 (Ubuntu

4.2.4-1ubuntu3)
(1 row)

postgres=# SELECT

round(3.5::numeric) as "rn3.5",
round(3.5::float8) as "rf3.5",
dround(3.5::numeric) as "dn3.5",
dround(3.5::float8) as "df3.5",
CAST(3.5::numeric as INTEGER) as "cn3.5",
CAST(3.5::float8 as INTEGER) as "cf3.5",
round(4.5::numeric) as "rn4.5",
round(4.5::float8) as "rf4.5",
dround(4.5::numeric) as "dn4.5",
dround(4.5::float8) as "df4.5",
CAST(4.5::numeric as INTEGER) as "cn4.5",
CAST(4.5::float8 as INTEGER) as "cf4.5"

;
 rn3.5 | rf3.5 | dn3.5 | df3.5 | cn3.5 | cf3.5 | rn4.5 | rf4.5 | dn4.5 | df4.5 |
cn4.5 | cf4.5
-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------
    4 |     4 |     4 |     4 |     4 |     4 |     5 |     4 |     4 |     4 |
5 |     4
(1 row)


--
Adrian Klaver
aklaver@comcast.net