Open In App

SQLite Data Types

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

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 –

  1. If the declared column type has the string “INT” then it’s allotted integer affinity.
  2. If the declared column type has any of the strings like “TEXT”, “VARCHAR” or “CLOB” then that column has TEXT affinity.
  3. If the declared column type has the string “BLOB” or if no kind is given then the column has BLOB affinity.
  4. If the declared column type has strings like “FLOA” or “DOUB” then the column has REAL affinity.
  5. 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
INT
INTEGER
TINYINT
SMALLINT
MEDIUMINT
BIGINT
UNSIGNED BIG INT
INT2
INT8
INTEGER
NUMERIC
DECIMAL(10, 5)
BOOLEAN
DATE
DATETIME
NUMERIC
REAL
DOUBLE
DOUBLE PRECISION
FLOAT
REAL
CHARACTER(20)
VARCHAR(255)
VARYING CHARACTER(255)
NCHAR(55)
NATIVE CHARACTER(70)
NVARCHAR(100)
TEXT
CLOB
TEXT
BLOB
no datatype specified
NONE

SQLite provides the typeof() function that could be used to check the storage class of a value based on its format.



Example-1 :

SELECT typeof(200), typeof(20.0), 
typeof('200'), typeof(x'2000'), typeof(NULL);

typeof(200) typeof(20.0) typeof(‘200’) typeof(x’2000′) typeof(NULL)
integer real text blob null

In SQLite, there is no need to declare a specific data type for a column while creating a table.


Example-2 :
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;

Item typeof(Item)
(1) integer
(2) integer
(10.1) real
(20.5) real
(‘A’) text
(‘B’) text
(NULL) null
(x’0010′) blob
(x’0011′) blob


Last Updated : 04 Nov, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads