Re: Exponentiation confusion

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!"