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.
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 support 5 concrete data type which is also known as storage classes. Following are the 5 storage classes that are supported by SQLite
|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.|
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
|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.
Each affinity influences how values are stored in its associated column. The rules of principal storage are as follows.
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.
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 does not support data types like Boolean. We can store 0 or 1 as Integer.
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.
SELECT CURRENT_TIME, typeof(CURRENT_TIME);
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.
SELECT CURRENT_DATE, typeof(CURRENT_DATE);
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.
2016-08-05 07:35:37 text
This is how we can use data types in SQLite queries based on our requirements.