How to Upsert in ClickHouse
Last updated July 25, 2026 · By the SaturnSQL team
ClickHouse has no INSERT ... ON CONFLICT. The standard pattern is a ReplacingMergeTree with a version column: always insert the new state of the row, and let merges keep the latest version per key. Read with FINAL or argMax for correct results before merges happen.
CREATE TABLE customers
(
customer_id UInt64,
email String,
plan LowCardinality(String),
updated_at DateTime
)
ENGINE = ReplacingMergeTree(updated_at)
ORDER BY customer_id;
INSERT INTO customers VALUES (101, '[email protected]', 'pro', now());Read the latest version
SELECT customer_id,
argMax(email, updated_at) AS email,
argMax(plan, updated_at) AS plan
FROM customers
GROUP BY customer_id;SELECT ... FINAL is simpler but slower on large tables; the argMax form parallelizes better. Avoid ALTER TABLE UPDATE for upsert-style workloads, mutations are far too heavy for that.
Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free