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

On Fri, Feb 25, 2011 at 9:21 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
>> right -- in understand how floating point works -- but are you are
>> saying that you are ok with the fact that (for example) a table with a
>> floating point unique key could dump and not restore? more
>> specifically, a binary dump would restore but a text dump would not.
>
> pg_dump takes measures against that (see extra_float_digits).
>
>> I think this is a problem with our implementation -- not all versions
>> of 2183.68 as outputted from the server are the same internally.
>
> It's an inherent property of float math.  Yes, we could set the default
> value of extra_float_digits high enough that distinct internal values
> always had distinct text representations, but trust me, you would not
> like it.

no I wouldn't, and the pg_dump extra_float_digits setting addresses my
primary concern. The client has a similar issue though -- suppose it
fetches a value from the server and updates it back -- which record
gets the update? You would get different results if the client was
using binary or text features of the protocol. Not saying this is
wrong or needs to be fixed, just pointing it out :-).

update foo set val=val + 1 where val = 2183.68;

merlin