Re: PostgresSQL 10 | Driver 42.2.5 | Float Conversion Issue

Hi,

On 2020-10-20 09:50:46 -0700, David G. Johnston wrote:
> So, confirming the following query result on head.
>
> select '1234567'::float4::numeric; -- 1234570

The reason this happens is that the float[48]->numeric casts are
implemented by converting the float value to string using
FLT_DIG/DBL_DIG, and then reading that back as a string. Unfortunately
that's pretty bogus - FLT_DIG / DBL_DIG are extremely pessimistic; as it
turns out rounding a few digits beyond the decimal point isn't the same
as before the decimal point.

Unfortunately this can't easily be changed, as any such change would
e.g. break indexes that include float->numeric casts. Including across
major versions, due to pg_upgrade.

For text this is not a problem anymore since v12 (with default settings,
i.e. extra_float_digits > 0), because we now use ryu for those
conversions. But we can't depend extra_float_digits in the case of
float->numeric casts, because it's an immutable function :/

Thanks to Andrew Gierth for some pointers.

Some discussion links:
https://www.postgresql.org/message-id/874lagotif.fsf%40news-spur.riddles.org.uk
https://postgr.es/m/26443.1571712071%40sss.pgh.pa.us
https://www.postgresql.org/message-id/87y37qmwee.fsf%40news-spur.riddles.org.uk

Greetings,

Andres Freund