Why loss of precision when casting float4 integer to numeric in PostgreSQL?

Question

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.

Answer 1

Let's look at the implementation

melkij=> select target_type.typname, pg_proc.prosrc from pg_cast join pg_type target_type on target_type.oid = casttarget join pg_proc on pg_proc.oid = castfunc where castsource in (select oid from pg_type where typname = 'float4');
 typname | prosrc 
---------+----------------
 int8 | ftoi8
 int2 | ftoi2
 int4 | ftoi4
 float8 | ftod
 numeric | float4_numeric
(5 rows)

These type casts are implemented by functions:

• ftod for float4 -> float8 conversion, nothing surprising, direct type casting within the C language data types. More formally, it may differ due to the compiler implementation
• float4_numeric for the conversion of float4 to numeric is somewhat more unexpected and is implemented by converting to a string representation with hardcoded FLT_DIG precision. FLT_DIG is a standard compile-time constant defined as the number of decimal digits of precision for the float data type. More related to this question, probably. FLT_DIG is commonly 6.

It is interesting to mention here that the textual display of float4 values ​​is influenced by the extra_float_digits runtime configuration parameter. By default extra_float_digits is 1, so this gives FLT_DIG + 1 = 7 in most cases. But float4_numeric does not take into account extra_float_digits.

It is unlikely to be considered as a bug, since it does not violates the promise to have at least 6 decimal digits.

• Mike Sherrill 'Cat Recall': While it might not be considered a bug, Oracle, SQL Server, and SQLite have the OP's expected behavior. See this fiddle. I edited the OP's query to use standard SQL syntax.