Re: Unexpected result using floor() function

On Mon, Mar 14, 2016 at 11:54 AM, Pujol Mathieu
<mathieu(dot)pujol(at)realfusio(dot)com> wrote:
> Le 14/03/2016 15:29, Merlin Moncure a écrit :
>>
>> 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.
>>
>> merlin
>>
>>
> I think also that it is a float precision issue but the weird thing is that
> both calls without floor return 273. Maybe the display method make a
> rounding ?

Yeah. One of the trickiest bits about floating point numbers with
postgres (as well as many other implementations) is that the textual
representation does not necessarily equate to the internal one. This
can lead to some pretty weird situations. For example, data that was
valid when dumped could fail to restore on duplicate key. Anyone
doing work with floating point should be aware of this: it's compact
and fast but poor at representing precise numbers.

merlin