Apply column default to existing rows

ClickHouse includes a special wrapper type called Nullable which allows a column to contain null values. It's common to use this early on in schema design, when a default value has not yet been decided.

CREATE TABLE deleteme
(
    `number` UInt64,
    `date` Nullable(DateTime)
)
ENGINE = MergeTree
PARTITION BY number % 10
ORDER BY number AS
SELECT
    number,
    NULL
FROM numbers(10)

However, you will often find that you eventually want to modify this column to remove Nullable and insert a default value instead of nulls.

ALTER TABLE deleteme MODIFY COLUMN `date` DEFAULT now()

Adding a default value will affect new rows, but will not replace the nulls in existing rows.

SELECT *
FROM deleteme
LIMIT 1;

┌─number─┬─date─┐
│      0 │ ᴺᵁᴸᴸ │
└────────┴──────┘

To apply the new default value to existing rows, you can use MATERIALIZE.

ALTER TABLE deleteme
MATERIALIZE COLUMN `date`;

SELECT *
FROM deleteme
LIMIT 1;

┌─number─┬────────────────date─┐
│      0 │ 2022-09-23 12:31:14 │
└────────┴─────────────────────┘