In SQLite, how does a column's declared type name decide its type affinity, and what does that affinity do to inserted text?
answer
- a preference, not a requirement
- substring rules, checked in order
- INT wins over CHAR
- convert only when lossless
- STRING is not TEXT
basics
~20 sSQLite picks a column's affinity from substrings of its declared type, in order: INT, then CHAR/CLOB/TEXT, then BLOB or none, then REAL/FLOA/DOUB, else NUMERIC. Affinity converts a value only when that loses nothing; otherwise the value is stored unchanged.
solid answer
~40 sIn an ordinary SQLite table the declared type only sets an *affinity*, the storage class the column prefers. SQLite checks substrings in order: a name containing `INT` gives INTEGER affinity, then `CHAR`, `CLOB` or `TEXT` gives TEXT, then `BLOB` or no type gives BLOB, then `REAL`, `FLOA` or `DOUB` gives REAL, and anything else is NUMERIC. So `VARCHAR(20)` is TEXT with the length ignored, `DECIMAL(10,2)` and `DATE` are NUMERIC, `STRING` is NUMERIC and `FLOATING POINT` is INTEGER. On insert, a NUMERIC or INTEGER column turns well-formed numeric text such as `'42'` into a number, but text like `'1,200'` or `'n/a'` is stored as text with no error. A TEXT column turns numbers into text, and a BLOB column converts nothing.
code
sql · 13 linesCREATE TABLE import_rows(
sku VARCHAR(20), -- TEXT affinity: contains CHAR
price DECIMAL(10,2), -- NUMERIC affinity: rule 5
qty INT, -- INTEGER affinity: contains INT
sold_on DATE, -- NUMERIC affinity: rule 5
note STRING -- NUMERIC affinity, not TEXT
);
INSERT INTO import_rows VALUES ('00123', '20.00', '42', '2026-03-15', '007');
INSERT INTO import_rows VALUES (123, '$3.50', '1,200', '20260315', 'n/a');
SELECT quote(sku), quote(price), quote(qty), quote(sold_on), quote(note)
FROM import_rows;
-- '00123'|20|42|'2026-03-15'|7
-- '123'|'$3.50'|'1,200'|20260315|'n/a'go deeper
Recall that the declared type only suggests a class, that numeric-looking text is converted and other text is stored as is, and that typeof() shows the result.
Walk through the five ordered substring rules with awkward names like STRING and FLOATING POINT, and say exactly what NUMERIC, TEXT and BLOB affinity do to text and numbers.
Diagnose a query that misses or includes rows because stray TEXT values sit in a numeric column, explain the comparison rule behind it, and clean and enforce the column.
Decide when a team should rely on affinity, add typeof() CHECK constraints, or require STRICT tables, weighing import tolerance against silent bad data.
## Affinity: a preference, not a rule In an ordinary (non-STRICT) SQLite table every column has a **type affinity**, the storage class it prefers. SQLite applies the affinity when a value is stored: if the value can be converted to the preferred class without losing information, it is converted; if not, it is stored exactly as it arrived, and **no error is raised**. That is how a column declared `INT` can end up holding the text `'n/a'`. The five affinities are TEXT, NUMERIC, INTEGER, REAL and BLOB (BLOB affinity was once called NONE). ## How the declared type picks the affinity SQLite does not look the type name up in a list of known types. It applies five substring rules, in this order, and the first match wins: 1. The name contains `INT`: **INTEGER** affinity. 2. The name contains `CHAR`, `CLOB` or `TEXT`: **TEXT** affinity. 3. The name contains `BLOB`, or there is no type at all: **BLOB** affinity. 4. The name contains `REAL`, `FLOA` or `DOUB`: **REAL** affinity. 5. Anything else: **NUMERIC** affinity. The rules produce some well-known surprises: - `VARCHAR(255)` and `NVARCHAR(100)` are TEXT because they contain `CHAR`, and the number in parentheses is ignored. SQLite enforces no length below its global string limit, so a 2,000-character value fits in a `VARCHAR(50)` column. - `DECIMAL(10,2)`, `BOOLEAN`, `DATE` and `DATETIME` all fall through to NUMERIC. - `STRING` is NUMERIC, not TEXT, because it matches none of the first four rules. - `FLOATING POINT` is INTEGER, because "POINT" contains `INT` and rule 1 is checked first. ## What each affinity does on insert | Affinity | Well-formed numeric text | Numeric values | Non-numeric text | |---|---|---|---| | TEXT | kept as TEXT | converted to TEXT | kept as TEXT | | NUMERIC | becomes INTEGER, or REAL if not integral | a REAL with no fractional part becomes INTEGER | kept as TEXT | | INTEGER | same as NUMERIC | same as NUMERIC | kept as TEXT | | REAL | becomes REAL | integers are forced to REAL | kept as TEXT | | BLOB | no conversion | no conversion | no conversion | NULLs and BLOB values are never converted by any affinity. A few finer points matter in practice: - "Well-formed" means a decimal integer or real literal. Hexadecimal text such as `'0x1F'` is not treated as well-formed, so it stays TEXT. - Integer text too large for a 64-bit signed integer becomes a REAL. - `'3.0e+5'` becomes the integer 300000 in a NUMERIC column, because the value is integral. - INTEGER and NUMERIC affinity behave identically for columns; they differ only inside `CAST`, where `CAST(4.0 AS INT)` gives 4 and `CAST(4.0 AS NUMERIC)` keeps 4.0. ## The spreadsheet import, column by column Picture a price list exported from a spreadsheet into `import_rows(sku VARCHAR(20), price DECIMAL(10,2), qty INT, sold_on DATE, note STRING)`: - **sku** (TEXT): `'00123'` stays the text `'00123'`; if the exporter wrote the number 123, it is stored as the text `'123'`, and the leading zeros were already lost before SQLite saw them. - **price** (NUMERIC): `'19.99'` becomes the REAL 19.99, `'20.00'` becomes the INTEGER 20, and `'$3.50'` stays TEXT. - **qty** (INTEGER): `'42'` becomes 42, but `'1,200'` stays TEXT because a thousands separator is not part of a numeric literal. - **sold_on** (NUMERIC): `'2026-03-15'` stays TEXT, while a compact `'20260315'` becomes the INTEGER 20260315. - **note** (NUMERIC, because of `STRING`): `'007'` becomes the integer 7, losing the zeros the user typed. Nothing failed, so nothing was reported. After any import, audit each column with a query such as `SELECT typeof(qty), count(*) FROM import_rows GROUP BY 1;`. ## Affinity also acts when values are compared Before a comparison, SQLite may convert one operand. If one side is a column with INTEGER, REAL or NUMERIC affinity and the other side has TEXT, BLOB or no affinity, NUMERIC affinity is applied to the other side; if one side has TEXT affinity and the other has none, TEXT affinity is applied. That is why `qty = '42'` matches the stored integer 42. The conversion cannot rescue a stored value that stayed TEXT, though. An INTEGER or REAL value always ranks below any TEXT value, so the row holding `'1,200'` passes `qty > 5` and never passes `qty < 5`. The fix is to clean the stray values and then enforce the type, either with a constraint such as `CHECK (typeof(qty) IN ('integer', 'null'))`, which sees the value after affinity has been applied, or by moving the data into a STRICT table.
- After that SQLite import, why does WHERE qty > 5 return the row whose qty is '1,200'?`'1,200'` was not a well-formed number, so it stayed TEXT. The comparison applies the column's numeric affinity only to the literal 5, which is already an integer, and SQLite ranks every INTEGER or REAL value below every TEXT value. So that row passes `> 5` and never `< 5`. Find such rows with `typeof(qty) <> 'integer'`, fix them, then enforce the type.
- In SQLite, how does CAST('12abc' AS INTEGER) differ from inserting '12abc' into an INTEGER-affinity column?`CAST` takes the longest prefix that reads as an integer and ignores the rest, so it returns 12, and it returns 0 when there is no numeric prefix at all. Column affinity converts only when nothing is lost, so the inserted `'12abc'` stays TEXT. When cleaning data, check values first: a blind `CAST` turns `'$3.50'` into 0.
- In SQLite, what is the difference between INTEGER and NUMERIC affinity?As column affinities they behave the same: both turn well-formed numeric text into INTEGER or REAL and store integral reals as integers. The difference shows only in `CAST`: `CAST(4.0 AS INT)` returns the integer 4, while `CAST(4.0 AS NUMERIC)` leaves the floating-point 4.0.
- VARCHAR(20) makes SQLite reject or truncate a longer string
- A column declared STRING gets TEXT affinity
- Inserting 'n/a' into an INT column of an ordinary table raises a type error
- DECIMAL(10,2) in SQLite stores exact two-decimal values
- FLOATING POINT gives REAL affinity because it names a float type