Determination of column affinity

The type affinity of a column is determined by the declared type of the column, according to the following rules:

  1. If the datatype contains the string "INT", then it is assigned INTEGER affinity.
  2. If the datatype of the column contains any of the strings "CHAR", "BLOB", or "TEXT", then that column has TEXT affinity. Notice that the type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity.
  3. If the datatype for a column contains the string "BLOB" or if no datatype is specified, then the column has affinity NONE.
  4. Otherwise, the affinity is NUMERIC.

If you create a table using a CREATE TABLE table AS SELECT... statement, then all columns have no datatype specified, and they are given no affinity.