Re: Function trunc() behaves in unexpected manner with different data types

On Thu, Feb 24, 2011 at 1:01 PM, Nathan M. Davalos
<n(dot)davalos(at)sharedmarketing(dot)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