> ## Documentation Index
> Fetch the complete documentation index at: https://clickhouse.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.
> CoalescingMergeTree inherits from the MergeTree engine. Its key feature is the ability to automatically store last non-null value of each column during part merges.
# CoalescingMergeTree table engine
**Available from version 25.6**
This table engine is available from version 25.6 and higher in both OSS and Cloud.
This engine inherits from [MergeTree](/docs/reference/engines/table-engines/mergetree-family/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](/docs/reference/engines/table-engines/mergetree-family/mergetree)) 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](/docs/reference/engines/table-engines/mergetree-family/replacingmergetree).
Creating a table
```sql theme={null}
CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
...
) ENGINE = CoalescingMergeTree([columns])
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]
```
For a description of request parameters, see [request description](/docs/reference/statements/create/table).
Parameters of CoalescingMergeTree
Columns
`columns` - Optional. A tuple with the names of columns where values will be united. The provided columns 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](/docs/reference/engines/table-engines/mergetree-family/mergetree) 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.
```sql theme={null}
CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
...
) ENGINE [=] CoalescingMergeTree(date-column [, sampling_expression], (primary, key), index_granularity, [columns])
```
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:
```sql theme={null}
CREATE TABLE test_table
(
key UInt64,
value_int Nullable(UInt32),
value_string Nullable(String),
value_date Nullable(Date)
)
ENGINE = CoalescingMergeTree()
ORDER BY key
```
Insert data to it:
```sql theme={null}
INSERT INTO test_table VALUES(1, NULL, NULL, '2025-01-01'), (2, 10, 'test', NULL);
INSERT INTO test_table VALUES(1, 42, 'win', '2025-02-01');
INSERT INTO test_table(key, value_date) VALUES(2, '2025-02-01');
```
The result will looks like this:
```sql theme={null}
SELECT * FROM test_table ORDER BY key;
```
```text theme={null}
┌─key─┬─value_int─┬─value_string─┬─value_date─┐
│ 1 │ 42 │ win │ 2025-02-01 │
│ 1 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ 2025-01-01 │
│ 2 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ 2025-02-01 │
│ 2 │ 10 │ test │ ᴺᵁᴸᴸ │
└─────┴───────────┴──────────────┴────────────┘
```
Recommended query for correct and final result:
```sql theme={null}
SELECT * FROM test_table FINAL ORDER BY key;
```
```text theme={null}
┌─key─┬─value_int─┬─value_string─┬─value_date─┐
│ 1 │ 42 │ win │ 2025-02-01 │
│ 2 │ 10 │ test │ 2025-02-01 │
└─────┴───────────┴──────────────┴────────────┘
```
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.
```sql theme={null}
SELECT key, last_value(value_int), last_value(value_string), last_value(value_date) FROM test_table GROUP BY key; -- Not recommended.
```
Tuple element aggregation
When the `allow_tuple_element_aggregation` setting is enabled, `Tuple` columns are recursively flattened so that each leaf element participates in coalescing independently. This allows you to store multiple fields in a single `Tuple` column and have them coalesced element-wise during merges — each `Nullable` sub-column retains the latest non-NULL value independently.
The same rules apply to the flattened sub-columns as to regular columns:
* Sub-columns that belong to a `Tuple` in the sorting key or partition key are excluded from coalescing.
* If `columns` is specified, only sub-columns of the listed `Tuple` columns are coalesced.
This setting is immutable and must be specified at table creation time.
```sql theme={null}
CREATE TABLE coalescing_tuples
(
key UInt64,
data Tuple(
value_a Nullable(UInt64),
value_b Nullable(String),
nested Tuple(
value_c Nullable(UInt64)
)
)
) ENGINE = CoalescingMergeTree()
ORDER BY key
SETTINGS allow_tuple_element_aggregation = 1;
INSERT INTO coalescing_tuples VALUES (1, (100, NULL, (NULL)));
INSERT INTO coalescing_tuples VALUES (1, (NULL, 'hello', (42)));
SELECT key, data.value_a, data.value_b, data.nested.value_c FROM coalescing_tuples FINAL;
```
```text theme={null}
┌─key─┬─data.value_a─┬─data.value_b─┬─data.nested.value_c─┐
│ 1 │ 100 │ hello │ 42 │
└─────┴──────────────┴──────────────┴─────────────────────┘
```