Re: Exponentiation confusion

> On 13/10/2022 18:20 CEST Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> 
> In trying to answer an SO question I ran across this:
> 
> Postgres version 14.5
> 
> select 10^(-1 * 18);
>   ?column?
> ----------
>      1e-18
> 
> select 10^(-1 * 18::numeric);
>        ?column?
> --------------------
>   0.0000000000000000
> 
> 
> Same for power:
> 
> 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?

power has two overloads: https://www.postgresql.org/docs/14/functions-math.html#id-1.5.8.9.6.2.2.19.1.1.1

Calling power(numeric, numeric) is what I expect in that case instead of
downcasting the exponent argument to double precision, thus losing precision.

select
  pg_typeof(power(10, -18)),
  pg_typeof(power(10, -18::numeric));

    pg_typeof     | pg_typeof 
------------------+-----------
 double precision | numeric
(1 row)

Determining the right function is described in https://www.postgresql.org/docs/14/typeconv-func.html

--
Erik