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.
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 support 5 concrete data type which are 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 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.|
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
|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.
Each affinity influences how values are stored in its associated column. The rules of principal storage is as follows.
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 does not support data type 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 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.
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
Now we will see how to use CURRENT_TIMESTAMP keyword with example.
When we run above sqlite query we will get 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.