SQLite Type Affinity: How Column Types Actually Behave

Affinity Is a Preference, Not a Rule

SQLite is dynamically typed. A value carries its own storage class (NULL, INTEGER, REAL, TEXT, BLOB), and a column's declared type doesn't strictly constrain what you can put in it. What the declared type does is give the column an affinity — a preferred storage class SQLite tries to convert incoming values to.

Watch what happens when affinity isn't enough to stop a mismatch:

CREATE TABLE items (
    id INTEGER,
    name TEXT
);

INSERT INTO items VALUES (1, 'apple');
INSERT INTO items VALUES ('two', 'banana');

SELECT id, typeof(id), name FROM items;

The second row stores the string 'two' in an INTEGER column. SQLite tried to convert 'two' to a number, couldn't (it's not numeric), and stored it as TEXT anyway. typeof() reveals the actual storage class of each value — and it's not always what the column declaration suggests.

This surprises people coming from Postgres or MySQL. It's by design.

The Five Affinities

Every column in a non-STRICT table gets exactly one of these:

• TEXT — prefers strings.
• NUMERIC — prefers numbers, but accepts text if it can't convert.
• INTEGER — like NUMERIC, but stores values without a fractional part as integers.
• REAL — prefers floating-point numbers.
• BLOB — no preference, stores whatever you give it.

BLOB affinity is also called "no affinity" — it's what you get when you don't declare a type at all.

CREATE TABLE samples (
    a,                 -- BLOB affinity (no type declared)
    b TEXT,            -- TEXT
    c INTEGER,         -- INTEGER
    d REAL,            -- REAL
    e NUMERIC          -- NUMERIC
);

INSERT INTO samples VALUES ('42', '42', '42', '42', '42');
SELECT typeof(a), typeof(b), typeof(c), typeof(d), typeof(e) FROM samples;

Same input — the string '42' — five different stored types. Each column converted (or didn't) according to its affinity.

How SQLite Picks an Affinity From Your Declaration

Here's the part that trips people up: SQLite doesn't have a fixed list of "valid" types. You can write almost anything after a column name, and SQLite figures out the affinity by scanning the text for substrings, in this order:

1. Contains INT → INTEGER
2. Contains CHAR, CLOB, or TEXT → TEXT
3. Contains BLOB, or no type at all → BLOB
4. Contains REAL, FLOA, or DOUB → REAL
5. Anything else → NUMERIC

That's the whole algorithm. It explains a lot of weirdness:

CREATE TABLE quirks (
    a VARCHAR(255),       -- TEXT  (contains CHAR)
    b BIGINT,             -- INTEGER (contains INT)
    c DOUBLE PRECISION,   -- REAL (contains DOUB)
    d DECIMAL(10,2),      -- NUMERIC (matches none of the above)
    e BOOLEAN,            -- NUMERIC (matches none of the above)
    f DATETIME,           -- NUMERIC (matches none of the above)
    g FLOATING_POINTS,    -- INTEGER (contains INT — surprise!)
    h GIBBERISH           -- NUMERIC (matches none of the above)
);

SELECT name, type FROM pragma_table_info('quirks');

FLOATING_POINTS becomes INTEGER because the substring INT appears in POINTS. The first matching rule wins, top to bottom. This is why blindly copying types from another database can give you something different than you expected.

Affinity in Action: Conversions on Insert

Affinity matters most when SQLite decides whether to convert your value or store it as-is. The rules:

• TEXT affinity: numbers and BLOBs get converted to text.
• NUMERIC, INTEGER, REAL affinities: text that looks like a number gets converted; text that doesn't stays text.
• BLOB affinity: nothing is converted.

CREATE TABLE conv (
    n NUMERIC,
    t TEXT,
    b BLOB
);

INSERT INTO conv VALUES ('123', 456, 789);
INSERT INTO conv VALUES ('12.5', 7.5, 'hello');
INSERT INTO conv VALUES ('hello', 'world', 'world');

SELECT n, typeof(n), t, typeof(t), b, typeof(b) FROM conv;

Row by row:

• '123' in a NUMERIC column becomes the integer 123. The text-to-number conversion succeeded and was lossless.
• '12.5' becomes the real 12.5.
• 'hello' in NUMERIC stays as text — no number to convert to.
• The TEXT column converts numbers into their string form.
• The BLOB column stores everything exactly as given, type and all.

The INTEGER vs REAL Wrinkle

INTEGER affinity behaves almost identically to NUMERIC, with one twist: a value like 3.0 that has no real fractional part gets stored as the integer 3 to save space.

CREATE TABLE nums (
    i INTEGER,
    n NUMERIC
);

INSERT INTO nums VALUES (3.0, 3.0);
INSERT INTO nums VALUES (3.5, 3.5);

SELECT i, typeof(i), n, typeof(n) FROM nums;

3.0 lands as INTEGER in both columns — the optimization happens for NUMERIC too. 3.5 keeps its fractional part and stays REAL. The takeaway: don't rely on typeof() to tell you whether a column was declared INTEGER or REAL. It tells you what's actually stored, which can vary row by row.

When Affinity Bites You

The flexibility is convenient until it isn't. Two failure modes show up in real code:

1. Bad data sneaks in. If your application has a bug that sends 'N/A' to an INTEGER column, SQLite stores it. Later queries that do arithmetic on the column return strange results or NULL. No error, no warning, just silent corruption.

2. Comparisons get weird. Sorting and equality checks treat values of different storage classes differently:

CREATE TABLE mixed (val INTEGER);
INSERT INTO mixed VALUES (10), (2), ('100'), ('20'), (3);

SELECT val, typeof(val) FROM mixed ORDER BY val;

Integers sort numerically, then text values sort lexicographically — and they sort after all the numbers. So you get 2, 3, 10 (the integers in numeric order), then '20', '100' (the strings in alphabetical order). Not what most people want.

If you control the inserts and validate carefully, regular tables are fine. If you don't — or you just want the database to enforce types for you — there's a better option.

Next: STRICT Tables

SQLite 3.37 added STRICT tables, which turn affinity off and reject values that don't match the declared type. They give you the dynamic-typing-by-default behavior when you want it, and Postgres-style enforcement when you don't. That's the next page.