Re: Exponentiation confusion

> On 13/10/2022 18:20 CEST Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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