A Deep Dive into Decimal Types in ClickHouse

  

ClickHouse is a powerful analytical database that primarily focuses on speed and efficiency. However, when dealing with financial data, monetary calculations, or scientific measurements, accuracy matters as much as performance. This is where Decimal types shine.

 

Unlike floating-point types (Float32/Float64), which may introduce rounding errors, ClickHouse’s Decimal types allow you to represent numbers with exact precision and controlled scale, making them perfect for applications requiring deterministic arithmetic.

 

In this post, we'll explore the different Decimal types in ClickHouse, their precision and scale capabilities, and real-world examples showing how to use them correctly.

 

What is a Decimal type?

Decimal types (also known as numeric or fixed-point types) represent numbers with a fixed number of digits before and after the decimal point. They are defined by two key properties:

 

·      Precision: The total number of digits (both before and after the decimal point). Example: 123.45 has a precision of 5.

 

·      Scale: The number of digits after the decimal point. Example: 123.45 has a scale of 2.

 

Following table summarizes the decimal type variants in ClickHouse.

 

Type	Precision Range	Scale Range	Max Total Digits
Decimal32	1 to 9	0 to 9	9
Decimal64	10 to 18	0 to 18	18
Decimal128	19 to 38	0 to 38	38
Decimal256	39 to 75	0 to 75	75

 

Follow below step-by-step procedure to work with an example.

CREATE DATABASE IF NOT EXISTS demo_db;

CREATE TABLE demo_db.financials (
    id UInt32,
    price Decimal(10, 2),         -- 10 total digits, 2 after decimal
    tax Decimal64(4),         -- High precision for financial tax calculation
    interest_rate Decimal32(3) -- 9 total digits, 3 after decimal
) ENGINE = MergeTree
ORDER BY id;

INSERT INTO demo_db.financials VALUES
(1, 123456.78, 0.0825, 3.125),
(2, 99999.99, 0.095, 2.875);

SELECT
    id,
    price,
    tax,
    round(price * tax, 2) AS tax_amount,
    interest_rate,
    round(price * interest_rate / 100, 2) AS interest_amount
FROM demo_db.financials;

krishna :) SELECT
    id,
    price,
    tax,
    round(price * tax, 2) AS tax_amount,
    interest_rate,
    round(price * interest_rate / 100, 2) AS interest_amount
FROM demo_db.financials;

SELECT
    id,
    price,
    tax,
    round(price * tax, 2) AS tax_amount,
    interest_rate,
    round((price * interest_rate) / 100, 2) AS interest_amount
FROM demo_db.financials

Query id: 8e292305-298b-4802-9ee9-68f2b9833d6b

   ┌─id─┬─────price─┬────tax─┬─tax_amount─┬─interest_rate─┬─interest_amount─┐
1. │  1 │ 123456.78 │ 0.0825 │   10185.18 │         3.125 │         3858.02 │
2. │  2 │  99999.99 │  0.095 │       9500 │         2.875 │            2875 │
   └────┴───────────┴────────┴────────────┴───────────────┴─────────────────┘

2 rows in set. Elapsed: 0.008 sec. 

   

Decimal vs (Decimal32, Decimal64, Decimal128, Decimal256)

There are two flavours of Decimal

·      Decimal(precision, scale): Flexible & Smart, you can specify precision and scale.

·      Decimal32, Decimal64, Decimal128, Decimal256 – Fixed Types: Only specify scale, precision is fixed

 

Previous                                                    Next                                                    Home