Postgresql wrong casting from float8 to decimal(24,8)

Question

using Postgres 12.4 I found some inconsistencies rounding between float8 to decimal(24,8)

By doing

select 
  29314.630053404966::float8::decimal(24,8) as num1,
  29314.630053404966::decimal(24,8) as num2

the return is:

num1: 29314.63005341 -> wrong
num2: 29314.63005340 -> correct

As you can see the rounding does not work properly

Any way to cast correctly from float8 to decimal(24,8)?

I'm not sure why you think this is wrong. `float8` is a binary type, not decimal. Rounding almost always introduces a rounding error, and rounding twice can add two rounding errors. — MSalters, Mar 03 '22 at 15:54

D-Shih · Answer 1 · 2022-03-03T15:58:16.300

I would always use decimal instead of using float8 if I wanted to store decimal point values.

why? there is an example

SELECT 0.1::decimal(24,8) + 0.2::decimal(24,8); --0.30000000

SELECT 0.1::float8 + 0.2::float8; --0.30000000000000004

we can see the 0.1::float8 + 0.2::float8 will get the wrong number.

Any way to cast correctly from float8 to decimal(24,8)?

There is a way that might help you do that

we can try to cast the value as a string first, Before Casting float8 to decimal(24,8).

select 
  29314.630053404966::float8::varchar(40)::decimal(24,8) as num1,
  29314.630053404966::decimal(24,8) as num2

This way might work, but that will cause performance issues.

1 Answers1