DuckDB 2.0 Explained: How to Upgrade Without Breaking Your Data Files

Any older process that opens a file the upgraded one just created will fail with an IO error. With DuckDB 2.0 the change is one-way: new files use a format that DuckDB 1.x cannot read, while old files keep working.

Picture a common setup. An online shop runs a nightly Python job that deletes and rebuilds sales.duckdb from the day's orders.csv. A small dashboard service on another machine opens the same file each morning, pinned to DuckDB 1.5.6. The setup is made up, but the error below is real.

Someone bumps the nightly job to duckdb==2.0.0 because pip says it is the latest. The job runs fine. The next morning the dashboard shows this, the first lines from our run on 1.5.6:

IO Error: Trying to read a database file with version number 69, but we can only read versions between 64 and 68.
The database file was created with a newer version of DuckDB.

Nothing is corrupted. The job wrote a perfectly good file in the new format, and the old reader simply does not understand it. This guide follows that shop's upgrade from start to finish, using this small orders.csv:

order_id,city,units,packs
1,Pune,12,4
2,Kochi,6,0
3,Jaipur,9,3

Every SQL snippet below starts from these three rows, so totals such as 27 units match what you will see.

You need four words: embedded database, database file, storage version and LTS. Each gets a plain meaning and a line from the shop's setup.

• Embedded database: a database that runs inside your own program instead of as a separate server. Example: the nightly job imports duckdb and queries sales.duckdb directly, with no server to call.
• Database file: the single file on disk that holds all tables. Example: sales.duckdb, which the job writes and the dashboard reads.
• Storage version: a label inside the file that says which DuckDB releases can read it. Example: a file labelled v2.0.0+ needs DuckDB 2.0 or newer.
• LTS (long-term support): a release that gets bug fixes for a longer, announced period. Example: 2.0.0 is supported until at least October 2027.

The biggest change in DuckDB 2.0 is the default storage format. A smaller set of SQL and Python behaviours also changed, and most of them now raise errors where 1.5 returned a quiet result.

Only the first row matters for every reader: it is why the dashboard broke. Beginners can skip the other rows; each one is explained, with a fix, in the engineer sections. We ran each row on duckdb 1.5.6 and 2.0.0, side by side, on 23 October 2026.

Most rows now raise errors, so you will see them in your logs. In SQL, three change results silently: the ~ operator, the factorial !, and the X'...' and B'...' literals, which are no longer text. Python has one more, covered in its own section. This table is a selection; the migration guides list every change.

Because 2.0 writes new files in storage version 69, and 1.5 only understands versions 64 to 68. The real change is the default: 1.x wrote new files in an old, widely readable format, and 2.0 writes the newest one.

We checked the setting behind this, called storage_compatibility_version. On 1.4.5 and 1.5.6 its default value is v0.10.2, so new files were readable by almost every release. On 2.0.0 the default is latest.

The new format earns its place. The DuckDB storage page says it loads column metadata lazily, so wide tables open faster and use less memory. It also turns on a string compression method called DICT_FSST by default and stores deleted rows more compactly.

Think of it like a word processor that saves new documents in its newest file type, while still opening and saving your old documents in their original type. A colleague on last year's version can open your old files, but not the new ones. Where the picture breaks: a word processor's Save As can turn an existing document into the older type. DuckDB's setting only labels files it creates, so converting a file means copying it.

Pause and predict: the nightly job on 2.0 appends rows to a file that 1.5.6 created last month. Can the 1.5.6 dashboard still open it? Make a guess, then check the diagram.

Yes, it can, and that explains why a job that only appends would not have broken the dashboard. The shop's job deletes and rebuilds the file each night, so it always creates a new file. A job that only appends to an existing 1.x file keeps it readable by 1.x.

We tested that second case directly. DuckDB 2.0.0 inserted rows, created a table and ran a checkpoint on a file 1.5.6 had made. Both 1.5.6 and 1.4.5 still opened it afterwards.

How to check which storage version a file uses

Ask DuckDB 2.0 to read the file's label. Attach it read-only so nothing changes, then read the storage_version tag.

import duckdb

con = duckdb.connect()
con.execute("ATTACH 'sales.duckdb' AS sales (READ_ONLY)")
row = con.execute(
    "SELECT tags['storage_version'] FROM duckdb_databases() "
    "WHERE database_name = 'sales'"
).fetchone()
print(row[0])

On the shop's rebuilt file this printed v2.0.0+, meaning 2.0 or newer is needed. A file 1.5.6 created printed v1.0.0+, meaning any DuckDB from 1.0 onwards can open it; that is the label for the old v0.10.2 default. Do not trust the raw bytes at the start of the file. The storage page notes that v2.0.0-format files hold 999 there, which is why 1.4.5 reports version 999.

Either upgrade every reader before any writer, or make the 2.0 writer produce an older storage version. The second option buys time when you cannot touch every reader at once.

In Python, pass the setting when you connect. It only applies to files DuckDB creates, so the shop's job now removes last night's file first:

from pathlib import Path
import duckdb

# The setting only applies to new files, so remove last night's file first
for name in ("sales.duckdb", "sales.duckdb.wal"):
    Path(name).unlink(missing_ok=True)

con = duckdb.connect("sales.duckdb", config={"storage_compatibility_version": "v1.5.0"})
con.execute("CREATE OR REPLACE TABLE orders AS SELECT * FROM 'orders.csv'")
con.close()

To check it worked, run the version check above: it should print v1.5.0+. Then open the file from the 1.5.6 dashboard. In our run it returned 3 orders and 27 units, the same as on 2.0.0.

What just happened? The writer ran on 2.0, but it stamped the new file with an older label, v1.5.0+. The 1.5.6 dashboard read that label, recognised it and opened the file as before.

Outside Python you have two more ways to set the same thing. In SQL, add STORAGE_VERSION to ATTACH. The command-line client takes a -storage-version argument. The migration guide documents it, but we did not run it, because the 2.0 command-line binary was not on PyPI as of 23 October 2026.

Upgrade readers before writers, fix the queries that now error or change, and only then let 2.0 write new-format files. The migration guide also suggests a shadow system: a copy of your workload that runs the new version before production does. If you can upgrade every reader first, skip steps 5 and 7. Do step 6 straight after step 4, then move the writer to 2.0 without the pin.

1. 1

   List every process that touches each file

   Write down each job, notebook and service that opens a shared DuckDB file, with its duckdb version. The shop found two: the nightly job and the dashboard.

2. 2

   Fix queries that now error

   Search your SQL for the table rows that now raise errors, such as // and %. Each has a fix that works on both versions, such as wrapping the divisor in NULLIF. The engineer sections give every fix.

3. 3

   Find queries that now return different results

   Some table rows change results with no error, such as 'abc' ~ 'b'. Tests that only look for errors will pass, so run your real queries on a copy with 2.0 and compare the outputs.

4. 4

   Fix Python calls

   Check the job runs on Python 3.11 or newer, because duckdb 2.0 needs it. Then run your Python code once against 2.0 on a copy; the Python section lists the calls that changed.

5. 5

   Upgrade the writer, with the pin

   Move the writer to 2.0.0 with storage_compatibility_version set to the oldest reader's line, such as v1.5.0. Rebuild the file from source so the pin applies. If you cannot rebuild it, copy it into a new pinned file instead, then check its version.

6. 6

   Upgrade the readers

   Move every reader to duckdb 2.0.0 and confirm it still opens the pinned file and returns the same totals.

7. 7

   Remove the pin and rebuild

   Once no 1.x reader remains, drop the setting and recreate or copy the file. Dropping the setting alone leaves an existing file at its old version, so triggers still fail.

Beginners can stop here. You know why the dashboard broke and the safe order to upgrade. The rest is for engineers: the queries that now error, new features, copying between formats, Python and server mode.

Division by zero with // or %, the old lambda arrow and duplicate MERGE matches now raise errors. Each one used to run without an error, so a query that ran for months can start failing on day one of the upgrade.

Division and modulo by zero

The shop's dashboard works out units per pack with integer division, written //. One order has zero packs.

-- Fails on 2.0.0 when packs is 0
SELECT order_id, units // packs AS units_per_pack FROM orders ORDER BY order_id;

-- Works on both: NULLIF turns a zero divisor into NULL
SELECT order_id, units // NULLIF(packs, 0) AS units_per_pack FROM orders ORDER BY order_id;

On 1.5.6 the first query returned 3, NULL and 3. On 2.0.0 it stopped with a Division by zero error that names the expression. The NULLIF version returned 3, NULL and 3 on both versions, so it is safe to deploy before the upgrade.

The rule covers the operators, not only integers: 7.0 // 0 and 7 % 0.0 also failed on 2.0.0. Wrap one expression in TRY(...) to get NULL for it, or SET error_on_division_by_zero = false to bring NULL back everywhere. Version 1.5.6 rejects that setting. Regular division with / still returns inf.

Lambda functions

A lambda is a tiny inline function you pass to list functions such as list_transform. The old style x -> x + 1 now raises a Binder Error. Write lambda x: x + 1 instead, which worked on both 1.5.6 and 2.0.0.

SET lambda_syntax = 'enable_single_arrow' brings the old style back for a session, and our run confirmed it. The migration guide says DuckDB 2.1 removes both that flag and the arrow style. The JSON arrow operator, as in data -> '$.a', is a different thing and still works.

Changes that do not raise errors

The quiet ones need a search, because tests that only check for errors will pass. On 2.0.0, 'abc' ~ 'b' is true because ~ now matches part of a string, like PostgreSQL. SET regex_match_operator_semantics = 'full' brought back false in our run, but the migration guide marks that setting deprecated.

The factorial ! now binds tighter, so 2 * 3! is 12; write (2 * 3)! to get 720. X'...' now gives a BLOB and B'...' a BIT value, where 1.5.6 gave text for both. CREATE TABLE ... AS SELECT NULL AS x makes a column that rejects later values. Cast it, as in NULL::VARCHAR AS x.

Triggers and nested schemas only work in files that use the v2.0.0 format. So the compatibility pin from earlier and these new features cannot be used together on the same file.

A trigger is a SQL statement the database runs on its own when a table changes. The classic use is an audit table: a record of what changed. Here the shop logs every change to the units column, in a new file with the default 2.0 format:

CREATE TABLE order_changes (order_id BIGINT, old_units BIGINT, new_units BIGINT);

CREATE TRIGGER log_unit_changes
    AFTER UPDATE ON orders
    REFERENCING OLD TABLE AS o NEW TABLE AS n
    FOR EACH STATEMENT
        INSERT INTO order_changes
        SELECT n.order_id, o.units, n.units
        FROM o JOIN n ON o.order_id = n.order_id;

UPDATE orders SET units = 10 WHERE order_id = 1;
SELECT * FROM order_changes;

On 2.0.0 this returned one row: order 1, old units 12, new units 10. The OLD TABLE and NEW TABLE parts are transition tables: temporary views of the changed rows before and after the update. On 1.5.6 the same statement is a parser error.

Now try it in a file written with v1.5.0 or v1.4.0. DuckDB 2.0.0 refused with a Binder Error: CREATE TRIGGER is only supported for storage versions v2.0.0 and higher. It said the same on a file that 1.5.6 had created and 2.0 had opened later. The trigger docs page does not mention this limit as of 23 October 2026.

VARIANT is a column type that stores a different type in each row, such as a number in one row and a nested object in the next. It is not brand new: the DuckDB 2.0 highlights post says it shipped in 1.5, and our 1.5.6 run confirmed it. Version 2.0 makes it faster and adds more variant_ functions.

Two smaller additions do not depend on the file at all. The $name shorthand reads a session variable: after SET VARIABLE min_units = 8, the filter WHERE units >= $min_units worked on 2.0.0 and failed on 1.5.6. NEAREST joins match each row with its k closest rows by a distance, which suits embedding search.

Attach both files and run COPY FROM DATABASE, giving the target the storage version you want. It works in both directions, so it is also how you move a pinned file up to the v2.0.0 format.

To serve an old reader from a file that is already in the v2.0.0 format, copy it into a new file with an older storage version. Run this on DuckDB 2.0:

ATTACH 'sales_v2.duckdb' AS src (READ_ONLY);
ATTACH 'sales_for_old_readers.duckdb' AS dst (STORAGE_VERSION 'v1.5.0');
COPY FROM DATABASE src TO dst;

DuckDB 1.5.6 opened the copy and saw both tables, so the old reader is served again.

To go the other way, attach a new file without STORAGE_VERSION and copy into it. Our pinned v1.5.0+ file became v2.0.0+ this way, and CREATE TRIGGER then worked on the copy.

Triggers never come along. COPY FROM DATABASE dropped our trigger whether the target was v1.5.0 or v2.0.0, and EXPORT DATABASE plus IMPORT DATABASE dropped it too. The 2.0 EXPORT docs page says so, so keep every CREATE TRIGGER statement in a script and re-run it after any copy.

The Python package now needs Python 3.11 or newer, and several calls changed. PyPI lists requires_python as 3.11 or newer for 2.0.0 and 3.10 or newer for 1.5.6, so an old interpreter quietly installs 1.x.

A relation is the lazy query object that con.sql() returns. In 2.0 it has no fetchone(), because it keeps no result and reruns its query on every read. Use con.execute(query).fetchone() and then con.complete(), or rel.limit(1).fetchall().

The surprise that cost us a test run was the open result rule. In 2.0, a result from con.execute() that you have not read to the end blocks con.sql() on that connection. A new con.execute() still runs, and it throws the open result away.

import duckdb

con = duckdb.connect()
con.execute("CREATE TABLE orders AS SELECT range AS id FROM range(10)")

total = con.execute("SELECT count(*) FROM orders").fetchone()
con.complete()  # without this line, the next call raises ResourceInUseException on 2.0
print(total, con.sql("SELECT 1").fetchall())

On 2.0.0 this printed (10,) [(1,)]. Without con.complete(), the con.sql() call raised ResourceInUseException, even though the count had only one row. Reading with fetchall(), or calling fetchone() until it returns None, also clears it. On 1.5.6 there is no complete() method, so this line is 2.0 only.

Arrow and Polars output moved to relations too. On 2.0.0, con.execute(q).arrow(), .fetch_arrow_table() and .pl() raised AttributeError, while con.sql(q).to_arrow_table() and con.sql(q).pl() worked. con.execute(q).to_arrow_table() still works but warns that it makes an extra copy.

The quiet Python change is in the expression API: a plain string is now a value, not a column name. FunctionExpression('upper', 'city') returned CITY three times on 2.0.0, with only a UserWarning. Version 1.5.6 returned the city names. ColumnExpression('city') gave the names on both.

One more change shows up in transactions. On 2.0.0, any statement that failed after con.begin() aborted the transaction, even a typo or a missing table. The next statement raised TransactionException, and commit() rolled back without raising an error. On 1.5.6 only errors raised while a statement ran, such as a failed cast, did that. A missing table or a syntax error left the transaction open, and it committed.

We also saw con.sql() on a CREATE statement return a relation instead of None. The Python migration guide adds that calling one connection from two threads at once now raises an error, so give each thread its own con.cursor().

Server mode lets one DuckDB process serve its databases to other DuckDB processes over the network. It comes from an extension called quack, which DuckDB downloads automatically on first use. The docs say it has existed since 1.5.3; 2.0 ships a polished version.

From the Quack protocol docs: the server starts with CALL quack_serve('quack:localhost') and listens on port 9494 by default. A client runs ATTACH 'quack:localhost' AS shop with a token, then CONNECT shop, and later queries run on the server. CONNECT also works with PostgreSQL and MySQL.

By default the server only listens on localhost, meaning the same machine. For remote access the docs recommend a TLS-terminating reverse proxy, a web server in front that handles HTTPS. We could not run this ourselves, because our test machine could not download extensions.

The Quack security page adds the caveat that matters most. A server exposes read and write access to every table its session can see, and the default authorization lets every query through. Anyone with the token can change data unless you add an authorization rule.

For the shop, server mode is a later step, not part of the upgrade. It would let the dashboard query the job's database instead of sharing a file. With both sides on 2.0, that removes the file-format problem, but it adds a network service to secure and run.

Frame it as a rolling upgrade with a one-way data format, the same problem as a schema change shared by many services. Interviewers want to hear the order of steps and how you would notice failure.

1. 1

   Name the constraint

   New files from 2.0 cannot be read by 1.x, but 2.0 reads every 1.x file. So the risk sits with readers, not writers.

2. 2

   Map who reads and writes

   Draw each process and the files it touches. A shared file with mixed versions is where the outage happens.

3. 3

   Choose an order

   Upgrade readers first. If you cannot, pin the writer's storage version to the oldest reader and say which version that is.

4. 4

   Plan the rollback

   Keep the old binary and a pinned-format copy. COPY FROM DATABASE can move data down, but it drops triggers, so recreate them from your schema script.

5. 5

   Say how you would verify

   Check the storage_version tag after each nightly run and alert on IO errors in readers. Run a shadow system first.