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