SQLite Type Affinity Surprise

Not only that, it does not throw any error if you give some random type. CREATE TABLE t(value TIMMYSTAMP); There is no TIMMYSTAMP type, but SQLite accepts this happily. SQLite has five types: NULL, INTEGER, REAL, TEXT, BLOB. Want to know something cursed? The type affinity works by substring match! CREATE TABLE t(value SPONGEBLOB) ---- This is BLOB type! So yeah, this happens too: Note that a declared type of “FLOATING POINT” would give INTEGER affinity, not REAL affinity, due to the “INT” at the end of “POINT”.

A screenshot explains SQLite’s permissive type system with examples like the nonexistent TIMMYSTAMP type, SPONGEBLOB being treated as BLOB, and FLOATING POINT receiving INTEGER affinity because it contains “INT”. The joke is that SQLite determines type affinity through substring matching, producing bizarre but valid results. It is shared as a reaction to cursed database design and surprising technical behavior.

• @ithueti / 16326 2025-07-06