Sample Header Ad - 728x90

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