Re: Unexpected result using floor() function

On Mon, Mar 14, 2016 at 9:29 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> On Mon, Mar 14, 2016 at 9:21 AM, Pujol Mathieu
> <mathieu(dot)pujol(at)realfusio(dot)com> wrote:
>> Hi
>> I reproduce your bug using "PostgreSQL 9.3.6, compiled by Visual C++ build
>> 1600, 64-bit" on Windows 8
>> I also notice that SELECT floor(4.725 * power(10, 2)::numeric + 0.5) work as
>> expected
>
> I don't think this is a bug -- just peculiarities of floating point math.

To be more clear: the problem is neither with pow() or floor(). The issue is:

postgres=# SELECT (4.725 * 100.0 + 0.5 ) = 473;
?column?
──────────
t
(1 row)

Time: 0.387 ms
postgres=# SELECT (4.725 * 100.0::FLOAT8 + 0.5 ) = 473;
?column?
──────────
f

The workaround is to use fixed point or build in epsilon tolerances in
any case where you are using decimal numbers and expect precise
results.

merlin