SQLite Data Types

In SQLite, understanding data types is important for efficient database design and query execution. SQLite provides five primary data types such as NULL, INTEGER, REAL, TEXT, and BLOB each of them is used for distinct purposes. In this article, We will learn about SQLite Data Types with the help of examples and so on.

SQLite Data Types

• NULL- It is a NULL value.
• INTEGER- It is an integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the value.
• REAL- It is a floating point value, stored as an 8-byte floating number.
• TEXT- It is a string, stored using the database encoding (UTF).
• BLOB- It is a group of data, stored exactly as it was entered.

Type Affinity

The considered storage class for any column is called its affinity. Every table's column in the SQLite database is assigned one of the following type affinities -

• TEXT - This column captures all NULL, TEXT, or BLOB data.
• NUMERIC - This column captures values for all storage classes.
• INTEGER - It has an exception in a CAST expression and behaves in similar way as a column with NUMERIC affinity.
• REAL - It forces integer values into floating representation. and behaves like a column with NUMERIC affinity.
• NONE - A column having NONE affinity do not choose one storage class above other and do not change data from one storage class to other.

Note: SQLite do not have any different storage class for storing dates and/or times. On the other hand, the TEXT, INT, or REAL could be used to store date and time values.

Determination Of Affinity

The following rules within the order shown used to declared the kind of the column and the affinity of any column:

1. If the declared column type has the string "INT" then it's allotted integer affinity.
2. If the declared column type has any of the strings like "TEXT", "VARCHAR" or "CLOB" then that column has TEXT affinity.
3. If the declared column type has the string "BLOB" or if no kind is given then the column has BLOB affinity.
4. If the declared column type has strings like "FLOA" or "DOUB" then the column has REAL affinity.
5. Otherwise, the affinity is NUMERIC.

Below table shows common datatype from SQL are converted into affinities by the 5 rules of the Determination Of Affinity for a small set of the datatype that SQLite can accept:

SQLite provides the typeof() function that could be used to check the storage class of a value based on its format.

Examples of SQLite Data Types

Let's look at some example of different data types in SQLite

Example 1. Using typeof function to check data type in SQLite

In this example, we use the typeof function on different values to check their data type.

Query:

SELECT typeof(200), typeof(20.0), 
typeof('200'), typeof(x'2000'), typeof(NULL);

Output:

In SQLite, there is no need to declare a specific data type for a column while creating a table.

Example 2. Check data type of values in a table

Let us create a new table named geek_test and insert values -

CREATE TABLE geek_test (Item);

INSERT INTO geek_test (Item)
VALUES (1), (2), (10.1), (20.5), ('A'), ('B'), 
(NULL), (x'0010'), (x'0011');

Use the typeof() function to check the data type of each value stored in Item column.

SELECT Item, typeof(Item)
FROM geek_test;

Output:

SQLite Data Types and Type Affinities

Conclusion

SQLite’s type affinity system offers flexibility in data storage, making it adaptable to various use cases. By understanding how type affinities are determined and how different type names map to SQLite’s affinities, developers can design more effective database schemas and perform efficient data handling.