SQLite Type Affinity

What I learned:

• SQLite has only four column types: INTEGER, REAL, TEXT, BLOB
• SQLite columns are dynamically typed
• Date and Time is stored either as TEXT (ISO8601 strings), REAL (number of days) or INTEGER (unix timestamp)¹
• Each column has a preferred type¹

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 ²:

CREATE TABLE foo (a smallint);
INSERT INTO foo (a) VALUES (3);
INSERT INTO foo (a) VALUES (3.14);
INSERT INTO foo (a) VALUES ('pi');