Easy Tutorial
Sqlite Alias Sqlite Like Clause Sqlite Syntax Sqlite Php Sqlite Distinct Keyword Sqlite Functions Sqlite Autoincrement Sqlite Detach Database Sqlite Unions Clause Sqlite Attach Database Sqlite Joins Sqlite Pragma Sqlite Group By Sqlite Where Clause Sqlite Injection Sqlite And Or Clauses Sqlite Operators Sqlite Constraints Sqlite Drop Table Sqlite C Cpp Sqlite Order By Sqlite Subquery Sqlite Expressions Sqlite View Sqlite Indexed By Sqlite Alter Command Sqlite Insert Sqlite Select Sqlite Explain Sqlite Python Sqlite Installation Sqlite Transaction Sqlite Update Sqlite Delete Sqlite Commands Sqlite Date Time Sqlite Null Values Sqlite Create Table Sqlite Java Sqlite Data Types Sqlite Trigger Sqlite Create Database Sqlite Limit Clause Sqlite Intro Sqlite Truncate Table Sqlite Vacuum Sqlite Perl Sqlite Glob Clause Sqlite Index Sqlite Tutorial Sqlite Having Clause
❮ Sqlite Java Sqlite Trigger ❯

SQLite Data Types

SQLite data types are attributes used to specify the type of data for any object. Each column, variable, and expression in SQLite has an associated data type.

You can use these data types when creating tables. SQLite employs a more universal 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 Class Description
NULL The value is a NULL value.
INTEGER The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
REAL The value is a floating point value, stored as an 8-byte IEEE floating point number.
TEXT The value is a 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's storage classes are slightly more general than data types. The INTEGER storage class, for example, includes six different integer data types of different lengths.

SQLite Affinity Types

SQLite supports the concept of column affinity types. Any column can still store any type of data, but when data is inserted, the field will preferentially use the affinity type as the storage method for that value. SQLite currently supports the following five affinity types:

Affinity Type Description
TEXT Numeric data is converted to text format before being inserted into the target field.
NUMERIC When text data is inserted into a NUMERIC affinity field, SQLite converts it to INTEGER or REAL if the conversion is lossless and reversible. If conversion fails, SQLite stores the data as TEXT. For NULL or BLOB data, SQLite stores it as NULL or BLOB without conversion. It's worth noting that SQLite may convert text constants like "30000.0" to INTEGER if the value can be represented as an integer without losing information.
INTEGER For INTEGER affinity fields, the rules are the same as NUMERIC, with the only difference being in the execution of the CAST expression.
REAL The rules are mostly the same as NUMERIC, with the only difference being that text data like "30000.0" is not converted to INTEGER storage.
NONE No conversion is performed, and the data is stored in its original data type.

SQLite Affinity Types and Type Names

The following table lists the various data type names that can be used when creating an SQLite3 table, along with their corresponding affinity types:

Data Type Affinity Type
INT<br>INTEGER<br>TINYINT<br>SMALLINT<br>MEDIUMINT<br>BIGINT<br>UNSIGNED BIG INT<br>INT2<br>INT8 INTEGER
CHARACTER(20)<br>VARCHAR(255)<br>VARYING CHARACTER(255)<br>NCHAR(55)<br>NATIVE CHARACTER(70)<br>NVARCHAR(100)<br>TEXT<br>CLOB TEXT
BLOB<br>Unspecified type BLOB
REAL<br>DOUBLE<br>DOUBLE PRECISION<br>FLOAT REAL
NUMERIC<br>DECIMAL(10,5)<br>BOOLEAN<br>DATE<br>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 Types

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

Storage Class Date Format
TEXT Date in the format "YYYY-MM-DD HH:MM:SS.SSS".
REAL Number of days since noon in Greenwich on November 24, 4714 B.C.
INTEGER Number of seconds since 1970-01-01 00:00:00 UTC.

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

❮ Sqlite Java Sqlite Trigger ❯