How column affinity is determined

Updated: October 26, 2022
The type affinity of a column is determined by its declared type, according to these rules:
  1. If the datatype contains the string INT, it is assigned INTEGER affinity.
  2. If the datatype contains any of the strings CHAR, BLOB, or TEXT, the column is assigned TEXT affinity. Note that the VARCHAR type contains the string CHAR and hence, is assigned TEXT affinity.
  3. If the datatype contains the string BLOB or no datatype is specified, the assigned affinity is 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 are given no affinities.