What are Data Types in SQLite?

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

SQLite Data Types

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

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

SQLite uses 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 are 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 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 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 character 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 type affinity of a column is determined by the declared type of 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 system.

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 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 as 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 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 is as follows.

1. A NUMERIC column may contain all five storage classes. If you try to insert TEXT value in NUMERIC column, it will first attempt to convert it into INTEGER storage class. If it fails to convert, then it stores this using TEXT storage class.
2. An INTEGER column tries to behave much like NUMERIC column. If you try to insert REAL value in 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 a INTEGER. INTEGER column try to store TEXT as REAL if possible. If not, then try to store as INTEGER. If also 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 of affinity. NULL and BLOB values are always stored as is in every column. 

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

CREATE TABLE Affinity (i INTEGER, n numeric, t text, b blob);

INSERT INTO Affinity values(9.581,9.581,9.581,9.581);

INSERT INTO Affinity values('9.581','9.581','9.581','9.581');

INSERT INTO Affinity values(9581,9581,9581,9581);

INSERT INTO Affinity values(x'9581', x'9581', x'9581', x'9581');

INSERT INTO Affinity VALUES (null,null,null,null);

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

SELECT ROWID,typeof(i),typeof(n),typeof(t),typeof(b) FROM Affinity;

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

rowid       typeof(i)   typeof(n)   typeof(t)   typeof(b)

----------  ----------  ----------  ----------  ----------

1           real        real        text        real

2           real        real        text        text

3           integer     integer     text        integer

4           blob        blob        blob        blob

5           null        null        null        null

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 type 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 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 example. Following is the example of using CURRENT_TIME keyword.

SELECT CURRENT_TIME, typeof(CURRENT_TIME);

When we run above sqlite statement we will get result like as shown below.

CURRENT_TIME  typeof(CURRENT_TIME)

------------  --------------------

07:35:04      text

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

SELECT CURRENT_DATE, typeof(CURRENT_DATE);

When we run above sqlite query we will get result like as shown below

CURRENT_DATE  typeof(CURRENT_DATE)

------------  --------------------

2016-08-05    text

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

SELECT CURRENT_TIMESTAMP,typeof(CURRENT_TIMESTAMP);

When we run above sqlite query we will get result like as shown below.

CURRENT_TIMESTAMP    typeof(CURRENT_TIMESTAMP)

-------------------  -------------------------

2016-08-05 07:35:37  text

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