floating point output

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
lup
Reply | Threaded
Open this post in threaded view
|

floating point output

lup

I've found the description of floating point types (here), but I'm looking for the rationale of the output format, particularly with respect to total digits presented (variable in a single select's output) and the dropping of a trailing zero (to some implying a loss of precision).  Is the code my only guide here?


Reply | Threaded
Open this post in threaded view
|

Re: floating point output

Adrian Klaver-4
On 9/4/19 5:23 PM, Rob Sargent wrote:
> I've found the description of floating point types (here
> <https://www.postgresql.org/docs/10/datatype-numeric.html#DATATYPE-FLOAT>),
> but I'm looking for the rationale of the output format, particularly
> with respect to total digits presented (variable in a single select's
> output) and the dropping of a trailing zero (to some implying a loss of
> precision).  Is the code my only guide here?

Some examples would help explain your concerns.

Bottom line, if you want precision use numeric.

>
>


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: floating point output

Luca Ferrari-2
On Thu, Sep 5, 2019 at 6:14 AM Adrian Klaver <[hidden email]> wrote:
> Some examples would help explain your concerns.

I guess the problem is with extra_float_digits. If greater then zero,
the string representation of a real is at its minimum, that is only
significative digits are there:

restoredb=# set extra_float_digits to 1;
SET
restoredb=# select 2.001230::real;
 float4
---------
 2.00123
(1 row)


If lower than zero, the output switch to "precise" mode that is
extra_float_digits are subtracted from the number of available digits
for a number:

restoredb=# set extra_float_digits to -2;
SET
restoredb=# select 2.001230::real;
 float4
--------
  2.001
(1 row)

restoredb=# set extra_float_digits to -3;
SET
restoredb=# select 2.001230::real;
 float4
--------
      2
(1 row)


However, this has some more obscure to me behaviors when the value is
greater than 1:

restoredb=# set extra_float_digits to 1;
SET
restoredb=# select 2.1::real;
 float4
--------
    2.1
(1 row)

restoredb=# set extra_float_digits to 2;
SET
restoredb=# select 2.1::real;
  float4
-----------
 2.0999999
(1 row)


>
> Bottom line, if you want precision use numeric.

Yeah, totally agree. I would also add that numeric is a little more documented.

Luca


lup
Reply | Threaded
Open this post in threaded view
|

Re: floating point output

lup
In reply to this post by Adrian Klaver-4


> On Sep 4, 2019, at 9:14 PM, Adrian Klaver <[hidden email]> wrote:
>
>> On 9/4/19 5:23 PM, Rob Sargent wrote:
>> I've found the description of floating point types (here <https://www.postgresql.org/docs/10/datatype-numeric.html#DATATYPE-FLOAT>), but I'm looking for the rationale of the output format, particularly with respect to total digits presented (variable in a single select's output) and the dropping of a trailing zero (to some implying a loss of precision).  Is the code my only guide here?
>
> Some examples would help explain your concerns.
>
> Bottom line, if you want precision use numeric.
>
>
>
When at my desk I’ll give example. Actual precision is not the issue. Purely cosmetics.

lup
Reply | Threaded
Open this post in threaded view
|

Re: floating point output

lup


> On Sep 5, 2019, at 7:32 AM, Rob Sargent <[hidden email]> wrote:
>
>
>
>> On Sep 4, 2019, at 9:14 PM, Adrian Klaver <[hidden email]> wrote:
>>
>>> On 9/4/19 5:23 PM, Rob Sargent wrote:
>>> I've found the description of floating point types (here <https://www.postgresql.org/docs/10/datatype-numeric.html#DATATYPE-FLOAT>), but I'm looking for the rationale of the output format, particularly with respect to total digits presented (variable in a single select's output) and the dropping of a trailing zero (to some implying a loss of precision).  Is the code my only guide here?
>>
>> Some examples would help explain your concerns.
>>
>> Bottom line, if you want precision use numeric.
>>
>>
>>
> When at my desk I’ll give example. Actual precision is not the issue. Purely cosmetics.

Below I show a floating point column from a recent query.  

Here's my guess at what's happening in the formatting:
  1) scientific notation if smaller than 1.0e-04 (I’m not in the very-much-larger-than-one world)
  2) 15 digits after the most signicant 0
  3) remove trailing zeros

I may be alone in this but I find the presentation messy and that's unusual in the PostgreSQL world.  To me the most agregious is the truncation of the zeros.  Pick a number of digits and stick with it. One format too.

    1 |  8.8999911000089e-05 |
    2 |  0.00010999989000011 |
    3 |  0.00011999988000012 |
    4 | 0.000128999871000129 |
    5 | 0.000132499966875008 |
    6 | 0.000132999867000133 |
    7 | 0.000136922971597714 |
    8 | 0.000139499930250035 |
    9 | 0.000158809516247166 |
   10 | 0.000170294107629758 |
   11 |  0.00028999971000029 |
   12 | 0.000327999672000328 |
   13 | 0.000628999371000629 |
   14 |  0.00065999934000066 |
   15 | 0.000667999332000668 |
   16 | 0.000681999318000682 |
   17 | 0.000683999316000684 |
   18 | 0.000708999291000709 |
   19 | 0.000722999277000723 |
   20 | 0.000730999269000731 |
   21 |  0.00074999925000075 |
   22 | 0.000753999246000754 |
   23 | 0.000755999244000756 |
   24 | 0.000762999237000763 |
   25 | 0.000765999234000766 |
   26 | 0.000773999226000774 |
   27 | 0.000780999219000781 |
   28 | 0.000785999214000786 |
   29 | 0.000787999212000788 |
   30 | 0.000791999208000792 |
   31 | 0.000793999206000794 |
   32 | 0.000813999186000814 |
   33 | 0.000817999182000818 |
   34 | 0.000826999173000827 |
   35 | 0.000833999166000834 |
   36 | 0.000834999165000835 |
   37 | 0.000838999161000839 |
   38 | 0.000847999152000848 |
   39 | 0.000854999145000855 |
   40 | 0.000855999144000856 |
   41 | 0.000857999142000858 |
   42 |  0.00085999914000086 |
   43 | 0.000864999135000865 |
   44 |  0.00086999913000087 |
   45 | 0.000875999124000876 |
   46 | 0.000882999117000883 |
   47 | 0.000887999112000888 |
   48 | 0.000887999112000888 |
   49 | 0.000904999095000905 |
   50 | 0.000910999089000911 |
   51 | 0.000912999087000913 |
   52 | 0.000912999087000913 |
   53 | 0.000914999085000915 |
   54 | 0.000921999078000922 |
   55 | 0.000922999077000923 |
   56 | 0.000938999061000939 |
   57 |  0.00094999905000095 |
   58 | 0.000967999032000968 |
   59 | 0.000971999028000972 |
   60 | 0.000982999017000983 |
   61 | 0.000995999004000996 |
   62 |    0.001000998999001 |
   63 |    0.001003998996001 |
   64 |    0.001003998996001 |
   65 |  0.00101099898900101 |
   66 |  0.00101199898800101 |
   67 |  0.00101599898400102 |
   68 |  0.00101899898100102 |
   69 |  0.00102199897800102 |
   70 |  0.00102599897400103 |
   71 |  0.00103099896900103 |
   72 |  0.00104999895000105 |
   73 |  0.00106899893100107 |
   74 |  0.00107499892500108 |
   75 |  0.00107599892400108 |
   76 |  0.00108499891500109 |
   77 |  0.00108699891300109 |
   78 |  0.00108799891200109 |
   79 |   0.0011039988960011 |
   80 |  0.00110799889200111 |
   81 |  0.00111099888900111 |
   82 |  0.00111299888700111 |
   83 |  0.00111599888400112 |
   84 |  0.00111699888300112 |
   85 |  0.00111999888000112 |
   86 |  0.00111999888000112 |
   87 |  0.00112099887900112 |
   88 |  0.00116599883400117 |
   89 |  0.00117461448106578 |
   90 |   0.0011788180746529 |
   91 |  0.00118099881900118 |
   92 |   0.0011839994080003 |
   93 |  0.00118827261924794 |
   94 |  0.00118899881100119 |
   95 |   0.0012049987950012 |
   96 |  0.00120622217754733 |
   97 |  0.00120999879000121 |
   98 |  0.00121409079871902 |
   99 |  0.00121481807138018 |
  100 |  0.00122618178102479 |
  101 |  0.00122699877300123 |
  102 |  0.00122899877100123 |
  103 |  0.00123999876000124 |
  104 |  0.00125999874000126 |
  105 |  0.00127766866245761 |
  106 |  0.00128099935950032 |
  107 |  0.00129099870900129 |
  108 |  0.00129199870800129 |
  109 |  0.00129299870700129 |
  110 |  0.00129924967518758 |
  111 |   0.0013019986980013 |
  112 |   0.0013019986980013 |
  113 |  0.00130449934775033 |
  114 |  0.00133099866900133 |
  115 |  0.00133106666886731 |
  116 |  0.00135249932375034 |
  117 |  0.00137999988500001 |
  118 |  0.00142799857200143 |
  119 |  0.00143799856200144 |
  120 |  0.00144399855600144 |
  121 |   0.0014563623123979 |
  122 |  0.00146199853800146 |
  123 |  0.00148299851700148 |
  124 |   0.0014969985030015 |
  125 |  0.00151499848500151 |
  126 |  0.00151599848400152 |
  127 |  0.00154699845300155 |
  128 |  0.00154809076835538 |
  129 |  0.00156599843400157 |
  130 |  0.00156999843000157 |
  131 |  0.00157899842100158 |
  132 |  0.00163499836500163 |
  133 |  0.00165199834800165 |
  134 |  0.00165299917350041 |
  135 |  0.00167799832200168 |
  136 |  0.00168699831300169 |
  137 |  0.00171599914200043 |
  138 |  0.00172849913575043 |
  139 |  0.00174599825400175 |
  140 |  0.00199399800600199 |
  141 |   0.0028989971010029 |
  142 |  0.00417999582000418 |
  143 |  0.00436199563800436 |



Reply | Threaded
Open this post in threaded view
|

Re: floating point output

Tom Lane-2
Rob Sargent <[hidden email]> writes:
> Below I show a floating point column from a recent query.  

> Here's my guess at what's happening in the formatting:
>   1) scientific notation if smaller than 1.0e-04 (I’m not in the very-much-larger-than-one world)
>   2) 15 digits after the most signicant 0
>   3) remove trailing zeros

> I may be alone in this but I find the presentation messy and that's
> unusual in the PostgreSQL world.

In released versions of Postgres, float8out basically devolves to
sprintf with "%.15g" format, and the above is what POSIX says that
should do.

As of v12, we'll use the "Ryu" output algorithm, which adjusts
the number of displayed digits to ensure that the value will
re-load exactly by default.  I'm afraid that will probably make
your complaint about variable numbers of digits worse not better.
It's unlikely anybody will entertain an argument to undo it,
though, because (a) Ryu is faster than the old code, and (b) it's
hard to argue against ensuring that output/input preserves values.

You might consider using type "numeric" if you find truncation
of low-order zeroes undesirable.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: floating point output

Peter J. Holzer
On 2019-09-09 01:03:06 -0400, Tom Lane wrote:

> Rob Sargent <[hidden email]> writes:
> > Below I show a floating point column from a recent query.  
>
> > Here's my guess at what's happening in the formatting:
> >   1) scientific notation if smaller than 1.0e-04 (I’m not in the very-much-larger-than-one world)
> >   2) 15 digits after the most signicant 0
> >   3) remove trailing zeros
>
> > I may be alone in this but I find the presentation messy and that's
> > unusual in the PostgreSQL world.
>
> In released versions of Postgres, float8out basically devolves to
> sprintf with "%.15g" format, and the above is what POSIX says that
> should do.
>
> As of v12, we'll use the "Ryu" output algorithm, which adjusts
> the number of displayed digits to ensure that the value will
> re-load exactly by default.
Cool. I looked into that some time ago, but never got around to
implement it.

> I'm afraid that will probably make your complaint about variable
> numbers of digits worse not better. It's unlikely anybody will
> entertain an argument to undo it, though, because (a) Ryu is faster
> than the old code, and (b) it's hard to argue against ensuring that
> output/input preserves values.

I agree in general, but I wonder whether it would be worthwhile to add
display formatting options to psql (something like "COLUMN foo FORMAT ..."
in Oracle's sqlplus), so that the user can decide to display a specific
column (or maybe all float numbers) as (for example) "%8.3f" or ".6e".

This is of course already possible by using to_char in the query (e.g.
to_char(f, '9999.999') or to_char(f, '9.999999EEEE')) but that is a bit
cumbersome.

        hp

--
   _  | Peter J. Holzer    | we build much bigger, better disasters now
|_|_) |                    | because we have much more sophisticated
| |   | [hidden email]         | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>

signature.asc (849 bytes) Download Attachment