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 Type | Description |
|---|---|
| NULL | Represents a NULL value. |
| INTEGER | A signed integer, stored in 0, 1, 2, 3, 4, 6, or 8 bytes depending on the value's magnitude. |
| REAL | A floating point value, stored as an 8-byte IEEE floating point number. |
| TEXT | A text string, stored in the database encoding UTF-8, UTF-16BE, or UTF-16LE. |
| BLOB | A 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.