Re: PG8.3->10 migration data differences

"Peter J. Holzer" <hjp-pgsql(at)hjp(dot)at> writes:
> On 2018-09-10 11:57:42 +0300, Achilleas Mantzios wrote:
>> On 10/09/2018 11:22, Csaba Ragasits wrote:
>>> Field type: REAL
>>> pg93: 2.2
>>> pg10: 2.20000005

>> Those have to do with rounding. Precision for real is 6 decimal
>> digits. Your difference is on the 8-th digit.

> Nitpick: Precision is 24 binary digits which is *about* 6 decimal
> digits. The number which is actually stored (on both pg93 and pg10) is
> actually 2.2000000476837158203125 (1.00011001100110011001101 * 2^1 in
> binary). Apparently Pg9.3 rounds this to "2.2" on output while Pg10
> prints the more precise (but still not exact) "2.20000005".

Well, more specifically:

regression=# set extra_float_digits to 2;
SET
regression=# select 2.2::real;
float4
--------
2.2
(1 row)

regression=# set extra_float_digits to 3;
SET
regression=# select 2.2::real;
float4
------------
2.20000005
(1 row)

8.3 did not let you set extra_float_digits as high as 3, so you couldn't
get the "2.20000005" result there, but given that and a correct
implementation of sprintf you would have.

I surmise that the OP is comparing the output of pg_dump in the two
versions. pg_dump always sets extra_float_digits to the maximum the
server will allow, and the reason is to ensure that the dumped value will
reload as the same binary bit pattern (again, assuming correct float I/O
functionality in libc). We used to think that 2 extra digits beyond the
nominal precision was enough to guarantee that, but there are cases where
you need 3, so it got changed.

regards, tom lane