Sqlite Data Types
SQLite: Data Types
The following is a list of datatypes available in SQLite, which includes string, numeric, date/time, and large object datatypes.
For simplicity's sake, SQLite essentially uses these basic datatypes:
- TEXT
- INTEGER
- NUMERIC
- REAL
- NONE
To be compatible with other SQL databases, SQLite allows you to use the common datatype names that you've seen in other databases and maps them to their basic SQLite datatypes (listed above).
Let's look at the common datatypes names that SQLite lets you define.
String Datatypes
All string datatypes in SQLite are converted to a TEXT datatype. If you try to specify a size 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 | Explanation |
|---|---|
| CHAR(size) | Equivalent to TEXT (size is ignored) |
| VARCHAR(size) | Equivalent to TEXT (size is ignored) |
| TINYTEXT(size) | Equivalent to TEXT (size is ignored) |
| TEXT(size) | Equivalent to TEXT (size is ignored) |
| MEDIUMTEXT(size) | Equivalent to TEXT (size is ignored) |
| LONGTEXT(size) | Equivalent to TEXT (size is ignored) |
| NCHAR(size) | Equivalent to TEXT (size is ignored) |
| NVARCHAR(size) | Equivalent to TEXT (size is ignored) |
| CLOB(size) | Equivalent to TEXT (size is ignored) |
Numeric Datatypes
All numeric datatypes in SQLite are converted to INTEGER, NUMERIC, or REAL datatypes.
The following are the Numeric Datatypes in SQLite:
| Data Type Syntax | Explanation |
|---|---|
| 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 |
| 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 |
Date/Time Datatypes
All date or time datatypes in SQLite are converted to NUMERIC datatypes.
The following are the Date/Time Datatypes in SQLite:
| Data Type Syntax | Explanation |
|---|---|
| DATE | Equivalent to NUMERIC |
| DATETIME | Equivalent to NUMERIC |
| TIMESTAMP | Equivalent to NUMERIC |
| TIME | Equivalent to NUMERIC |
Large Object (LOB) Datatypes
The following are the LOB Datatypes in SQLite:
| Data Type Syntax | Explanation |
|---|---|
| BLOB | Equivalent to NONE |
