Diff two relations (tables, queries) off a primary key, reporting per-key status (identical/different/left_only/right_only), a JSON summary of changed columns, and per-column left/right values.
Compatibility Matrix
This table covers all DuckDB releases that have not reached end-of-life, including LTS releases, and shows the 2 most recent patch releases of each. Older patch releases are not listed, but the extension may be available for them as well.
| Platform | 1.4.4 | 1.4.5 | 1.5.5 | 1.5.6 | 2.0.0 |
|---|---|---|---|---|---|
| Linux AMD64 | |||||
| Linux ARM64 | |||||
| macOS AMD64 | |||||
| macOS ARM64 | |||||
| Windows AMD64 | |||||
| Windows ARM64 | |||||
| WebAssembly |
Installing and Loading
INSTALL duck_diff FROM community;
LOAD duck_diff;
Example
LOAD duck_diff;
-- Create two sample snapshots
CREATE TABLE users_v1 AS SELECT * FROM (VALUES
(1, 'Ada', '[email protected]', 100),
(2, 'Linus', '[email protected]', 50),
(3, 'Grace', '[email protected]', 75)
) t(id, name, email, credits);
CREATE TABLE users_v2 AS SELECT * FROM (VALUES
(1, 'Ada', '[email protected]', 120), -- credits changed
(2, 'Linus', '[email protected]', 50), -- unchanged
(4, 'Mike', '[email protected]', 10) -- new (id 3 removed)
) t(id, name, email, credits);
-- Diff them off the primary key
SELECT id, diff_status, diff_data, credits_left, credits_right
FROM table_diff('FROM users_v1', 'FROM users_v2', pk := 'id')
ORDER BY id;
-- Or get counts and percentages per status
SELECT * FROM table_diff_summary('FROM users_v1', 'FROM users_v2', pk := 'id');
About duck_diff
duck_diff finds the difference between two tables — which can be on
entirely different systems if needed. Typical uses:
- Checking a copy is faithful: CDC pipelines, read replicas, warehouse ELT, migrations. Per-key results show stale rows, missed deletes, and missing inserts that row counts hide.
- Verifying a SQL rewrite (refactor, dialect translation, performance work) returns the same rows as the original.
- Letting a coding agent such as Claude verify its own changes: the check
is deterministic (
n_total = n_identical), so an agent can rewrite, run the diff, and repeat until it passes — with Claude Code's/goal, set the diff returning true as the completion condition and it keeps working across turns until that verifiably holds.
Each key gets a status (identical / different / left_only /
right_only), a JSON diff_data of the changed columns, and
<c>_left / <c>_right / <c>_diff_status columns. Composite keys are
supported; upcast_types, numeric_tolerance, timestamp_precision,
null_equals_empty, and columns / ignore / context control the
comparison across engines.
Links: website & cross-database recipe builder · function reference · runnable demos (MySQL, Postgres, ClickHouse, BigQuery, Snowflake, Iceberg, DuckLake, S3 Tables) · AI-assisted migration guide
Functions:
| Function | Returns | Purpose |
|---|---|---|
table_diff(left, right, pk := ...) |
table | one row per key: key column(s), diff_status, diff_data, expanded columns |
table_diff_summary(left, right, pk := ...) |
one row | counts (and percentages) per status |
schema_diff(left, right) |
table | per-column name/type comparison — reads no rows |
Added Functions
| function_name | function_type | description | comment | examples |
|---|---|---|---|---|
| schema_diff | table | Compares the column names and types of two relations (query strings) without reading any rows: one row per column with left_type, right_type and status (identical/type_differs/left_only/right_only). | NULL | [SELECT * FROM schema_diff('SELECT 1::INTEGER AS id, ''a'' AS name', 'SELECT 1::BIGINT AS id, 2.5 AS score'); – id | INTEGER | BIGINT | type_differs – name | VARCHAR | NULL | left_only – score | NULL | DECIMAL(2,1) | right_only] |
| table_diff | table | Diffs two relations (query strings, e.g. 'FROM t') on a primary key: one row per key with diff_status (identical/different/left_only/right_only), a JSON diff_data of the changed columns, and per-column |
NULL | [SELECT id, diff_status, diff_data FROM table_diff('FROM (VALUES (1, 10), (2, 20), (3, 30)) t(id, v)', 'FROM (VALUES (1, 10), (2, 25), (4, 40)) t(id, v)', pk := 'id') ORDER BY id; – 1 | identical | NULL – 2 | different | {"v":{"left":20,"right":25}} – 3 | left_only | NULL – 4 | right_only | NULL] |
| table_diff_summary | table | Summarizes a table_diff of two relations in one row: per-status counts (n_identical, n_different, n_left_only, n_right_only, n_total) and their percentages. Takes the same parameters as table_diff. | NULL | [SELECT n_identical, n_different, n_left_only, n_right_only FROM table_diff_summary('FROM (VALUES (1, 10), (2, 20), (3, 30)) t(id, v)', 'FROM (VALUES (1, 10), (2, 25), (4, 40)) t(id, v)', pk := 'id'); – 1 | 1 | 1 | 1] |
Overloaded Functions
This extension does not add any function overloads.
Added Types
This extension does not add any types.
Added Settings
| name | description | input_type | scope | aliases | typed_value |
|---|---|---|---|---|---|
| temp_directory | Set the directory to which temporary files are written. | VARCHAR | GLOBAL | [] | post.db.tmp |