SQLite Data Types
SQLite Data Type is a quality that defines the type of data of any object. SQLite is different from other database systems, it uses dynamic type system. In another way a value stored in any column defines its data type, not the column’s data type.
Storage Classes could be used to define the format that SQLite uses to store data on disk. SQLite provides five primary data types which are mentioned below –
- 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 concept is supported by SQLite on columns. The considered storage class for any column is called its affinity. Every table’s column in SQLite database is assigned one of the following type affinities –
- TEXT – This column captures all data for NULL, TEXT or BLOB.
- NUMERIC – This column capture 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 –
- If the declared column type has the string “INT” then it’s allotted integer affinity.
- If the declared column type has any of the strings like “TEXT”, “VARCHAR” or “CLOB” then that column has TEXT affinity.
- If the declared column type has the string “BLOB” or if no kind is given then the column has BLOB affinity.
- If the declared column type has strings like “FLOA” or “DOUB” then the column has REAL affinity.
- 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 data type||Type affinity|
UNSIGNED BIG INT
no datatype specified
SQLite provides the typeof() function that could be used to check the storage class of a value based on its format.
SELECT typeof(200), typeof(20.0), typeof('200'), typeof(x'2000'), typeof(NULL);
In SQLite, there is no need to declare a specific data type for a column while creating 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;