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)?

Answer 1

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.

more detail can refer by What Every Programmer Should Know About Floating-Point Arithmetic

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.

sqlfiddle