2 Jan 2024

ClickHouse: How does a number have a set number of decimal places?

I’ve been working with a dataset in ClickHouse where I compute currency values and I really struggled to figure out how to get numbers whose decimal part is divisible by 10 to have a fixed number of decimal places. If you want to do that too, hopefully, this blog post will help.

Let’s start by seeing what happens if we output the number 12.40

SELECT 12.40 AS number;

Output

┌─number─┐
│   12.4 │
└────────┘

The trailing 0 has been stripped. But maybe we can fix that by casting it to a Decimal type with 2 decimal places?

SELECT CAST(12.40 AS Decimal(10,2)) AS number;

Output

┌─number─┐
│   12.4 │
└────────┘

Hmm, nope. What about if we coerce the value with the toDecimal32 function instead?

SELECT toDecimal32(12.40, 2) AS number;

Output

┌─number─┐
│   12.4 │
└────────┘

Still nope. At this point I was pretty stuck - Google didn’t have any suggestions and even ChatGPT didn’t know what to do.

Lucky for me, my colleague Pavel came to the rescue by pointing me to the output_format_decimal_trailing_zeros setting. If we enable that, we’ll see our trailing 0 as long as we have a Decimal type.

SELECT 12.40 AS number,
       toDecimal32(number, 2) AS number2,
       CAST(number AS Decimal(10,2)) AS number3
SETTINGS output_format_decimal_trailing_zeros=1;

Output

┌─number─┬─number2─┬─number3─┐
│   12.4 │   12.40 │   12.40 │
└────────┴─────────┴─────────┘

Thanks Pavel!

About the author

I'm currently working on short form content at ClickHouse. I publish short 5 minute videos showing how to solve data problems on YouTube @LearnDataWithMark. I previously worked on graph analytics at Neo4j, where I also co-authored the O'Reilly Graph Algorithms Book with Amy Hodler.