SQLite Data types

SqliteData Type

The SQLite data type is a property that specifies the data type of any object. Each column in SQLite has an associated data type for each variable and expression.

You can use these data types while creating a table. SQLite uses a more general dynamic type System. In SQLite, the data type of a value is related to the value itself, not to its container.

SQLite Storage Class

Each value stored in the SQLite database has one of the following storage classes:

Storage Class	Description
Null	Value is a NULL value.
INTEGER	The value is a signed integer that is stored in 1, 2, 3, 4, 6, or 8 bytes, depending on the size of the value.
REAL	The value is a floating-point value that is stored as a 8-byte IEEE floating-point number.
TEXT	The value is a text string that is stored using the database encoding (UTF-8, utf-16be, or Utf-16le).
Blob	The value is a blob of data that is stored entirely according to its input.

SQLite's storage classes are slightly more common than data types. An integer storage class, for example, contains 6 different lengths of integral data types.

SQLite Affinity Type

SQLite supports the type affinity concept on columns. Any column can still store any type of data, but the preferred storage class for the column is its affinity. In the SQLite3 database, the columns for each table are assigned to one of the following types of affinity:

Affinity	Description
TEXT	The column stores all data using the storage class NULL, TEXT, or BLOB.
NUMERIC	The column can contain values that use all five storage classes.
INTEGER	Same as the column with NUMERIC affinity, with an exception in the CAST expression.
REAL	Similar to a column with NUMERIC affinity, the difference is that it forces an integer value to be converted to a floating point representation.
NONE	Columns with affinity NONE, which storage class is not preferred, nor does it attempt to cast data from one storage class to another.

SQLite Affinity and type names

The following table lists the various data type names that can be used when creating the SQLite3 table, and also shows the appropriate application Affinity:

Data Type	Affinity
Int INTEGER TINYINT SMALLINT Mediumint BIGINT UNSIGNED BIG INT INT2 INT8	INTEGER
CHARACTER (20) VARCHAR (255) VARYING CHARACTER (255) NCHAR (55) NATIVE CHARACTER (70) NVARCHAR (100) TEXT Clob	TEXT
Blob No datatype specified	NONE
REAL DOUBLE DOUBLE PRECISION FLOAT	REAL
NUMERIC DECIMAL (10,5) BOOLEAN DATE Datetime	NUMERIC

Boolean data type

SQLite does not have a separate Boolean storage class. Instead, the Boolean values are stored as integers 0 (false) and 1 (true).

Date and Time data types

SQLite does not have a separate storage class for storing dates and/or times, but SQLite can store dates and times as TEXT, REAL, or INTEGER values.

Storage Class	Date Format
TEXT	The format is "Yyyy-mm-dd HH:MM:SS." SSS "date.
REAL	The number of days starting from midday in November 24, 4714 GMT.
INTEGER	The number of seconds from 1970-01-01 00:00:00 UTC.

You can store dates and times in any of the above formats, and you can use the built-in date and time functions to freely convert different formats.

SQLite Data types