SQLite Data Types

SqLite – Data Types

SQLite Data Types are quite different than the normal SQL data types.
SQLite uses a different and more general dynamic type system. In SQLite, the data type of a value is associated with the value itself, not with its container.

Storage classes

Every value which is stored in an SQLite database has one of the following storage classes.

Storage Class	Description
NULL	null value
INTEGER	signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value
REAL	a floating point value, 8-byte IEEE floating point number.
TEXT	text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).
BLOB	The value is a blob of data, stored exactly as it was input.

SQLite does not Provide a separate Boolean storage class. In SQLite, Boolean values are stored as integers i.e. 0 (false) and 1 (true).

SQLite supports the concept of “type affinity” on columns. The type affinity of a column is the recommended type for data stored in that column.

Affinity	Description
TEXT	This column stores all data using storage classes NULL, TEXT or BLOB.
NUMERIC	This column may contain values using all five storage classes.
INTEGER	Behaves the same as a column with NUMERIC affinity with an exception in a CAST expression.
REAL	Behaves like a column with NUMERIC affinity except that it forces integer values into floating point representation
NONE	A column with affinity NONE does not prefer one storage class over another and no attempt is made to coerce data from one storage class into another.

In the table below various data type names which can be used while creating SQLite3 tables and corresponding applied affinity is listed:

Example Typenames From The CREATE TABLE Statement or CAST Expression	Resulting Affinity	Rule Used To Determine Affinity
INT INTEGER TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIG INT INT2 INT8	INTEGER	1
CHARACTER(20) VARCHAR(255) VARYING CHARACTER(255) NCHAR(55) NATIVE CHARACTER(70) NVARCHAR(100) TEXT CLOB	TEXT	2
BLOB no datatype specified	BLOB	3
REAL DOUBLE DOUBLE PRECISION FLOAT	REAL	4
NUMERIC DECIMAL(10,5) BOOLEAN DATE DATETIME	NUMERIC	5