SQLite3 Data Types Explained

A list of all the data types available in SQLite3 and what they mean

Table of contents

SQLite data types

Data TypeDescription
NULLRepresents a NULL value.
INTEGERA signed integer, stored in 0, 1, 2, 3, 4, 6, or 8 bytes depending on the value's magnitude.
REALA floating point value, stored as an 8-byte IEEE floating point number.
TEXTA text string, stored in the database encoding UTF-8, UTF-16BE, or UTF-16LE.
BLOBA blob of data, stored exactly as input.

How does it work?

Type Affinities

While SQLite allows you to store any value in any column, regardless of the data type, it still tries to keep track of the 'type' of the data in the column through the concept of "type affinity." Every column is assigned a type affinity, depending on the declared data type of the column. The type affinity is the recommended type for data stored in that column.

Type Casting

Because SQLite is dynamically typed, it may perform some automatic type conversions for values when they are inserted into the database. This behavior is primarily guided by the type affinity of the column.

For instance:

If a column has INTEGER affinity, and you try to insert a floating-point number into that column, SQLite will try to convert it to an integer (by rounding) before storage. If a column has NUMERIC affinity, SQLite will decide on a storage class (INTEGER, REAL, TEXT, BLOB) depending on the kind of data that you try to store in that column.

Copyright Adam Richardson © 2023 - All rights reserved