Summary: in this tutorial, you will learn about SQLite data types system and its related concepts such as storage classes, manifest typing, and type affinity.
Introduction to SQLite data types
If you come from other database systems such as MySQL, PostgreSQL, etc., you notice that they use static typing. It means when you declare a column with a specific data type, that column can store only data with declared data type.
Different from other database systems, SQLite uses dynamic type system. For example, the value stored in a column determines its data type, not the column’s data type.
In addition, you don’t have to declare a specific data type for a column when you create a table. In case you declare a column with the integer data type, you can store any kind of data types such as text, blob, etc., SQLite will not complain about this.
SQLite provides five primitive data types which referred to as storage classes.
The concept of storage classes describes the format SQLite uses to store data on disk. A storage class is more general than a data type e.g.,
INTEGER storage class includes 6 different types of integers. In most cases, you can use storage classes and data type interchangeably.
The following table illustrates 5 storage classes in SQLite:
|NULL||NULL values mean missing information or unknown.|
|INTEGER||Integer values are whole numbers (either positive or negative). An integer can have variable sizes such as 1, 2,3, 4, or 8 bytes.|
|REAL||Real values are real numbers with decimal values that use 8-byte floats.|
|TEXT||TEXT is used to store character data. The maximum length of TEXT is unlimited. SQLite supports various character encodings.|
|BLOB||BLOB stands for a binary large object that can be used to store any kind of data. The maximum size of BLOBs is unlimited.|
Based on the format of a value, SQLite determines its data type based on the following rules:
- If a literal has no enclosing quotes and decimal point or exponent, SQLite assigns the INTEGER storage class.
- If a literal is enclosed by single or double quotes, SQLite assigns the TEXT storage class.
- If a literal does not have quote nor decimal point nor exponent, SQLite assigns REAL storage class.
- If a literal is NULL without quotes, it assigned NULL storage class.
- If a literal has the X’ABCD’ or x ‘abcd’, SQLite assigned BLOB storage class.
SQLite does not support built-in date and time storage classes. However, you can use the TEXT, INT, or REAL to store date and time values. For detailed information on how to handle date and time values, check it out the SQLite date and time tutorial.
SQLites provides the
typeof() function that allows you to check the storage class of a value based on its format. See the following statement:
A single column in SQLite can store mixed data types. See the following example.
First, we create a new table named
test_datatypes for testing.
CREATE TABLE test_datatypes (
id INTEGER PRIMARY KEY,
Second, we insert data into the
INSERT INTO test_datatypes (val)
Third, we use the
typeof() function to get the data type of each value stored in the
You may ask how SQLite sorts data in a column with different storage classes like val column above.
To resolve this, SQLite provides a set of rules when it comes to sorting.
- NULL storage class has the lowest value. It less than any other value. Between NULL values, there is no order.
- The next higher storage classes are INTEGER and REAL. SQLite compares INTEGER and REAL numerically.
- The next higher storage class is TEXT. SQLite uses the collation of TEXT values when it compares the TEXT values.
- The highest storage class is BLOB. SQLite uses C memcmp() function to compare BLOB values.
When you use the ORDER BY clause to sort the data in a column with different storage classes, SQLite performs the following steps:
- First, group values based on storage class: NULL, INTEGER and REAL, TEXT, and BLOB.
- Second, sort the values in each group.
Let’s sort the data in the
test_datatypes table to see sorting mixed values in action.
ORDER BY val;
SQLite manifest typing & type affinity
Other important concepts related to SQLite data types are manifest typing and type affinity.
Manifest typing means two things:
- First, a column can have an associated data type.
- Second, the data type of the column can be inferred from values it stores.
Type affinity means that…
Type affinity of a column is recommended the type of data that column stores. It is recommended, not required, therfore a column can store any type of data.
In this tutorial, you have learned about SQLite data types and some important concepts including storage classes, manifest typing, and type affinity.