DuckDB 2.0 shipped today. I ran its headline features — triggers work, VARIANT has a silent-NULL trap

DuckDB 2.0.0 LTS, codename "Cinnamon Teal," shipped today (October 21), billed as the project's biggest feature release yet: close to 20,000 commits since 1.5, a polished Quack client-server protocol, asynchronous I/O, a first-class VARIANT type, triggers, a new PEG parser as the default, an "agent mode" for the CLI, and support promised until at least October 2027.

Launch posts are menus. I wanted a scoreboard. So I installed the Python client — duckdb==2.0.0, on a 2 vCPU Linux VM — and ran the headline features myself. Most of them work exactly as advertised. One of them has a trap that returns NULL instead of an error, which is my least favorite kind of trap.

Triggers: the blog's example, verbatim

2.0 adds CREATE TRIGGER with BEFORE/AFTER, FOR EACH ROW/FOR EACH STATEMENT, and transition tables via REFERENCING OLD/NEW TABLE. I ran the release post's own use case — triggers maintaining an aggregate table incrementally:

CREATE TABLE people (name VARCHAR, nationality VARCHAR, age INTEGER);
CREATE TABLE age_statistics (
    nationality VARCHAR PRIMARY KEY,
    sum_age INTEGER, num_people INTEGER, average_age DOUBLE);

CREATE TRIGGER people_insert
    AFTER INSERT ON people
    REFERENCING NEW TABLE AS n
    FOR EACH STATEMENT
        MERGE INTO age_statistics s
        USING (SELECT nationality, sum(age) AS sum_age,
                      count(*) AS num_people
               FROM n GROUP BY nationality) d
        ON s.nationality = d.nationality
        WHEN MATCHED THEN UPDATE SET
            sum_age = s.sum_age + d.sum_age,
            num_people = s.num_people + d.num_people,
            average_age = (s.sum_age + d.sum_age)
                        / (s.num_people + d.num_people)
        WHEN NOT MATCHED THEN INSERT
            VALUES (d.nationality, d.sum_age, d.num_people,
                    d.sum_age / d.num_people);

INSERT INTO people VALUES
    ('Alice', 'US', 34), ('Bram', 'NL', 27), ('Csilla', 'HU', 45),
    ('Dave', 'US', 58), ('Eva', 'NL', 19), ('Femke', 'NL', 41);

FROM age_statistics ORDER BY nationality;

('HU', 45, 1, 45.0)
('NL', 87, 3, 29.0)
('US', 92, 2, 46.0)

Matches the post's output exactly. Statement-level triggers with transition tables are the genuinely useful kind — that's a hand-rolled materialized view, maintained by the database instead of by a cron job and a prayer.

The limitations are also real, and at least they fail loudly:

CREATE TRIGGER ... BEFORE INSERT ... FOR EACH ROW
→ Not implemented Error: BEFORE FOR EACH ROW triggers
  are not yet supported

VARIANT: smaller on disk, and one silent NULL

VARIANT stores semi-structured data in a binary, shreddable form instead of JSON text. The basic demo from the post works:

CREATE TABLE events (payload VARIANT);
INSERT INTO events VALUES
    ('{"user": {"id": 42, "tags": ["a", "b"]}}'::JSON::VARIANT),
    ('{"event": "click", "x": 10, "y": 20}'::JSON::VARIANT);

SELECT variant_type(payload), variant_keys(payload) FROM events;

('OBJECT', ['user'])
('OBJECT', ['event', 'x', 'y'])

Then I reached for the function whose name promises extraction, and fed it the path syntax every JSON tool on earth has trained me to use:

SELECT variant_extract(payload, '$.user.id') FROM events;

(NULL,)
(NULL,)

No error. Just NULL — for a row where the value demonstrably exists. It turns out variant_extract takes a single key name, not a path:

variant_extract(payload, 'user')       → {'id': 42, 'tags': ['a', 'b']}
variant_extract(payload, '$.user')     → NULL
variant_extract(payload, 'user.id')    → NULL
variant_extract(payload, '/user/id')   → NULL
payload.user.id                        → 42        ← dot access works
payload['user'].id                     → 42        ← so does this

Silent NULLs on a plausible-but-wrong path syntax are how wrong analytics happen. If you migrate JSON code to VARIANT, grep for variant_extract with a $ in it before you grep for anything else.

The storage win, at least, is real on my test. I built a 200,000-row table of small JSON documents ({"user":{"id":…,"score":…}}), stored it as text in one database file and as VARIANT in another, checkpointed both:

JSON text table   3,944,448 bytes
VARIANT table     2,895,872 bytes      (−27%)

The query-speed story was less flattering — on this workload. Median of 5 runs, summing user.score over all 200k rows, same total (9,599,419) both ways:

JSON text, json_extract(s, '$.user.score')   0.020s
VARIANT, payload.user.score                  0.076s

Before you tweet this at the DuckDB team: my VARIANT column was made by casting strings in an in-memory table, which is not the shredded-Parquet read path where 2.0's VARIANT work is aimed, and 200k tiny rows is a parser-overhead game more than a storage game. What I can honestly say is narrower: the "JSON on steroids" speedup did not show up for free on a naive cast-and-query workload, and the storage saving did. Measure on your own files, ideally the Parquet ones.

The small stuff, rapid fire

SET VARIABLE x = 41;  SELECT $x + 1;
→ 42                         ($variable shorthand works)

WITH ins AS (INSERT INTO t VALUES (1),(2),(3) RETURNING i)
SELECT sum(i) FROM ins;
→ 6                          (data-modifying CTEs work)

SELECT list_transform([1,2,3], x -> x + 1);
→ Binder Error: Deprecated lambda arrow (->) detected...
  Use SET lambda_syntax='ENABLE_SINGLE_ARROW' to revert
  (the old lambda syntax is off by default now, as promised)

SELECT json_set('{"a": 1}', '$.b', 2);
→ Binder Error: no overload for (VARCHAR, VARCHAR, INTEGER) —
  json_set exists, but wants JSON-typed arguments:
  json_set('{"a": 1}'::JSON, '$.b', '2'::JSON) → {"a":1,"b":2}

Two more honest footnotes. Nested schemas are listed as a 2.0 feature, but CREATE SCHEMA finance.reports failed cold with "finance" is not a catalog or schema — create the parent first, then the child works. And the new PEG parser's error messages are as good as advertised:

SELECT * FROM people WHERE age >>
Parser Error: syntax error at or near ">>"
LINE 1: SELECT * FROM people WHERE age >>
                                       ^^

Precise location, caret included. Every SQL tool should do this; most don't.

The upgrade warning that matters

The breaking-change section deserves more attention than the features. Files created by 2.0 cannot be read by pre-2.0 versions; 2.0 reads your existing files fine and keeps them readable by older versions. The DuckDB team itself suggests testing on a shadow system and, for mission-critical workloads, waiting for the first patch release in November. That is admirably un-hypey advice for a launch post, and I'd take it.

Overall: triggers work, the parser is better, the friendly-SQL additions are real, and VARIANT is a genuine storage improvement with one sharp edge in its extraction API. An LTS you can test in an afternoon — that's the right shape for a 2.0.

Sources: DuckDB, "Announcing DuckDB 2.0.0 LTS 'Cinnamon Teal'," October 21, 2026 (duckdb.org/2026/10/21/announcing-duckdb-200). All queries and timings are my own runs against the duckdb 2.0.0 Python package on a 2 vCPU VM; timings are workload-specific, as discussed.