Re: PG8.3->10 migration data differences

On 9/10/18 6:43 AM, Csaba Ragasits wrote:
> I think I found the solution.
> 
> When I set this parameter on the pg10 client connection, the pg10 REAL 
> value format same as the pg83 value:

In your previous post you mentioned both 9.3 and 8.3.

Is 8.3 the actual version you are migrating from?

> 
> SET extra_float_digits = 0;
> 
> Interesting, because the default value is 0 in the postgresql.conf:
> #extra_float_digits = 0                 # min -15, max 3
> 
> Do you have any ideas how can I format the miliseconds too?

Assuming you are moving from 8.3 what does:

pg_controldata -D /your_83/data_dir

show for Date/time type storage: ?

vs

pg_controldata -D /your_10/data_dir

> 
> Thx,
> Csaba
> 
> 2018-09-10 12:00 GMT+02:00 Peter J. Holzer <hjp-pgsql@hjp.at 
> <mailto:hjp-pgsql@hjp.at>>:
> 
>     On 2018-09-10 11:57:42 +0300, Achilleas Mantzios wrote:
>     > On 10/09/2018 11:22, Csaba Ragasits wrote:
>     >     Hello,
>     > 
>     >     We would like to migrate our old databases to new postgres server,
>     >     with the simple backup-restore process. The process run fine
>     >     without errors but when we're  checking the source and the
>     >     migrated datas as field level, we found the following differences:
>     [...]
>     > 
>     >     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".
> 
>     (I would argue that the Pg9.3 output is better, since it represents the
>     same value in fewer digits, but always printing the minimum number of
>     digits necessary is surprisingly difficult.)
> 
>              hp
> 
>     -- 
>         _  | Peter J. Holzer    | we build much bigger, better disasters now
>     |_|_) |                    | because we have much more sophisticated
>     | |   | hjp@hjp.at <mailto:hjp@hjp.at>         | management tools.
>     __/   | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com