5 ms·

Postgres and SQL: postgres=# select 9999999999999999.0 - 9999999999999998.0 as result; result -------- 1.0

• roessland 3y ago
  The default PG type, `numeric`, has almost arbitary precision. select pg_typeof(9999999999999999.0); -- numeric select 9999999999999999.0::double precision - 9999999999999998.0::double precision; -- 2 More interesting perhaps, is mixing up `real` (aka float32) with `numeric`: select 9999999999999999.0::real - 9999999999999998.0; -- 272564226 (?! can anyone explain?) select 9999999999999999.0::real; -- 10000000300000000 Wat
  • zokier 3y ago
    #include<stdio.h> int main(void) { long a = (float)9999999999999999; long b = 9999999999999998; printf("%ld - %ld = %ld\n", a, b, a-b); } produces 10000000272564224 - 9999999999999998 = 272564226
  • moefh 3y ago
    The `real` type (float32) only has 24 bits of precision. So converting `9999999999999999.0` or `10000000000000000` or even `10000000300000000` yields the 32-bit float `10000000272564224`. For some reason Postgres prints it as `10000000300000000`. It uses a heuristic to print a "pretty" number that converts to the actual stored value, and it's not smart enough to give `10000000000000000`. Some heuristic like this is needed so something like `0.3` doesn't print the actual stored value of `0.300000011920928955078125`, which would be confusing. You can check all this here: https://www.h-schmidt.net/FloatConverter/IEEE754.html https://www.h-schmidt.net/FloatConverter/IEEE754.html