Menu

SQLite NUMERIC Affinity: Storage, Conversion, and DECIMAL

In SQLite, NUMERIC is a column type affinity, not a separate storage class or an arbitrary-precision decimal type. A column declared NUMERIC prefers to convert numeric-looking text to an INTEGER or REAL value, but it can still hold values from any of SQLite’s five storage classes.

This matters when moving a schema from MySQL or PostgreSQL: NUMERIC(10,2) in SQLite does not enforce 10 digits of precision or two decimal places.

NUMERIC affinity versus storage class

SQLite has five storage classes: NULL, INTEGER, REAL, TEXT, and BLOB. It also has five column affinities: TEXT, NUMERIC, INTEGER, REAL, and BLOB. The two lists are not interchangeable: NUMERIC is an affinity, not a storage class.

For ordinary (non-STRICT) tables, NUMERIC affinity tries to convert well-formed numeric text to INTEGER when possible, or to REAL otherwise. Text that is not a valid number stays TEXT; NULL and BLOB values are not converted. Use typeof() to see the actual storage class.

Example: inspect stored NUMERIC values

Run this complete example in SQLite:

CREATE TABLE amounts (
    value NUMERIC(10,2)
);

INSERT INTO amounts (value) VALUES
    ('12.50'),
    ('3.0e+5'),
    ('not a number'),
    (NULL),
    (123.456);

SELECT quote(value) AS stored_value,
       typeof(value) AS storage_class
FROM amounts;
stored_value     storage_class
---------------  -------------
12.5             real
300000           integer
'not a number'   text
NULL             null
123.456          real

The text '3.0e+5' becomes the integer 300000 because it represents a whole number within the signed 64-bit range. The value 123.456 is not rounded to two decimal places: SQLite ignores the (10,2) precision and scale for this affinity.

NUMERIC versus INTEGER and REAL

NUMERIC and INTEGER affinity usually convert inserted values in the same way. Their difference is visible with CAST:

SELECT typeof(CAST(4.0 AS NUMERIC)) AS numeric_cast,
       typeof(CAST(4.0 AS INTEGER)) AS integer_cast;
numeric_cast  integer_cast
------------  ------------
real          integer

A column with REAL affinity prefers floating-point representation even for whole numbers. Floating-point values use IEEE 754 binary64 and cannot represent most decimal fractions exactly.

Should NUMERIC store prices?

Not when exact decimal arithmetic is required. Declaring NUMERIC(10,2) does not provide fixed-point math or enforce a two-decimal scale. Store amounts as integer minor units (for example, cents) when the range and currency rules permit, or use an appropriate decimal-arithmetic extension or application library.

SQLite STRICT tables enforce a limited set of declared types; NUMERIC is not an allowed declared type in a STRICT table. See the SQLite STRICT tables documentation.

Try the example in the SQLite Playground, then change the inserted values and compare typeof() results. For more context, see SQLite Data Types, the official type affinity rules, and floating-point behavior.