Float32, Float64 and BFloat16 ​

Floating-point types represent approximate numeric values. They can cover a wide range, but most decimal fractions cannot be represented exactly. Use Decimal when decimal rounding differences are unacceptable.

Syntax ​

Float32
Float64
BFloat16

Using floating-point numbers ​

Converting to a narrower type can change the value. BFloat16 truncates the low Float32 bits; it does not round to the nearest representable value.

NaN and Inf ​

Explicit positive and negative infinity are accepted. NaN is normalized to SQL NULL. A finite input that overflows its declared floating-point type raises NUMERIC_OVERFLOW.

Top-level columns accept NULL unless declared NOT NULL. Ordinary numeric comparisons, arithmetic and aggregates can consume these types; each function documents how it handles nonfinite results.

BFloat16 ​

BFloat16 represents approximate numbers with seven fraction bits. Conversion truncates the low Float32 bits. Use it when reduced precision is acceptable.

SELECT to_type_name(CAST(1.5 AS BFloat16)), CAST(1.5 AS BFloat16)
FORMAT csv;

"BFloat16",1.5

Examples ​

Precision loss in Float32 ​

The integer 16777217 is exactly representable in Float64 but lies between two Float32 values:

SELECT CAST(16777217 AS Float32),
       CAST(16777217 AS Float64)
FORMAT csv;

16777216,16777217.0

NaN becomes NULL ​

SELECT CAST('nan' AS Float32) IS NULL,
       CAST('nan' AS Float64) IS NULL
FORMAT csv;

1,1

Explicit numeric conversion ​

SELECT to_float64(42), to_type_name(to_float32(42))
FORMAT csv;

42.0,"Float32"