SQLite Data Types

SQLite data type is an attribute that specifies the type of data for any object. Each column, variable, and expression in SQLite has a related data type.

You can use these data types when creating tables. SQLite uses a more general dynamic type system. In SQLite, the data type of a value is associated with the value itself, not with its container.

SQLite Storage Classes

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

Storage ClassDescription
NULLThe value is a NULL value.
INTEGERThe value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
REALThe value is a floating-point value, stored as an 8-byte IEEE floating-point number.
TEXTThe value is a text string, stored using the database encoding (UTF-8, UTF-16BE, or UTF-16LE).
BLOBThe value is a blob data, stored exactly as it was input.

SQLite storage classes are slightly more general than data types. The INTEGER storage class, for example, includes 6 different integer data types of different lengths.

SQLite Affinity Types

SQLite supports the concept of type affinity for columns. Any column can still store any type of data; when data is inserted, the field's data will preferentially use the affinity type as the storage method for that value. The current version of SQLite supports the following five affinity types:

Affinity TypeDescription
TEXTNumeric data needs to be converted to text format before being inserted, and then inserted into the target field.
NUMERICWhen text data is inserted into a field with NUMERIC affinity, if the conversion operation does not cause loss of data information and is fully reversible, SQLite will convert the text data into INTEGER or REAL type data. If the conversion fails, SQLite will still store the data as TEXT. For new data of NULL or BLOB type, SQLite will not perform any conversion and will store the data directly as NULL or BLOB. It should be additionally noted that for constant text in floating-point format, such as "30000.0", if the value can be converted to INTEGER without losing numeric information, SQLite will convert it to INTEGER storage.
INTEGERFor fields with INTEGER affinity, the rules are the same as NUMERIC, with the only difference being when executing CAST expressions.
REALIts rules are basically the same as NUMERIC, with the only difference being that text data like "30000.0" will not be converted to INTEGER storage.
NONENo conversion is performed; the data is stored directly as its own data type.

SQLite Affinity Types and Type Names

The following table lists the various data type names that can be used when creating SQLite3 tables, and also shows the corresponding affinity types:

Data TypeAffinity Type

  • 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

  • Unspecified type

BLOB

  • 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, Boolean values are stored as integers 0 (false) and 1 (true).

Date and Time Data Type

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 ClassDate Format
TEXTDate in the format "YYYY-MM-DD HH:MM:SS.SSS".
REALNumber of days since noon in Greenwich Mean Time on November 24, 4714 BC.
INTEGERNumber of seconds since 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 between different formats.

Other Extensions