From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Csaba Ragasits <ragasits(dot)csaba(at)gmail(dot)com>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: PG8.3->10 migration data differences |
Date: | 2018-09-10 14:21:25 |
Message-ID: | 07333286-2480-8750-c73d-d82e061b2bca@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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:
>
> 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?
select version();
version
------------------------------------------------------------------------------------
PostgreSQL 10.5 on x86_64-pc-linux-gnu, compiled by gcc (SUSE Linux)
4.8.5, 64-bit
test=# select '09/10/18 07:10:25.100'::timestamp;
timestamp
-----------------------
2018-09-10 07:10:25.1
(1 row)
test=# select '09/10/18 07:10:25.111'::timestamp;
timestamp
-------------------------
2018-09-10 07:10:25.111
Milliseconds will display if they are significant.
If you want to format the output:
test=# select to_char('09/10/18 07:10:25.100'::timestamp, 'MM/DD/YY
HH:MI:SS.MS');
to_char
-----------------------
09/10/18 07:10:25.100
(1 row)
>
> Thx,
> Csaba
>
> 2018-09-10 12:00 GMT+02:00 Peter J. Holzer <hjp-pgsql(at)hjp(dot)at
> <mailto:hjp-pgsql(at)hjp(dot)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(at)hjp(dot)at <mailto:hjp(at)hjp(dot)at> | management tools.
> __/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>
>
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | David Steele | 2018-09-10 14:33:55 | Re: Volume partitioning (was Re: pgbackrest when data/base is symlinked to another volume) |
Previous Message | Adrian Klaver | 2018-09-10 14:09:51 | Re: PG8.3->10 migration data differences |