SQLite Data Types
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 and PostgreSQL, 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 of the declared data type.
Different from other database systems, SQLite uses dynamic type system. In other words, a 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 and BLOB, SQLite will not complain about this.
SQLite provides five primitive data types which are referred to as storage classes.
Storage classes describe the formats that 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 types interchangeably.
The following table illustrates 5 storage classes in SQLite:
| Storage Class | Meaning |
|---|---|
| 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 store any kind of data. The maximum size of BLOB is, theoretically, unlimited. |
SQLite determines the data type of a value based on its data type according to 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 TEXT, INT, or REAL to store date and time values. For the 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 example:
SELECT
typeof(100),
typeof(10.0),
typeof('100'),
typeof(x'1000'),
typeof(NULL);
A single column in SQLite can store mixed data types. See the following example.
First, create a new table named test_datatypes for testing.
CREATE TABLE test_datatypes (
id INTEGER PRIMARY KEY,
val
);
Second, insert data into the test_datatypes table.
INSERT INTO test_datatypes (val)
VALUES
(1),
(2),
(10.1),
(20.5),
('A'),
('B'),
(NULL),
(x'0010'),
(x'0011');
Third, use the typeof() function to get the data type of each value stored in the val column.
SELECT
id,
val,
typeof(val)
FROM
test_datatypes;
You may ask how SQLite sorts data in a column with different storage classes like val column above.
To resolve this, SQLite provides the following set of rules when it comes to sorting:
- NULL storage class has the lowest value. It is lower than any other values. 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 the BLOB. SQLite uses the C function
memcmp()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.
The following statement sorts the mixed data in the val column of the test_datatypes table:
SELECT
id,
val,
typeof(val)
FROM
test_datatypes
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 that a data type is a property of a value stored in a column, not the property of the column in which the value is stored. SQLite uses manifest typing to store values of any type in a column.
- Type affinity of a column is the recommended type for data stored in that column. Note that the data type is recommended, not required, therefore, 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.