Thread: floating point output
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?
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 adrian.klaver@aklaver.com
On Thu, Sep 5, 2019 at 6:14 AM Adrian Klaver <adrian.klaver@aklaver.com> 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
> On Sep 4, 2019, at 9:14 PM, Adrian Klaver <adrian.klaver@aklaver.com> 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 outputformat, particularly with respect to total digits presented (variable in a single select's output) and the droppingof 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.
> On Sep 5, 2019, at 7:32 AM, Rob Sargent <robjsargent@gmail.com> wrote: > > > >> On Sep 4, 2019, at 9:14 PM, Adrian Klaver <adrian.klaver@aklaver.com> 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 outputformat, particularly with respect to total digits presented (variable in a single select's output) and the droppingof 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 agregiousis 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 |
Rob Sargent <robjsargent@gmail.com> 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
On 2019-09-09 01:03:06 -0400, Tom Lane wrote:
> Rob Sargent <robjsargent@gmail.com> 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
| | | hjp@hjp.at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>