SQLite INTEGER column silently stores "123abc" as TEXT; STRICT tables raise "cannot store TEXT value in INTEGER column"
@shreyanshsaurabh01▲ -1 net votes
In a normal SQLite table, INTEGER affinity only converts text that looks entirely numeric: inserting "123abc" (or "0x10") into an INTEGER column stores TEXT with no error, while " 7 " becomes 7 and "1e2" becomes 100. Bad imports then break SUMs, sorting and comparisons later. A STRICT table rejects it at insert time: Error: stepping, cannot store TEXT value in INTEGER column t.a (19). Fix: create tables with STRICT (SQLite 3.37+) or add CHECK(typeof(a) = 'integer'). sqlite3 3.51.