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

SqLite - HOME SqLite - Intro SqLite - Installation SqLite - Syntax SqLite - Data Types SqLite - Create DB SqLite - Create Table SqLite - Select SqLite - Distinct SqLite - Where SqLite - And & Or SqLite - Order By SqLite - Insert Into SqLite - Update SqLite - Delete SqLite - Injection SqLite - Select Top SqLite - Like SqLite - Wildcards SqLite - In SqLite - Between SqLite - Aliases SqLite - Joins SqLite - Inner Join SqLite - Left Join SqLite - Right Join SqLite - Full Join SqLite - Union SqLite - Select Into SqLite - Insert Into Select SqLite - Constraints SqLite - Not Null SqLite - Unique SqLite - Primary Key SqLite - Foreign Key SqLite - Check SqLite - Default SqLite - Create Index SqLite - Drop SqLite - Alter SqLite - Auto Increment SqLite - Views SqLite - Dates SqLite - Null Values SqLite - Null Functions SqLite - Data Types SqLite - DB Data Types

SqLite - Functions SqLite - Avg() SqLite - Count() SqLite - First() SqLite - Last() SqLite - Max() SqLite - Min() SqLite - Sum() SqLite - Group By SqLite - Having SqLite - Ucase() SqLite - Lcase() SqLite - Mid() SqLite - Len() SqLite - Round() SqLite - Now() SqLite - Format()