SQLite Data Types

Here we will learn SQLite data types with examples and how to use data types (null, integer, text, real, blob, boolean, date and time) in sqlite databases with examples.

SQLite Data Types

In SQLite, each table in a database has a data type and name. The data type defines what type of data can be stored in a column of a table.

 

Most databases use strong, static column type for tables. This means that the column can only hold a value that is compatible with columns defined type.

 

SQLite uses a dynamic typing technique also known as Manifest Typing. This means it allows nearly any element of any row to hold almost any type of value.

SQLite Storage Classes

SQLite support 5 concrete data type which is also known as storage classes. Following are the 5 storage classes that are supported by SQLite

 

Data Type	Description
NULL	A NULL is considered its own distinct type. A NULL type does not hold a value. It is represented by the NULL keyword and it only holds NULL.
INTEGER	The value is a signed integer numbers (8-byte length). Integer values are whole numbers i.e. it may be positive or negative. They can vary in size: 1, 2, 3, 4, 6, or 8 bytes. Integer has a range of −9,223,372,036,854,775,808 to +9,223,372,036,854,775,807, or roughly 19 digits. For example, it holds value like 6, 56985, -655656, etc.
REAL	A floating-point number, stored as an 8-byte value that contains a decimal point or exponent. Floating-point numbers are represented by any bare series of numeric digits that include a decimal point or exponent. SQLite uses 8-byte floats to store real numbers. For example, it holds value like 23.5, -54.25, 5.87545, 2, etc.
Text	Text values are variable-length character data. Text values are represented as characters enclosed within single quotes. The maximum string value in SQLite is unlimited. For example, it holds values like ‘abc’, ‘65xyz’, etc.
BLOB	A BLOB value is variable-length raw bytes. Literal BLOBs are represented as hexadecimal text strings preceded by an x. BLOB (Binary Large Object) data is any kind of data. The maximum BLOB value in SQLite is unlimited. For example, it holds value like x the notation x'9856ABCD' represents a 4-byte BLOB.

SQLite Type Affinity

Type affinity determines the storage class. In SQLite, type affinity is used to store the values within a column, and the declared type of column determines the type affinity of a column. However, you still can store any type of data as you wish; these types are recommended but not required.

 

These types were introduced in SQLite to maximize the compatibility between SQLite and other database management systems.

 

In SQLite, columns don’t have types or domains. While a column can have a declared type, internally it only has a type affinity. But the declared type and type affinity are two different things.

 

A column’s affinity is determined directly from its declared type. Therefore, when you declare a column in a table, the type you choose to declare it will ultimately determine that column’s affinity.

 

In SQLite, each table column must have one of five type affinities

 

Data Type	Description
Text	A column with this affinity can only store TEXT, NULL or BLOB value. If you store INTEGER in this affinity, then it is converted to text value type.
Numeric	A column with a numeric affinity will store any of the five types. Values with integer and float types, along with NULL and BLOB types, are stored without conversion.
Integer	A column with an integer affinity works essentially the same as a numeric affinity. If you store float value, then it is converted to an integer type.
Float	A column with a floating-point affinity also works essentially the same as a numeric affinity. The only difference is if you store integer value then it is converted to floating value.
None	A column with a none affinity has no preference over storage class. It is called as BLOB affinity.

 SQLite assigns a column’s affinity according to the following rules.

 

1. By default, a column’s default affinity is NUMERIC. That is, if a column is not INTEGER, TEXT, or NONE, then it is automatically assigned NUMERIC affinity.
2. If a column’s declared type contains the string 'INT' or ‘int’, then the column is assigned INTEGER affinity.
3. If a column’s declared type contains any of the strings 'CHAR', 'BLOB', or ‘TEXT’, then that column is assigned TEXT affinity. Note that 'VARCHAR' contains the string 'CHAR' and thus will give TEXT affinity.
4. If a column’s declared type contains the string 'BLOB', or if it has no declared type, then it is assigned NONE affinity.

Each affinity influences how values are stored in its associated column. The rules of principal storage are as follows.

 

1. A NUMERIC column may contain all five storage classes. If you try to insert TEXT value in a NUMERIC column, it will first attempt to convert it into an INTEGER storage class. If it fails to convert, then it stores this using the TEXT storage class.
2. An INTEGER column tries to behave much like a NUMERIC column. If you try to insert REAL value in the INTEGER column, then it will store it as REAL only. However, if REAL does not have a fractional part then it will be stored as an INTEGER. INTEGER column tries to store TEXT as REAL if possible. If not, then try to store as INTEGER. If it fails, then it will store it as TEXT.
3. A TEXT column will convert all INTEGER or REAL values to TEXT.
4. A NONE column does not attempt to convert any values. 
5. No column will ever try to convert NULL or BLOB values not considering affinity. NULL and BLOB values are always stored as is in every column. 

Now, let’s look at the example of how these affinities will work. First, create one new table “Affinity” and insert data into that table as shown below.

 

Once we execute the above statements new table "Affinity" will create with data. Now run following select statement to get inserted table details.

 

Whenever we run above query we will get output like as shown below

 

The first INSERT inserts a REAL value. Column i keep the NUMERIC storage class because it tries to be NUMERIC when it can. Column n doesn’t have to convert anything. Column t converts it to TEXT. Column b stores it exactly as given in the context.

 

In each subsequent INSERT, you can see how the conversion rules are applied in each varying case.

SQLite Boolean Data Type

SQLite does not support data types like Boolean. We can store 0 or 1 as Integer.

SQLite Date and Time Data Type

Most relational database products have several native data types for storing dates, times, timestamps, and durations of all sorts. SQLite does not support data type for storing DATE/TIME. For that SQLite provide a small set of date & time conversion function that will store it as either TEXT or INTEGER. 

 

SQLite includes three special keywords that may be used as a default value: CURRENT_TIME, CURRENT_DATE, and CURRENT_TIMESTAMP.

 

We will see how to use these keywords with examples. Following is the example of using the CURRENT_TIME keyword.

 

When we run the above SQLite statement we will get a result like as shown below.

 

Now we will see how to use the CURRENT_DATE keyword with example.

 

When we run the above SQLite query we will get a result like as shown below

 

Now we will see how to use the CURRENT_TIMESTAMP keyword with example.

 

When we run the above SQLite query we will get a result like as shown below.

 

This is how we can use data types in SQLite queries based on our requirements.