Re: Zero-padding and zero-masking fixes for to_char(float) - Mailing list pgsql-hackers

The issue of significant (decimal) digits to and from floating point 
representation is a complex one.

What 'significant' means may depend upon the intent.

There are (at least) two different tests which may need to be used.

* How many digits can be stored and then accurately returned?
or
* How many decimal digits are needed to recreate a floating point value?  Or in longer form, if you have a floating
pointvalue, you may want to 
 
print it in decimal form and then later scan that to recreate the exact 
bit pattern from the original floating point value.  How many decimal 
digits do you need?

The first question produces a smaller number of digits then the second one!

The idea of zero padding is, IMO, a bad idea all together.  It makes 
people feel better, but it adds inaccuracy.  I've lost this 
interpretation so many times now that I only mention it for the real 
number geeks out there.

Postgresql seems to be using the first interpretation and reporting 
fewer digits.  I've noticed this with pg_dump.  That a dump and restore 
of floating point values does not produce the same floating point 
values.  To me, that is inexcusable.  Using the -Fc format, real values 
are preserved.  I have a large database of security prices.  I want 
accuracy above all.

I do not have time right now to produce the needed evidence for all 
these cases of floating point values.  If there is interest I can 
produce this in a day or so.

Jeff Anton

BTW:  This is my first posting to this list.  I should introduce myself.
I'm Jeff Anton.  I was the first Postgres project lead programmer 
working for Michael Stonebraker at U.C. Berkeley a very long time ago.
The first version was never released.  I've since worked for several db 
companies.


On 03/24/15 06:47, Noah Misch wrote:
> On Sun, Mar 22, 2015 at 10:53:12PM -0400, Bruce Momjian wrote:
>> On Sun, Mar 22, 2015 at 04:41:19PM -0400, Noah Misch wrote:
>>> On Wed, Mar 18, 2015 at 05:52:44PM -0400, Bruce Momjian wrote:
>>>> This "junk" digit zeroing matches the Oracle behavior:
>>>>
>>>>     SELECT to_char(1.123456789123456789123456789d, '9.9999999999999999999999999999999999999') as x from dual;
>>>>     ------
>>>>     1.1234567891234568000000000000000000000
>>>>
>>>> Our output with the patch would be:
>>>>
>>>>     SELECT to_char(float8 '1.123456789123456789123456789', '9.9999999999999999999999999999999999999');
>>>>     ------
>>>>     1.1234567891234500000000000000000000000
>
>>> These outputs show Oracle treating 17 digits as significant while PostgreSQL
>>> treats 15 digits as significant.  Should we match Oracle in this respect while
>>> we're breaking compatibility anyway?  I tend to think yes.
>>
>> Uh, I am hesistant to adjust our precision to match Oracle as I don't
>> know what they are using internally.
>
> http://sqlfiddle.com/#!4/8b4cf/5 strongly implies 17 significant digits for
> float8 and 9 digits for float4.
>
>