Skip to content
Tutorials Center
My learning

Topics

Lesson 4 of 33 ยท Types and Tables

Dynamic Typing and Type Affinity

Understand SQLite storage classes (NULL, INTEGER, REAL, TEXT, BLOB), type affinity rules, typeof() and how values are coerced, plus the surprises that catch beginners.

Most databases are statically typed: a column declared INTEGER rejects the text 'hello'. SQLite is different. It is dynamically typed: the type belongs to each value you store, and a column's declared type is only a preference. This is the single biggest difference between SQLite and databases like PostgreSQL or MySQL, and it explains many surprises.

Once you know the rules, they are short and predictable. This lesson walks through them, and the next lessons show how to tighten them with constraints and STRICT tables.

The five storage classes

Every value SQLite stores belongs to exactly one of five storage classes:

Storage class What it holds
NULL The absence of a value
INTEGER A whole number, stored in 1 to 8 bytes as needed
REAL A floating-point number (8 bytes)
TEXT A string, stored as UTF-8 (or UTF-16)
BLOB Raw bytes, stored exactly as given

There is no boolean, no date and no decimal class. Booleans are integers 0 and 1, and dates are text or numbers (see Dates, Times and Booleans).

The function typeof() tells you which class a value has right now:

SQL typeof on literals

Press Run (or Ctrl + Enter) to see the result here.

The results are null, integer, real, text, blob, real and integer. Look at the last two. Adding an integer and a real gives a real. Dividing two integers gives an integer (10 / 4 is 2, not 2.5), a classic trap. Make one side a real, as in 10 / 4.0, to get 2.5.

Typed values, flexible columns

Values have types Each stored value is NULL, INTEGER, REAL, TEXT or BLOB.
Columns have affinity A preference, not a rule.
Conversion on insert SQLite converts a value only when it safely can.

Check what you really stored with typeof().

1 / 2

Type affinity: what a column prefers

When you declare a column, SQLite looks at the text of the type name and assigns one of five affinities. The affinity does not forbid other types. It nudges incoming values toward a preferred class. The rules are applied in this order:

  1. The type name contains INT (such as INT, INTEGER, BIGINT) gives INTEGER affinity.
  2. It contains CHAR, CLOB or TEXT (such as VARCHAR(50), TEXT) gives TEXT affinity.
  3. It contains BLOB, or no type is given at all, gives BLOB affinity (also called "none": no conversion).
  4. It contains REAL, FLOA or DOUB gives REAL affinity.
  5. Anything else (NUMERIC, DECIMAL, BOOLEAN, DATE, DATETIME) gives NUMERIC affinity.

Because the check is on substrings, odd names behave in funny ways: POINT contains INT, so it gets integer affinity. Names such as BOOLEAN and DATETIME get numeric affinity, which is why you can declare them but SQLite has no real boolean or date type.

What affinity actually does on insert:

  • INTEGER and NUMERIC columns try to convert text that looks like a number into a number. '42' becomes the integer 42, and '3.0' becomes 3. Text that does not look numeric stays text.
  • REAL columns convert integers to floating point.
  • TEXT columns convert numbers to text.
  • BLOB (none) columns store whatever they are given.
SQL Affinity at work

Press Run (or Ctrl + Enter) to see the result here.

In row 1 the text '123' going into column i becomes an integer, the number 123 going into t becomes the text '123', and the integer going into r becomes the real 123.0. Row 2 shows the limits: 'abc' cannot become an integer, so column i just stores the text, and 'xyz' is stored as text in the REAL column. No error happens. The numeric column converts '4.50' to the real 4.5, and the BLOB-affinity column keeps the text '123' as text.

Comparisons and mixed types

Because a column can hold mixed types, SQLite needs rules for comparing them. When you compare values of different storage classes, the order is: NULL < numbers (INTEGER and REAL) < TEXT < BLOB. And before comparing, an affinity conversion may be applied: if one side is a column with numeric affinity and the other is text that looks like a number, the text is converted first.

SQL Comparing across types

Press Run (or Ctrl + Enter) to see the result here.

The first two comparisons are true (1), because a column's affinity is applied to the other side. The literal comparison 10 = '10' is false (0), since neither side is a column and an integer never equals a text value. 2 < '10' is true (1) because every number sorts before every text value. '2' < '10' is false (0): as text, '2' comes after '1', so alphabetical order wins.

The same effect shows up when sorting a column that holds both numbers and text:

SQL Sorting mixed types

Press Run (or Ctrl + Enter) to see the result here.

The order is NULL, then 2.5 and 5 (numbers), then the texts '10' and 'apple', and last the blob. NULLs sort first in ascending order in SQLite.

Surprises to watch for

Garbage in, garbage out. A column declared INTEGER happily stores 'not a number'. Without constraints, your data can drift into mixed types, and queries like SUM() then behave in odd ways (text that is not numeric counts as 0).

SQL Text hiding in a numeric column

Press Run (or Ctrl + Enter) to see the result here.

The first query shows two integers and one text value. The sum is 15: the string 'free' was treated as 0. No error, no warning. Constraints and STRICT Tables shows how to turn such mistakes into errors.

Integer overflow becomes REAL. Integers use up to 64 bits. If arithmetic goes beyond the largest 64-bit integer, 9223372036854775807, SQLite switches to a floating-point result instead of wrapping.

Declared type names are mostly decoration. VARCHAR(10) does not limit length, and DECIMAL(10,2) does not round. Both only decide the affinity (TEXT and NUMERIC respectively).

Explicit conversion with CAST. To force a type, use CAST(value AS type). It follows the same affinity rules for the type name:

SQL CAST

Press Run (or Ctrl + Enter) to see the result here.

CAST('42abc' AS INTEGER) reads the leading digits and gives 42. Text with no digits becomes 0, and 3.99 is truncated toward zero to 3, not rounded. The last column is 2.5.

Design advice

  • Use plain type names that match what you mean: INTEGER, REAL, TEXT, BLOB. They make affinity obvious.
  • Validate data in your application and in the database with CHECK constraints or STRICT tables.
  • Use typeof() whenever a result looks strange. It usually reveals text sitting where you expected a number.
  • Store money as integer cents rather than REAL, so rounding errors cannot creep in.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. How many storage classes does SQLite have?

    Every value is NULL, INTEGER, REAL, TEXT or BLOB. Dates and booleans are stored using these classes.
  2. 2. What is stored by INSERT INTO t(i) VALUES ('abc') when column i is declared INTEGER?

    Affinity only converts values that look numeric. Anything else stays as it is, unless you use a STRICT table or a CHECK constraint.
  3. 3. What does SELECT 10 / 4; return?

    Dividing two integers gives an integer. Write 10 / 4.0 or CAST one side to REAL to get 2.5.
  4. 4. Which affinity does a column declared VARCHAR(30) get?

    The type name contains CHAR, which gives TEXT affinity. The length in parentheses is ignored.
  5. 5. What helps you discover which storage class a value really has?

    typeof() returns null, integer, real, text or blob for the value it is given.

Summary

  • SQLite is dynamically typed: values have a storage class (NULL, INTEGER, REAL, TEXT, BLOB); columns only have an affinity.
  • Affinity is chosen from the declared type name (INT, CHAR/CLOB/TEXT, BLOB or none, REAL/FLOA/DOUB, otherwise NUMERIC) and converts values only when it safely can.
  • Mismatched values are stored as they are, with no error, unless you add constraints or use STRICT tables.
  • Integer division truncates, and NULL < numbers < text < blob when mixed types are compared or sorted.
  • Use typeof() to inspect values and CAST to convert them deliberately.

Next: Tables, rowid and INTEGER PRIMARY KEY looks at how rows are stored and identified.