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@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"