Why loss of precision when casting float4 integer to numeric in PostgreSQL?
3
votes
1
answer
56
views
I've encountered what seems like an unexpected precision loss when casting from
float4
directly to numeric
in PostgreSQL:
select 1079414::float4,
(1079414::float4)::numeric(15,2) as float4_to_numeric, -- loses precision
((1079414::float4)::float8)::numeric(15,2) as float4_to_float8_to_numeric;
This produces:
float4 | float4_to_numeric | float4_to_float8_to_numeric
------------+-------------------+----------------------------
1,079,414 | 1,079,410 | 1,079,414
The direct conversion from float4
to numeric
unexpectedly yields 1,079,410
instead of the expected 1,079,414
.
The PostgreSQL docs state : "On all currently supported platforms, the real
type has a range of around 1E-37 to 1E+37 with a precision of at least 6 decimal digits." and I realize that this is 7 decimal digits.
However, Wikipedia notes : "Any integer with absolute value less than 2^24 can be exactly represented in the single-precision format"
Since 1,079,414 is much less than 2^24 (16,777,216), I would expect it to be represented exactly.
Interestingly, converting to float8
first and then to numeric
preserves the full value, suggesting PostgreSQL is storing the full precision but somehow losing it during direct conversion to numeric
.
Is this behavior a bug or is there some underlying reason for this behavior? What's happening during the type conversion that causes this precision loss?
I see there is a similar question already, but that example uses decimals while this is about an integer less than 2^24.
Asked by ChristopherTull
(303 rep)
Mar 27, 2025, 07:36 PM
Last activity: Mar 27, 2025, 09:20 PM
Last activity: Mar 27, 2025, 09:20 PM