SQLite Data Types: Storage Classes and Type Affinity

SQLite uses dynamic typing: each stored value has a storage class, while a column’s declared type determines its preferred type affinity. These are different concepts. In a regular table, the declared column type does not always determine the storage class of every value.

The five SQLite storage classes

There is no NUMERIC storage class. NUMERIC is one of the five affinities, along with TEXT, INTEGER, REAL, and BLOB. See NUMERIC affinity for why a DECIMAL(10,2) declaration does not guarantee fixed decimal precision.

How column affinity changes inserted values

In a normal SQLite table, a column’s affinity influences conversions when a value is inserted. For example, a numeric-looking string may become an integer in an INTEGER column, while a number may become text in a TEXT column.

CREATE TABLE demo (
    quantity INTEGER,
    price NUMERIC,
    label TEXT
);

INSERT INTO demo VALUES ('12', '12.50', 42);

SELECT typeof(quantity) AS quantity_type,
       typeof(price) AS price_type,
       typeof(label) AS label_type
FROM demo;

quantity_type  price_type  label_type
-------------  ----------  ----------
integer        real        text

The typeof() function reports each value’s actual storage class, not its column’s declared type. SQLite also supports STRICT tables for stronger type enforcement; their allowed declared types differ from ordinary tables.

Choose a type for your data

Use INTEGER for exact whole numbers and integer minor units such as cents. Use REAL for measurements where floating-point approximation is acceptable. Use TEXT for names and other strings, and BLOB for binary data. For dates and times, SQLite commonly uses TEXT, REAL, or INTEGER representations rather than a separate date storage class.

Run the example in the SQLite Playground and change the inserted values to observe affinity conversions. The official SQLite datatype reference explains the complete rules, including STRICT tables and type-name matching.