What I learned:
- SQLite has only four column types:
- SQLite columns are dynamically typed
- Date and Time is stored either as TEXT (ISO8601 strings), REAL (number of days) or INTEGER (unix timestamp)1
- Each column has a preferred type1
A column with NUMERIC affinity may contain values using all five storage classes. When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if such conversion is lossless and reversible.
- This is a valid SQL in SQLite 2:
CREATE TABLE foo (a smallint); INSERT INTO foo (a) VALUES (3); INSERT INTO foo (a) VALUES (3.14); INSERT INTO foo (a) VALUES ('pi');