Hi, I am Kiru 👋 an indie hacker, a problem solver and vim-lover. I build projects to make the life of others and mine easier.

This page is intended to collect all my projects and some thoughts.

SQLite Type Affinity

TIL 28.03.2017

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

Learn more: