SQLite NUMERIC, explained

NUMERIC is where SQLite puts every type it doesn’t have: DECIMAL, BOOLEAN, DATE, DATETIME, TIMESTAMP. It means “store it as a number if you can”. Here’s what that does to money, to yes/no values and to dates.

On this page In one paragraph What NUMERIC does DECIMAL(p,s) Booleans Dates & times Limits Affinity rules Other types

In one paragraph (ELI12)

NUMERIC is a “number if it can be” column. Give it "42" and SQLite stores the number 42; give it "3.5" and it stores 3.5; give it "hello" and it shrugs and keeps the text. Dates, yes/no values and money usually end up in NUMERIC columns, because SQLite has no special types for them.

What NUMERIC affinity does

When a value is stored in a NUMERIC column:

TEXT that looks like a number is converted — to INTEGER if it’s a whole number that fits in 64 bits, otherwise to REAL.
A REAL with no fractional part that fits in an integer is stored as INTEGER (3.0 → 3).
Everything else — non-numeric text, BLOBs, NULL — is kept as is.

CREATE TABLE n (v NUMERIC);
INSERT INTO n VALUES ('42'), ('3.50'), (3.0), ('1e3'), ('2026-09-29'), (x'01');
SELECT v, typeof(v) FROM n;
-- 42         | integer
-- 3.5        | real       ← '3.50' lost its trailing zero
-- 3          | integer
-- 1000       | integer
-- 2026-09-29 | text       ← a date is not a number
-- (blob)     | blob

DECIMAL(p,s) is not exact

DECIMAL(10,2), NUMERIC(12,4) and MONEY all get NUMERIC affinity, and the precision and scale are ignored. 19.99 is stored as the binary double nearest to it — so sums pick up the usual floating-point dust (0.1 + 0.2 = 0.30000000000000004), and '3.50' comes back as 3.5.

For exact money: store integer cents (amount_cents INTEGER), or keep the decimal string in a TEXT column and use the decimal extension (decimal_add(), decimal_sum()) where available.

Booleans

SQLite has no BOOLEAN type. BOOLEAN gets NUMERIC affinity; the keywords TRUE and FALSE (3.23+) are just 1 and 0 — which serial types 9 and 8 store in zero bytes of payload. Any non-zero number is truthy in a WHERE.

CREATE TABLE f (is_vip BOOLEAN CHECK (is_vip IN (0, 1)));
INSERT INTO f VALUES (TRUE), (0), ('1');   -- '1' is converted to 1
SELECT is_vip, typeof(is_vip) FROM f;      -- 1 integer / 0 integer / 1 integer
INSERT INTO f VALUES ('yes');              -- rejected by the CHECK

Dates and times

There is no DATE type either. DATE, DATETIME and TIMESTAMP get NUMERIC affinity, and applications pick one of three representations:

Stored as	Looks like	Good for
TEXT (ISO-8601)	`2026-09-29 14:05:00`	readable; sorts correctly as text
REAL (Julian day)	`2461313.0868`	date arithmetic in days
INTEGER (Unix time)	`1790690700`	compact; what most apps and Android use

The date functions accept all three and convert between them:

SELECT date('now'), datetime(1790690700, 'unixepoch'), julianday('2026-09-29'),
       strftime('%Y-%m', '2026-09-29 14:05'), unixepoch('2026-09-29'),
       date('2026-09-29', '+1 month', 'start of month', '-1 day');
-- 2026-09-29 | 2026-09-29 14:05:00 | 2461312.5 | 2026-09 | 1790640000 | 2026-09-30

The danger is mixing representations in one column: text sorts after every number, so a column holding both '2026-01-01' and 1790690700 sorts nonsensically. Pick one format per column; add a CHECK to enforce it.

Limits

Converted integers are 64-bit; larger numeric text becomes REAL (and loses precision past ~16 digits).
The date functions cover years 0000–9999; timezones other than UTC and localtime aren’t built in.
Hex text like '0x1A' is not converted; hex integer literals in SQL (0x1A) are.

Storage class vs. declared type vs. affinity

SQLite is dynamically typed: the type belongs to the value, not the column. Every value is one of five storage classes — NULL, INTEGER, REAL, TEXT, BLOB. A column’s declared type only sets its affinity: a preference SQLite applies when you store a value, by five rules checked in order:

#	If the declared type…	Affinity	Examples
1	contains `INT`	INTEGER	INT, INTEGER, BIGINT, SMALLINT, UNSIGNED BIG INT, INT8
2	contains `CHAR`, `CLOB` or `TEXT`	TEXT	TEXT, VARCHAR(255), CHARACTER(20), NCHAR(55), CLOB
3	contains `BLOB`, or is empty	BLOB	BLOB, (no type)
4	contains `REAL`, `FLOA` or `DOUB`	REAL	REAL, DOUBLE, DOUBLE PRECISION, FLOAT
5	anything else	NUMERIC	NUMERIC, DECIMAL(10,5), BOOLEAN, DATE, DATETIME

Order matters: CHARINT matches rule 1 before rule 2 and is INTEGER; FLOATING POINT contains INT and is INTEGER too. The viewer implements exactly these rules:

function affinityOf(declType) {
  const t = String(declType || '').toUpperCase();
  if (t.includes('INT')) return 'INTEGER';
  if (t.includes('CHAR') || t.includes('CLOB') || t.includes('TEXT')) return 'TEXT';
  if (t.includes('BLOB') || t.trim() === '') return 'BLOB';
  if (t.includes('REAL') || t.includes('FLOA') || t.includes('DOUB')) return 'REAL';
  return 'NUMERIC';
}

The other types

INTEGERwhole numbers, 0 to 8 bytes, and the rowid REAL8-byte floating point, and why 0.1 + 0.2 ≠ 0.3 TEXTUTF-8 strings, VARCHAR(n) that isn’t, collations BLOBraw bytes, untyped columns, and STRICT’s ANY NUMERICdates, booleans and DECIMAL(10,2)

Open any .sqlite, .db or .sqlite3 file in the SQLite viewer — every column header has an ELI12 button that explains its type and links here. How the viewer reads a 20 GB database without loading it: how to open a 20 GB SQLite database in the browser.