Type Affinity

The five storage classes

SQLite stores a value as one of five types, and it stores it per value, not per column. A column has an affinity, which is a preference, not a constraint.

typeof() reports the storage class of the value that is actually there.

CREATE TABLE t(a TEXT, b INTEGER, c REAL, d BLOB, e);
INSERT INTO t VALUES('7', '7', '7', '7', '7');
SELECT typeof(a), typeof(b), typeof(c), typeof(d), typeof(e) FROM t;
-- text|integer|real|text|text

The same literal '7' landed as text, integer, real, text, and text, decided entirely by each column's affinity. Column d is a BLOB column and it kept the text unchanged, because BLOB affinity means "no conversion at all".

The five affinities and how they are chosen

Affinity comes from the declared type name via five rules, applied in order. The rules match substrings, which is why unfamiliar type names still work.

CREATE TABLE aff(
  a BIGINT,           -- INTEGER, contains INT
  b VARCHAR(10),      -- TEXT, contains CHAR
  c DOUBLE PRECISION, -- REAL, contains DOUB
  d DECIMAL(10,2),    -- NUMERIC, matches nothing above
  e BOOLEAN,          -- NUMERIC
  f DATETIME,         -- NUMERIC
  g STRING,           -- NUMERIC, since it has no CHAR/CLOB/TEXT
  h                   -- BLOB, no declared type
);

Two of those surprise people every time. STRING is NUMERIC, not TEXT, because the rules look for CHAR, CLOB, or TEXT and STRING contains none of them. And POINT, UUID, JSON, MONEY are all NUMERIC too.

The affinities behave as follows on insert:

- TEXT converts integers and reals to text. Blobs are left alone. - NUMERIC converts a text value to an integer or real if it looks like a number and the conversion is lossless, otherwise it stores the text as text. - INTEGER is NUMERIC, plus a real with no fractional part becomes an integer. - REAL is NUMERIC, plus integers become reals. - BLOB does nothing. This affinity used to be called "none".

VARCHAR(10) does not truncate

The length in a type name is parsed and then discarded. SQLite has no maximum column length short of the global 1GB string limit.

CREATE TABLE u(name VARCHAR(3));
INSERT INTO u VALUES('a much longer string than three characters');
SELECT length(name) FROM u;   -- 41

Nothing errors, nothing truncates, nothing warns. Enforce a maximum yourself.

CREATE TABLE u2(
  name TEXT NOT NULL CHECK(length(name) BETWEEN 1 AND 40)
);
INSERT INTO u2 VALUES('x');   -- fine
-- Error: CHECK constraint failed: length(name) BETWEEN 1 AND 40

A CHECK is the only thing that actually constrains a value's shape in a non-STRICT table, and it runs on every insert and update.

A TEXT column happily stores an integer

This is the trap that ruins joins and comparisons. Affinity converts what it can, and stores the rest as-is.

CREATE TABLE ids(id TEXT);
INSERT INTO ids VALUES('42');
INSERT INTO ids VALUES(42);          -- becomes '42' via TEXT affinity, fine
CREATE TABLE mix(v);                 -- BLOB affinity, no conversion at all
INSERT INTO mix VALUES(42), ('42');
SELECT typeof(v), v FROM mix;
-- integer|42
-- text|42

Now comparisons split, because SQLite orders storage classes before comparing values.

SELECT 42 = '42';               -- 0, an integer never equals a text
SELECT '42' < 5;                -- 0, every text sorts after every number
SELECT 2 < 'apple';             -- 1
SELECT x'42' > 'zzz';           -- 1, every blob sorts after every text

The sort order across classes is NULL < INTEGER = REAL < TEXT < BLOB, and equality between different classes is always false. An untyped or wrongly typed column therefore produces a table where WHERE id = ? matches some rows and not others depending on whether the client bound a string or a number.

-- Find rows whose storage class does not match the column's intent
SELECT rowid, typeof(v), v FROM mix WHERE typeof(v) <> 'text';

That query is the standard audit for a legacy database.

CAST and comparison affinity

CAST forces a conversion, following the same rules as affinity but unconditionally.

SELECT CAST('42abc' AS INTEGER);   -- 42, it takes the leading numeric prefix
SELECT CAST('abc' AS INTEGER);     -- 0, no prefix means 0, NOT an error
SELECT CAST('3.9' AS INTEGER);     -- 3, truncates toward zero
SELECT CAST(3.9 AS INTEGER);       -- 3
SELECT CAST(-3.9 AS INTEGER);      -- -3
SELECT CAST(42 AS TEXT);           -- '42'
SELECT CAST('1e3' AS REAL);        -- 1000.0
SELECT typeof(CAST(NULL AS TEXT)); -- null, a cast never invents a value

CAST('abc' AS INTEGER) returning 0 rather than raising is the single most dangerous piece of this. A malformed row silently becomes zero, which then survives every downstream sum and average.

In a comparison, affinity is applied to the other side of the operator when one side is a column, which is why an index can still be used.

-- id is INTEGER PRIMARY KEY, so the text literal gets integer affinity applied
SELECT * FROM users WHERE id = '7';   -- matches id = 7
-- But two expressions with no column affinity do not convert
SELECT '7' = 7;                       -- 0

STRICT tables, the actual fix

STRICT (3.37+) makes the declared type a real constraint. Add it after the closing parenthesis.

CREATE TABLE users(
  id    INTEGER PRIMARY KEY,
  email TEXT NOT NULL,
  age   INTEGER,
  score REAL,
  raw   BLOB,
  meta  ANY
) STRICT;

INSERT INTO users(email, age) VALUES('a@b.c', 'thirty');
-- Error: cannot store TEXT value in INTEGER column users.age

Rules for a STRICT table:

- Every column must have a declared type, and it must be one of exactly six: INT, INTEGER, REAL, TEXT, BLOB, ANY. - NUMERIC, VARCHAR(10), DECIMAL, BOOLEAN, and DATETIME are all rejected at CREATE TABLE time. - A value that does not match the column's type is an error, except that an integer literal into a REAL column is converted, and a text value that looks like a number is not converted for you. - NOT NULL is enforced on PRIMARY KEY columns too, which is not the case otherwise. - ANY stores any of the five classes with no conversion whatsoever, which is stricter than a normal untyped column because it does not even apply BLOB affinity quirks.

CREATE TABLE bad(a NUMERIC) STRICT;
-- Error: unknown datatype for bad.a: "NUMERIC"
CREATE TABLE bad2(a VARCHAR(10)) STRICT;
-- Error: unknown datatype for bad2.a: "VARCHAR(10)"

Make every new table STRICT. It costs one keyword, it catches the class of bug that dynamic typing invites, and it is what every other SQL engine already does. Note that STRICT does not retro-fix an existing table, and it cannot be added with ALTER TABLE, so an existing table needs the twelve-step rebuild.

WITHOUT ROWID and STRICT combine in either order.

CREATE TABLE kv(k TEXT PRIMARY KEY, v TEXT) STRICT, WITHOUT ROWID;

No boolean, no date, no decimal

SQLite has no dedicated type for the three things applications need most.

SELECT TRUE, FALSE, typeof(TRUE);   -- 1|0|integer
SELECT 1 IS TRUE;                   -- 1

CREATE TABLE money(
  cents INTEGER NOT NULL,           -- never REAL for currency
  status TEXT NOT NULL CHECK(status IN ('pending','paid','void')),
  active INTEGER NOT NULL DEFAULT 1 CHECK(active IN (0,1)),
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
) STRICT;

Storing currency as REAL is a bug in every database, and here it is easier to do by accident because DECIMAL(10,2) looks like it works. It stores an 8-byte float.

SELECT 0.1 + 0.2 = 0.3;      -- 0
SELECT 0.1 + 0.2;            -- 0.30000000000000004