Postgresql BIGINT to Double Precision division rounding issue

Question

I have a column of bigint values, e.g. 62639999999999993, and I want to divide it by 10^18 (precise). Seems very reasonable I should get 0.062639999999999993. Instead I keep getting 0.06263999999999999.

I've tried casting the BIGINT column into a precise numeric datatype before dividing it but I keep getting the wrong answer on something that seemingly should be very simple.

I also tried the SET extra_float_digits = 3 option to no avail.

Is it some sort of issue with my PC? This is a PostgreSQL 12.4, 64-bit database on my local desktop running Windows 10. CPU is an i7-10700k with 16GB of RAM. No overclock.

Does this answer your question? [Is floating point math broken?](https://stackoverflow.com/questions/588004/is-floating-point-math-broken) — Amadan, Apr 23 '21 at 02:26
In SQL, use [`DECIMAL`](https://www.postgresql.org/docs/9.1/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL) for arbitrary precision. — Amadan, Apr 23 '21 at 02:28
@Amadan, there isn't any difference between DECIMAL and NUMERIC in PGSQL (https://stackoverflow.com/a/33730552/155110). The post referenced also does not answer my question as I'm not using float (inexact) types, I'm using numeric which are precise in PGSQL up to 131072 digits before the decimal point; up to 16383 digits after the decimal point — Brad, Apr 23 '21 at 02:30
Indeed, you mentioned trying it, but not how you tried it. `SELECT v / CAST (10 AS DECIMAL(100, 50)) ^ 18 FROM t` gives `0.06263999999999999300000000000000000000000000000000`. If you write `10^18`, that is a float, not a decimal (or numeric; yes, they are identical). — Amadan, Apr 23 '21 at 02:40

score 3 · Answer 1 · answered Apr 23 '21 at 02:39

Your problem is that 10^18 is a double, so then dividing your int by that double leaves you with a double. For the calculation to be precise, it's the 10^18 you need to cast.

postgres=# select 62639999999999993::bigint / 10^18;
      ?column?
---------------------
 0.06263999999999999
(1 row)

postgres=# select 62639999999999993::bigint / 10^18::numeric;
        ?column?
------------------------
 0.06263999999999999300
(1 row)

Postgresql BIGINT to Double Precision division rounding issue

1 Answers1