This tutorial provides a deep and practical understanding of numeric data types in ClickHouse, how they behave internally, and how to use them correctly in analytical queries. Numeric types are heavily used in analytics for aggregations, metrics, financial data, and scientific computations, which makes understanding their behavior critical.
Choosing the correct numeric type in ClickHouse directly affects storage size, precision, performance, and correctness of analytical results.
We will cover:
- Integer data types
- Floating point data types
- Decimal data types
- Boolean type
- Infinity and NaN behavior
Each section includes example queries and textual result tables so that the behavior is clear.
1. Overview of Numeric Data Types
Numeric data types are used to store numbers in ClickHouse. These include whole numbers, fractional numbers, and fixed-precision numbers.
ClickHouse provides:
- Integers
- Floating point numbers
- Decimals
- Boolean
- Special numeric values (Infinity and NaN)
Each numeric type exists for a reason, and selecting the wrong one can lead to precision loss or unnecessary storage usage.
2. Integer Data Types
An integer is a whole number without any decimal or fractional part.
ClickHouse supports both signed and unsigned integers, and the difference between them determines whether negative values are allowed.
- Signed integers allow both negative and positive values
- Unsigned integers allow only zero and positive values
ClickHouse supports multiple sizes:
- 8-bit
- 16-bit
- 32-bit
- 64-bit
- 128-bit
- 256-bit
Larger bit sizes allow a larger numeric range but use more storage.
2.1 Example: Convert to 16-bit Signed Integer
Query:
SELECT toInt16(123.99) AS result;
Result:
| result |
|---|
| 123 |
The fractional part is truncated and not rounded, which is an important behavior when casting.
3. Floating Point Data Types
Floating point numbers store values with decimal points and are suitable for scientific or approximate calculations.
ClickHouse supports:
- Float32
- Float64
Floating point computations are fast but can introduce rounding errors due to binary representation, which makes them unsuitable for financial precision.
3.1 Example: Convert to Float32
Query:
SELECT toFloat32(10) AS result;
Result:
| result |
|---|
| 10 |
3.2 Example: Floating Precision Behavior
Query:
SELECT toFloat32(0.1) + toFloat32(0.2) AS result;
Result:
| result |
|---|
| 0.30000000447034836 |
This illustrates floating point rounding artifacts.
4. Decimal Data Types
Decimal types are designed for exact precision and are commonly used for financial data.
A decimal number has:
- Precision → total digits allowed
- Scale → digits after decimal point
Types include:
- Decimal32
- Decimal64
- Decimal128
- Decimal256
Decimal arithmetic preserves precision in addition, subtraction, and multiplication.
4.1 Decimal32 Characteristics
Decimal32 supports:
- Precision: 1–9
- Scale: 0–9
4.2 Example: Convert to Decimal32 with Scale 5
Query:
SELECT toDecimal32(123.456789, 5) AS result;
Result:
| result |
|---|
| 123.45678 |
Only five digits remain after the decimal because scale = 5.
4.3 Example: Decimal Addition
Query:
SELECT
toDecimal32(10.25,2) + toDecimal32(2.10,2) AS result;
Result:
| result |
|---|
| 12.35 |
Precision is preserved exactly.
4.4 Example: Decimal Division
Query:
SELECT
toDecimal32(10.00,2) / toDecimal32(3.00,2) AS result;
Result:
| result |
|---|
| 3.33 |
During division, least significant digits are discarded and not rounded.
5. Boolean Data Type
ClickHouse provides a Boolean type, but internally it is stored as an 8-bit integer.
- TRUE → 1
- FALSE → 0
Example
Query:
SELECT true AS result;
Result:
| result |
|---|
| true |
6. Infinity and NaN
ClickHouse supports special numeric values:
- Positive Infinity
- Negative Infinity
- NaN (Not a Number)
These are useful for representing undefined or overflow results in analytics.
6.1 Positive Infinity
Query:
SELECT 1/0 AS result;
Result:
| result |
|---|
| inf |
6.2 Negative Infinity
Query:
SELECT -1/0 AS result;
Result:
| result |
|---|
| -inf |
6.3 NaN
Query:
SELECT 0/0 AS result;
Result:
| result |
|---|
| nan |