Perhaps there are platform differences, since the version I am using was built with Microsoft Visual Studio:
SELECT version() PostgreSQL 8.3.7, compiled by Visual C++ build 1400 The v8.1.5 server I'm using was compiled with GCC: PostgreSQL 8.1.5 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special) I would like to understand how this difference affects rounding in more detail so that I may explain caveats to customers. Thanks, Robert On Mon, Apr 20, 2009 at 5:36 PM, Adrian Klaver <[email protected]> wrote: > 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 > > [email protected] > > > 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 > [email protected] >
