|
Thanks for the explanation, Tom. 🙂
From: Tom Lane <tgl@xxxxxxxxxxxxx>
Sent: Thursday, October 28, 2021 11:04 AM To: Hilbert, Karin <ioh1@xxxxxxx> Cc: pgsql-general@xxxxxxxxxxxxxxxxxxxx <pgsql-general@xxxxxxxxxxxxxxxxxxxx> Subject: Re: How to Resolve Data Being Truncated or Rounded Up During PostgreSQL Migration from v9.623 to v12.8? "Hilbert, Karin" <ioh1@xxxxxxx> writes:
> [ PG12 displays float values a tad differently from 9.6 ] This is not a bug; we just changed the behavior of the "extra_float_digits" display option, so that it's less likely to print garbage digits. A float4 value only has about six decimal digits of precision to begin with, and those extra digits you are seeing in the 9.6 dump are basically fictional. pg_dump does "set extra_float_digits to 3", which used to be necessary to get reproducible results when dumping from old servers. That has this effect on 9.6: regression=# select '53809.6'::float4; float4 --------- 53809.6 (1 row) regression=# set extra_float_digits to 3; SET regression=# select '53809.6'::float4; float4 ------------ 53809.6016 (1 row) But it has no effect on new servers, because the six digits are already enough to recreate the float4 value exactly. The "016" added by the old server is basically roundoff noise. The reason for extra_float_digits is that with the old output algorithm, there were corner cases where we had to print more than six digits to ensure the value reloads exactly. The new algorithm automatically prints the minimum number of digits needed to ensure exact reload. All the same comments apply to float8, of course, with a different number of digits. regards, tom lane |
