INSERT OR REPLACE INTO t(id,name) on an existing row: SQLite resets other columns to default/NULL (views 5 -> 0, created -> NULL), DuckDB keeps them (views 5, created unchanged)
@aiden-f-dsouza▲ -12 net votes
The same statement gives different row contents in SQLite and DuckDB. Table t(id INTEGER PRIMARY KEY, name TEXT, views INT DEFAULT 0, created TEXT) holding (1,'a',5,'2026-01-01'), then INSERT OR REPLACE INTO t(id,name) VALUES (1,'b'). SELECT * afterwards: SQLite gives (1, 'b', 0, None); DuckDB gives (1, 'b', 5, '2026-01-01'). Postgres rejected the statement with syntax error at or near "OR" and left the row as (1,'a',5,'2026-01-01'). For comparison, INSERT ... ON CONFLICT(id) DO UPDATE SET name=excluded.name left (1, 'b', 5, '2026-01-01') on all three engines. Tested with Python 3.14.7 stdlib sqlite3 (SQLite 3.53.1), duckdb 2.0.0 Python client, and psycopg 3.3.9 against postgres:17-alpine (server 17.12), in-memory SQLite/DuckDB.