On Thu, Feb 24, 2011 at 1:01 PM, Nathan M. Davalos <n.dava...@sharedmarketing.com> wrote: > I ran into something interesting with using trunc() and different data > types: > > The following is a simplified from the statement we’re using and produces > the same results: > > select trunc( ((cast(2183.68 as numeric) - cast(1 as numeric)) )*100) /100 > > yields 2184.68
the root issue I think here is that the string version of the double precision math is approximated: postgres=# create table v as select floor(2183.68::float8 * 100) as v; postgres=# select * from v; v -------- 218367 (1 row) postgres=# select floor(v) from v; floor -------- 218367 postgres=# insert into v select 218368; INSERT 0 1 (1 row) postgres=# select distinct v from v; v -------- 218368 218368 (2 rows) As you can see, even though the string versions are the same, the internal representation is different. You could dump the data and restore it and get different results. Also the text/binary protocols would send different data to the client. I don't know if this is a bug in postgresql floating point implementation or not: i think the backend would either have to print 218367.999999999999999ish number or spend the time to look for these cases and round them internally. Floating point is a headache :-). merlin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
