From: | "Peter J(dot) Holzer" <hjp-pgsql(at)hjp(dot)at> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Exponentiation confusion |
Date: | 2022-10-13 17:05:27 |
Message-ID: | 20221013170527.3k3yeh6t2sjdlueg@hjp.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
On 2022-10-13 09:20:51 -0700, Adrian Klaver wrote:
> In trying to answer an SO question I ran across this:
>
> Postgres version 14.5
>
Same for 11.17. So it's been like that for some time, maybe forever.
> select power(10, -18);
> power
> -------
> 1e-18
> (1 row)
>
> select power(10, -18::numeric);
> power
> --------------------
> 0.0000000000000000
>
>
> Why is the cast throwing off the result?
It seems that the number of decimals depends only on the first argument:
hjp=> select power(10::numeric, -2::numeric);
╔════════════════════╗
║ power ║
╟────────────────────╢
║ 0.0100000000000000 ║
╚════════════════════╝
(1 row)
hjp=> select power(10::numeric, -16::numeric);
╔════════════════════╗
║ power ║
╟────────────────────╢
║ 0.0000000000000001 ║
╚════════════════════╝
(1 row)
hjp=> select power(10::numeric, -18::numeric);
╔════════════════════╗
║ power ║
╟────────────────────╢
║ 0.0000000000000000 ║
╚════════════════════╝
(1 row)
hjp=> select power(10::numeric, 18::numeric);
╔══════════════════════════════════════╗
║ power ║
╟──────────────────────────────────────╢
║ 1000000000000000000.0000000000000000 ║
╚══════════════════════════════════════╝
(1 row)
hjp=> select power(10::numeric(32,30), 18::numeric);
╔════════════════════════════════════════════════════╗
║ power ║
╟────────────────────────────────────────────────────╢
║ 1000000000000000000.000000000000000000000000000000 ║
╚════════════════════════════════════════════════════╝
(1 row)
hjp=> select power(10::numeric(32,30), -16::numeric);
╔══════════════════════════════════╗
║ power ║
╟──────────────────────────────────╢
║ 0.000000000000000100000000000000 ║
╚══════════════════════════════════╝
(1 row)
So the number of decimals by default isn't sufficient to represent
10^-18. You have to explicitely increase it.
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2022-10-13 17:16:58 | Re: Exponentiation confusion |
Previous Message | Ron | 2022-10-13 16:57:20 | Re: PGAdmin 4 fatal error after enabling the import logging |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2022-10-13 17:16:58 | Re: Exponentiation confusion |
Previous Message | Andres Freund | 2022-10-13 17:04:56 | Re: Summary function for pg_buffercache |