---
warning: DO NOT CHANGE THIS MANUALLY, THIS IS GENERATED BY https://github.com/duckdb/community-extensions repository, check README there
title: duck_diff
excerpt: |
  DuckDB Community Extensions
  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.

extension:
  name: duck_diff
  description: 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.
  version: 0.4.0
  language: C++
  build: cmake
  license: MIT
  maintainers:
    - avaitla
  opt_in_platforms: "linux_amd64_musl;linux_arm64_musl"

repo:
  github: avaitla/duck_diff
  ref: 00f636e5a62ee9ba508148d2493006e2ae1734e9

docs:
  hello_world: |
    LOAD duck_diff;

    -- Create two sample snapshots
    CREATE TABLE users_v1 AS SELECT * FROM (VALUES
        (1, 'Ada',   'ada@x.com',   100),
        (2, 'Linus', 'linus@x.com',  50),
        (3, 'Grace', 'grace@x.com',  75)
    ) t(id, name, email, credits);

    CREATE TABLE users_v2 AS SELECT * FROM (VALUES
        (1, 'Ada',   'ada@x.com',   120),   -- credits changed
        (2, 'Linus', 'linus@x.com',  50),   -- unchanged
        (4, 'Mike',  'mike@x.com',   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');

  extended_description: |
    `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`](https://code.claude.com/docs/en/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](https://avaitla.github.io/duck_diff/) ·
    [function reference](https://github.com/avaitla/duck_diff/blob/main/docs/functions.md) ·
    [runnable demos (MySQL, Postgres, ClickHouse, BigQuery, Snowflake, Iceberg, DuckLake, S3 Tables)](https://github.com/avaitla/duck_diff/tree/main/demo) ·
    [AI-assisted migration guide](https://github.com/avaitla/duck_diff/blob/main/docs/ai-assisted-migration.md)

    **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 |

extension_star_count: 8
extension_star_count_pretty: 8
extension_download_count: 1061
extension_download_count_pretty: 1.1k
image: '/images/community_extensions/social_preview/preview_community_extension_duck_diff.png'
layout: community_extension_doc
---
### Compatibility Matrix

This table covers all DuckDB releases that have not reached [end-of-life](https://duckdb.org/release_calendar), 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.

<div class="extension_compatibility_table"></div>

| 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
```sql
INSTALL {{ page.extension.name }} FROM community;
LOAD {{ page.extension.name }};
```

{% if page.docs.hello_world %}
### Example
```sql
{{ page.docs.hello_world }}```
{% endif %}

{% if page.docs.extended_description %}
### About {{ page.extension.name }}
{{ page.docs.extended_description }}
{% endif %}
### Added Functions

<div class="extension_functions_table"></div>

|   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');<br>-- id    \| INTEGER \| BIGINT       \| type_differs<br>-- name  \| VARCHAR \| NULL         \| left_only<br>-- 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 <c>_left / <c>_right / <c>_diff_status. | 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;<br>-- 1 \| identical  \| NULL<br>-- 2 \| different  \| {"v":{"left":20,"right":25{% raw %}}}{% endraw %}<br>-- 3 \| left_only  \| NULL<br>-- 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');<br>-- 1 \| 1 \| 1 \| 1]                                                                                                                          |

### Overloaded Functions

<div class="extension_functions_table"></div>

This extension does not add any function overloads.

### Added Types

<div class="extension_types_table"></div>

This extension does not add any types.

### Added Settings

<div class="extension_settings_table"></div>

|      name      |                       description                       | input_type | scope  | aliases | typed_value |
|----------------|---------------------------------------------------------|------------|--------|---------|-------------|
| temp_directory | Set the directory to which temporary files are written. | VARCHAR    | GLOBAL | []      | post.db.tmp |