CoalescingMergeTree
This engine inherits from MergeTree. The key difference is in how data parts are merged: for CoalescingMergeTree tables, ClickHouse replaces all rows with the same primary key (or more precisely, the same sorting key) with a single row that contains the latest non-NULL values for each column.
This enables column-level upserts, meaning you can update only specific columns rather than entire rows.
CoalescingMergeTree is intended for use with Nullable types in non-key columns. If the columns are not Nullable, the behavior is the same as with ReplacingMergeTree.
Creating a table
For a description of request parameters, see request description.
Parameters of CoalescingMergeTree
Columns
columns - a tuple with the names of columns where values will be united. Optional parameter.
The columns must be of a numeric type and must not be in the partition or sorting key.
If columns is not specified, ClickHouse unites the values in all columns that are not in the sorting key.
Query clauses
When creating a CoalescingMergeTree table the same clauses are required, as when creating a MergeTree table.
Deprecated Method for Creating a Table
Do not use this method in new projects and, if possible, switch the old projects to the method described above.
All of the parameters excepting columns have the same meaning as in MergeTree.
columns— tuple with names of columns values of which will be summed. Optional parameter. For a description, see the text above.
Usage example
Consider the following table:
Insert data to it:
The result will looks like this:
Recommended query for correct and final result:
Using the FINAL modifier forces ClickHouse to apply merge logic at query time, ensuring you get the correct, coalesced "latest" value for each column. This is the safest and most accurate method when querying from a CoalescingMergeTree table.
An approach with GROUP BY may return incorrect results if the underlying parts have not been fully merged.
