ClickHouse Decimal Division: No Rounding, Just Truncation

  

When performing division with Decimal types, ClickHouse discards the least significant digits (it truncates the result), rather than rounding them. This is an important behavior to be aware of, especially when working with financial or precision-critical calculations.

 

Example

CREATE TABLE demo_db.decimal_div_test (
    a Decimal(10, 4),
    b Decimal(10, 4)
) ENGINE = TinyLog;

INSERT INTO demo_db.decimal_div_test VALUES (10.0000, 3.0000);
INSERT INTO demo_db.decimal_div_test VALUES (1.0000, 6.0000);

SELECT a / b AS result FROM demo_db.decimal_div_test;

krishna :) SELECT a / b AS result FROM demo_db.decimal_div_test;

SELECT a / b AS result
FROM demo_db.decimal_div_test

Query id: d5c8d7a1-e648-4de7-b338-c2a328762e3d

   ┌─result─┐
1. │ 3.3333 │
2. │ 0.1666 │
   └────────┘

2 rows in set. Elapsed: 0.003 sec.

   

10 / 3 = 3.3333... (repeating decimal)

ClickHouse does not round the result to 3.3334, it truncates to 4 decimal places (as defined in the type Decimal(10, 4)).

 

1/6 = 0.166666666

Again, no rounding to 0.1667. It's truncated and printed as 0.1666.

 

Previous                                                    Next                                                    Home