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)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.14);
INSERT INTO foo (a) VALUES ('pi');


  1. SQLite Documentation 2

  2. Data Types