SqLite – Data Types




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.

 

Boolean Datatype

 

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 Affinity Type

 

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.

 

SQLite Affinity and Type Names

 

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

Partner Sites

VideoToGifs.com

EasyOnlineConverter.com

SqliteTutorials.com