toFloat32() in ClickHouse: Convert with Confidence

  

In ClickHouse, data type conversion is essential when preparing data for storage, computation, or filtering. The toFloat32() function allows you to explicitly convert values to a 32-bit floating-point number. Understanding how it behaves, including how it handles rounding, precision loss, and type casting is crucial when working with numeric and string data in ClickHouse.

 

This post explains how toFloat32() works, when and why to use it, and some key examples and real-world use cases.

 

Syntax

toFloat32(x)

 

Here:

·      x: A number or string that can be interpreted as a float.

·      Returns: A value of type Float32.

 

Example 1: Converting an Integer.

SELECT toFloat32(42) AS result;

Above statement converts integer to Float32 with .0 precision.

krishna :) SELECT toFloat32(42) AS result;

SELECT toFloat32(42) AS result

Query id: 196adf52-a7c8-4027-8c31-488f88f37ab1

   ┌─result─┐
1. │     42 │
   └────────┘

1 row in set. Elapsed: 0.004 sec.

   

Example 2: Converting a Decimal Number

 

SELECT toFloat32(123.456789) AS result;

krishna :) SELECT toFloat32(123.456789) AS result;

SELECT toFloat32(123.456789) AS result

Query id: 62a3e8aa-8684-4947-a3d8-368d835d479d

   ┌────result─┐
1. │ 123.45679 │
   └───────────┘

1 row in set. Elapsed: 0.001 sec. 

   

You can see a loss of precision, as Float32 can only hold around 5–6 decimal digits accurately.

 

Example 3: Converting a String

 

SELECT toFloat32('98.7654321') AS result;

krishna :) SELECT toFloat32('98.7654321') AS result;

SELECT toFloat32('98.7654321') AS result

Query id: 16cda33b-bc96-4715-8004-aa9e59958467

   ┌────result─┐
1. │ 98.765434 │
   └───────────┘

1 row in set. Elapsed: 0.001 sec.

   

If the string is not a valid number, it will throw an exception.

krishna :) SELECT toFloat32('98.aa') AS result;

SELECT toFloat32('98.aa') AS result

Query id: a30f2bc0-7068-4e21-928c-1f4347cf4c56


Elapsed: 0.046 sec. 

Received exception from server (version 25.5.1):
Code: 6. DB::Exception: Received from localhost:9000. DB::Exception: Cannot parse string '98.aa' as Float32: syntax error at position 3 (parsed just '98.'). Note: there are toFloat32OrZero and toFloat32OrNull functions, which returns zero/NULL instead of throwing exception.: In scope SELECT toFloat32('98.aa') AS result. (CANNOT_PARSE_TEXT)

Example 4: Converting NULL

SELECT toFloat32(NULL) AS result;

krishna :) SELECT toFloat32(NULL) AS result;

SELECT toFloat32(NULL) AS result

Query id: 91167f08-7352-40d6-9a50-e3ba95af6a83

   ┌─result─┐
1. │ ᴺᵁᴸᴸ   │
   └────────┘

1 row in set. Elapsed: 0.003 sec.

   

When to Use toFloat32()

·      When downcasting from Float64 to save storage or improve performance.

·      When reading data from external sources (e.g., CSV) as strings and converting to floats.

·      For sensor or IoT data where approximate values are acceptable.

·      In type-safe transformations during ETL or query pipelines.

 

Previous                                                    Next                                                    Home