SQLite - Data Types
SQLite Data Type is an attribute which specifies the type of data that will be stored inside each column when creating a table. The data type is a guideline for SQLite to understand what type of data is expected inside of each column, and it also identifies how SQLite will interact with the stored data.
Each value stored in an SQLite database (or manipulated by the database engine) has one of the following data types:
- NUMERIC
- INTEGER
- REAL
- TEXT
- BLOB
To be compatible with other SQL databases, SQLite allows to use the common datatype names which is used in other databases and maps them to their basic SQLite datatypes (listed above).
Note: The "BLOB" data type used to be called "NONE".
SQLite Numeric Data Types
All numeric datatypes in SQLite are converted to INTEGER, NUMERIC, or REAL datatypes.
The following are the Numeric Datatypes in SQLite:
| Data Type Syntax | Description |
|---|---|
| TINYINT | Equivalent to INTEGER |
| SMALLINT | Equivalent to INTEGER |
| MEDIUMINT | Equivalent to INTEGER |
| INT | Equivalent to INTEGER |
| INTEGER | Equivalent to INTEGER |
| BIGINT | Equivalent to INTEGER |
| INT2 | Equivalent to INTEGER |
| INT4 | Equivalent to INTEGER |
| INT8 | Equivalent to INTEGER |
| UNSIGNED BIG INT | Equivalent to INTEGER |
| NUMERIC | Equivalent to NUMERIC |
| DECIMAL | Equivalent to NUMERIC |
| REAL | Equivalent to REAL |
| DOUBLE | Equivalent to REAL |
| DOUBLE PRECISION | Equivalent to REAL |
| FLOAT | Equivalent to REAL |
| BOOLEAN | Equivalent to NUMERIC |
SQLite String Data Types
All string datatypes in SQLite are converted to a TEXT datatype. If a size is specified for a string datatype, SQLite will ignore it, as it does not allow size restrictions on string datatypes.
The following are the String Datatypes in SQLite:
| Data Type Syntax | Description |
|---|---|
| CHAR(size) | Equivalent to TEXT |
| CHARACTER(size) | Equivalent to TEXT |
| VARYING CHARACTER(size) | Equivalent to TEXT |
| VARCHAR(size) | Equivalent to TEXT |
| TINYTEXT(size) | Equivalent to TEXT |
| TEXT(size) | Equivalent to TEXT |
| MEDIUMTEXT(size) | Equivalent to TEXT |
| LONGTEXT(size) | Equivalent to TEXT |
| NCHAR(size) | Equivalent to TEXT |
| NVARCHAR(size) | Equivalent to TEXT |
| NATIVE CHARACTER(size) | Equivalent to TEXT |
| CLOB(size) | Equivalent to TEXT |
SQLite Date and Time Data Types
All date or time datatypes in SQLite are converted to NUMERIC datatypes.
The following are the Date and Time Datatypes in SQLite:
| Data Type Syntax | Description |
|---|---|
| DATE | Equivalent to NUMERIC |
| DATETIME | Equivalent to NUMERIC |
| TIMESTAMP | Equivalent to NUMERIC |
| TIME | Equivalent to NUMERIC |
SQLite Large Object (LOB) Data Types
The following are the LOB Datatypes in SQLite:
| Data Type Syntax | Description |
|---|---|
| BLOB | Equivalent to BLOB |
| no datatype specified | Equivalent to BLOB |
