Gilleain Torrance <Gilleain.Torrance@alfasystems.com> writes:
> When storing a double in Postgres, it looks like under specific circumstances it can get rounded incorrectly:
> select round(cast(float8 '42258656681.38498' as numeric), 2), round(numeric '42258656681.38498', 2);
> which returns either 42258656681.38 or 42258656681.39 depending on whether it is float8 or not.
I think this is behaving as expected. float8-to-numeric conversion
rounds the float8 to 15 (DBL_DIG) decimal places, since that's as much
precision as you're guaranteed to have. So what comes out of the cast
is
regression=# select cast(float8 '42258656681.38498' as numeric);
numeric
-----------------
42258656681.385
(1 row)
and then that rounds up to 42258656681.39. In the other case you
have an exact numeric value of 42258656681.38498, so it's unsurprisingly
rounded to 42258656681.38.
You could quibble about whether numeric round() ought to apply
round-up or round-to-nearest-even when dealing with exact halfway
cases. If it did the latter, this particular case would match up,
but other cases would not, so I don't think it's a helpful proposal
for this issue.
The other thing we could conceivably do is ask sprintf for more digits.
But since those extra digit(s) aren't fully precise, I'm afraid that
would likewise introduce as many oddities as it fixes. Still, it's
somewhat interesting to wonder whether applying the Ryu algorithm
would produce better or worse results on average.
regards, tom lane