totn SQLite

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