SQLite: datatype mismatch

What it means

SQLite is relaxed about types: an INTEGER column will store text if the text doesn’t look like a number. The exception is the rowid, the number that identifies each row. A column declared exactly INTEGER PRIMARY KEY is the rowid under another name, and it can hold only whole numbers. Anything else fails with SQLITE_MISMATCH (code 20) and the message datatype mismatch, which names no table or column.

SQLite converts what it can first. '10', ' 12' and 11.0 are stored as integers. 'abc', a UUID, '', 1.5 and a BLOB fail. NULL, or leaving the column out, gives the next free id.

The declaration has to be INTEGER exactly: INT PRIMARY KEY or BIGINT PRIMARY KEY is an ordinary column that happily stores 'abc'.

STRICT tables (SQLite 3.37.0 and later) check every column’s type, and say so differently: cannot store TEXT value in INTEGER column payments.amount. That’s SQLITE_CONSTRAINT (19), extended code SQLITE_CONSTRAINT_DATATYPE (3091).

Common causes

A UUID or other text id going into an INTEGER PRIMARY KEY: ids from another system, or a model that uses string ids against a table created with integer ones.
A CSV header row. The sqlite3 shell’s .import into an existing table treats every line as data, so the header’s id lands in the id column.
An empty string instead of NULL for a new row’s id, from a form or a CSV field.
A decimal id, such as 1.5, often from a spreadsheet or JSON number.
A wrong type in a STRICT table: 'twelve' or 12.5 for an INTEGER column. Text that converts cleanly, like '12', is accepted.

How to fix it

Leave the id to SQLite

For a new row, leave the column out of the INSERT or pass NULL, and SQLite picks the next id:

INSERT INTO customers (name) VALUES ('Linus');

Convert empty strings to NULL in your code before you insert.

Use a text key for text ids

If ids are UUIDs or other strings, the column should be text:

CREATE TABLE customers (
  id TEXT PRIMARY KEY NOT NULL,
  name TEXT NOT NULL
);

Add NOT NULL: SQLite allows NULL in a primary key that isn’t INTEGER PRIMARY KEY. Changing an existing column’s type takes a table rebuild (the 12 steps in SQLite’s documentation).

Skip the header when importing

.import --csv --skip 1 people.csv people

Without --skip 1, .import uses the header for column names only when it creates the table.

Store the right type in STRICT tables

Convert values before you insert: whole numbers for INTEGER, and for money, cents as an integer rather than 12.50. A column declared ANY in a STRICT table accepts every type. STRICT tables accept only INT, INTEGER, REAL, TEXT, BLOB and ANY as column types; anything else fails when you create the table, with unknown datatype for <table>.<column>.

Avoid CAST(x AS INTEGER) as a quick fix: CAST('abc' AS INTEGER) is 0, which stores a wrong value instead of failing.

Reproduce it

macOS /usr/bin/sqlite3, SQLite 3.51.0, with customers (id INTEGER PRIMARY KEY, name TEXT):

sqlite3 shop.db "INSERT INTO customers (id, name) VALUES ('abc', 'Bad');"

Error: stepping, datatype mismatch (20)

The shell adds “Error: stepping,” and the result code; SQLite’s message is datatype mismatch. A UUID, '', 1.5, x'01' and UPDATE customers SET id = 'x' failed the same way; '10', ' 12' and 11.0 were stored as integers. In a table declared id INT PRIMARY KEY, 'abc' was stored as text.

Importing a CSV file with a header into an existing table:

people.csv:1: INSERT failed: datatype mismatch

The two data rows were imported; with --skip 1 the header was skipped.

A STRICT table, payments (id INTEGER PRIMARY KEY, amount INTEGER NOT NULL, note TEXT) STRICT:

Error: stepping, cannot store TEXT value in INTEGER column payments.amount (19)
Error: stepping, cannot store REAL value in INTEGER column payments.amount (19)
Error: in prepare, unknown datatype for bad.d: "DATETIME"

Those were 'twelve', '12.50' (and 12.5), and CREATE TABLE bad (d DATETIME) STRICT. '12' was stored as the integer 12. Through Python’s sqlite3 module (SQLite 3.53.4), the rowid case raised sqlite3.IntegrityError: datatype mismatch with sqlite_errorname SQLITE_MISMATCH.

In Inlet

Grid edits, including new rows, are staged until you commit (⌘S), and Review shows the exact SQL first, so you can see the value going into the id column before SQLite sees it.

datatype mismatch