Declared Types and Type Affinity Rules

Learning outcomes

Lesson 1 established that runtime values have storage classes. Now we add the other half of SQLite’s ordinary typing model: column affinity. An affinity is a preference SQLite derives from the declared type name. It can convert values during insertion and can influence comparisons, but it is not the same as a rigid constraint.

A declared type name is input to an ordered rule set

For ordinary non-STRICT tables, SQLite does not maintain a catalog of every SQL type name used by other products. Instead, it examines the text of the declared type and assigns one of five affinities. The rules are applied in order; the first match wins.

The matching is based on substrings, not on a semantic dictionary. That is why unusual names can produce surprising affinities. The order also matters: a name containing both CHAR and INT receives INTEGER affinity because the INT rule is checked first.

Familiar type names may not mean what they mean elsewhere

Type names such as BOOLEAN, DATE, and DECIMAL(10,2) are accepted in ordinary SQLite DDL, but accepting the spelling does not create dedicated Boolean, date, or exact-decimal storage classes. Their spelling falls through to NUMERIC affinity. VARCHAR contains CHAR, so it gets TEXT affinity.

Inspect the declaration, then derive affinity yourself

PRAGMA table_xinfo reports the declared type string. It does not add a separate “affinity” column for you. Therefore an inspection tool or migration review must apply SQLite’s affinity rules to that declaration if it needs the derived affinity.

DROP TABLE IF EXISTS affinity_names;CREATE TABLE affinity_names (    as_boolean BOOLEAN,    as_date    DATE,    as_varchar VARCHAR(40),    as_decimal DECIMAL(10,2),    as_point   POINT,    as_custom  FIELDNOTE_CODE,    as_blob    BLOB,    as_double  DOUBLE PRECISION);PRAGMA table_xinfo('affinity_names');

Read each returned type value and run it through the ordered rules. This exercise is more durable than memorizing vendor-like names because the same technique works for arbitrary legacy schemas.

Affinity is applied when values are inserted

When TEXT that looks like a well-formed integer or real enters a NUMERIC- or INTEGER-affinity column, SQLite attempts to store it numerically. REAL affinity similarly prefers numeric representation, while TEXT affinity prefers text. NULL and BLOB values are not converted by these ordinary affinity rules.

DELETE FROM affinity_names;INSERT INTO affinity_names VALUES (    '00123', '00123', '00123', '00123',    '00123', '00123', '00123', '00123');SELECT    typeof(as_boolean), as_boolean,    typeof(as_varchar), as_varchar,    typeof(as_decimal), as_decimal,    typeof(as_point),   as_point,    typeof(as_blob),    as_blob,    typeof(as_double),  as_doubleFROM affinity_names;

Expected logical behavior: BOOLEAN, DECIMAL, POINT, and the custom FIELDNOTE_CODE declaration all prefer numeric storage and turn '00123' into INTEGER 123. VARCHAR preserves TEXT 00123. BLOB affinity leaves the incoming TEXT value as TEXT. DOUBLE PRECISION prefers REAL and stores a numeric representation such as 123.0. The leading zeros disappear wherever numeric conversion succeeds.

NUMERIC affinity can choose INTEGER or REAL after parsing text

NUMERIC affinity is not synonymous with REAL. If text represents an integer exactly, SQLite prefers INTEGER. If it represents a non-integer real value, SQLite uses REAL. This is observable with scientific notation too.

DROP TABLE IF EXISTS numeric_probe;CREATE TABLE numeric_probe (value NUMERIC);INSERT INTO numeric_probe(value) VALUES    ('42'),    ('42.5'),    ('3.0e+5'),    ('not-a-number');SELECT rowid, quote(value), typeof(value)FROM numeric_probeORDER BY rowid;

Expected storage classes are INTEGER for '42', REAL for '42.5', INTEGER for '3.0e+5' because 300000 is exactly integral, and TEXT for 'not-a-number' because numeric conversion is not available. This is one reason a DECIMAL-looking declaration does not by itself guarantee fixed decimal scale.

Comparisons can apply affinity too

Affinity is not only an insertion concept. Before some comparisons, SQLite may apply an operand’s affinity to the other operand. Keep the example small: one column has TEXT affinity, one has NUMERIC affinity, and both receive the source text '2'.

DROP TABLE IF EXISTS comparison_probe;CREATE TABLE comparison_probe (    text_value    TEXT,    numeric_value NUMERIC);INSERT INTO comparison_probe VALUES ('2', '2');SELECT text_value,       typeof(text_value),       text_value < 10 AS text_less_than_10,       text_value = 2  AS text_equals_2FROM comparison_probe;SELECT numeric_value,       typeof(numeric_value),       numeric_value < 10 AS numeric_less_than_10,       numeric_value = 2  AS numeric_equals_2FROM comparison_probe;

The TEXT-affinity value is stored as text. For text_value < 10, the numeric literal can be converted to text for the comparison, so lexical ordering makes '2' < '10' false. For equality, 2 can become text '2', so equality is true. The NUMERIC-affinity column stores INTEGER 2, making 2 < 10 and 2 = 2 both true.

CAST is an explicit conversion on an expression

Column affinity is attached to a column and participates automatically in specific contexts. CAST(expression AS type-name) is different: it is an explicit SQL expression that requests conversion now and produces a converted result value.

SELECT CAST('0042' AS INTEGER) AS n,       typeof(CAST('0042' AS INTEGER)) AS n_type;SELECT CAST(7.0 AS INT) AS as_int,       typeof(CAST(7.0 AS INT)) AS as_int_type,       CAST(7.0 AS NUMERIC) AS as_numeric,       typeof(CAST(7.0 AS NUMERIC)) AS as_numeric_type;

The first result is INTEGER 42. The second pair illustrates a documented distinction: casting 7.0 to an INT-like type produces INTEGER 7, while casting 7.0 to NUMERIC can preserve the REAL 7.0 representation. A CAST does not alter the table schema and does not change the affinity of a stored column.

Matrix lab: declaration, input, stored class, displayed value

Create a compact evidence table by inserting several source forms into columns with different affinities. Then reshape the observations with UNION ALL so each result row states the declaration you are testing.

DROP TABLE IF EXISTS affinity_lab;CREATE TABLE affinity_lab (    sample_id INTEGER PRIMARY KEY,    text_col    VARCHAR(20),    integer_col INTEGER,    numeric_col DECIMAL(10,2),    real_col    DOUBLE,    blob_col    BLOB);INSERT INTO affinity_lab VALUES    (1, '00123', '00123', '00123', '00123', '00123'),    (2, '12.50', '12.50', '12.50', '12.50', '12.50'),    (3, 'offline', 'offline', 'offline', 'offline', 'offline');SELECT sample_id, 'VARCHAR(20)' AS declaration,       typeof(text_col) AS stored_type, quote(text_col) AS valueFROM affinity_labUNION ALLSELECT sample_id, 'INTEGER', typeof(integer_col), quote(integer_col)FROM affinity_labUNION ALLSELECT sample_id, 'DECIMAL(10,2)', typeof(numeric_col), quote(numeric_col)FROM affinity_labUNION ALLSELECT sample_id, 'DOUBLE', typeof(real_col), quote(real_col)FROM affinity_labUNION ALLSELECT sample_id, 'BLOB', typeof(blob_col), quote(blob_col)FROM affinity_labORDER BY sample_id, declaration;

Before running the query, predict each typeof(). Then compare prediction with evidence. Record any mismatch in your notes and identify which ordered affinity rule or numeric conversion rule explains it.

Failure patterns and safe corrections

Summary and bridge to STRICT tables

Declared type names matter because they deterministically produce affinity. Affinity is a conversion preference, not a universal rigid constraint: values that cannot be converted may remain in another storage class. That flexibility is often useful, but some tables represent an application contract that should reject incompatible values. Lesson 3 applies strictness exactly where you choose it.

References

• SQLite — Datatypes In SQLite.
• SQLite — CREATE TABLE.
• SQLite — Core Functions.
• SQLite — Expressions and CAST.
• SQLite — STRICT Tables.
• SQLite — Release History.