SQLite data types are used to specify type of data of any object. Each column, variable and expression has related data type in SQLite. These data types are used while creating table. SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container.
Types of SQLite data types
SQLite Storage Classes
The stored values in a SQLite database has one of the following storage classes:
| Storage Class | Description |
|---|---|
| NULL | It specifies that the value is a null value. |
| INTEGER | It specifies the value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value. |
| REAL | It specifies the value is a floating point value, stored as an 8-byte IEEE floating point number. |
| text | It specifies the value is a text string, stored using the database encoding (utf-8, utf-16be or utf-16le) |
| BLOB | It specifies the value is a blob of data, stored exactly as it was input. |
Note: SQLite storage class is slightly more general than a data type. For example: The INTEGER storage class includes 6 different integer data types of different lengths.
SQLite Afinity Types
SQLite supports type affinity for columns. Any column can still store any type of data but the preferred storage class for a column is called its affinity.
There are following type affinity used to assign in SQLite3 database.
| Affinity | Description |
|---|---|
| TEXT | This column is used to store all data using storage classes NULL, TEXT or BLOB. |
| NUMERIC | This column may contain values using all five storage classes. |
| INTEGER | It behaves the same as a column with numeric affinity with an exception in a cast expression. |
| REAL | It behaves like a column with numeric affinity except that it forces integer values into floating point representation |
| NONE | A column with affinity NONE does not prefer one storage class over another and don’t persuade data from one storage class into another. |
SQLite Affinity and Type Names
Following is a list of various data types names which can be used while creating SQLite tables.
| Data Types | Corresponding Affinity |
|---|---|
| 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 no datatype specified | NONE |
| REAL DOUBLE DOUBLE PRECISION FLOAT | REAL |
| NUMERIC DECIMAL(10,5) BOOLEAN DATE DATETIME | NUMERIC |
Date and Time Data Type
In SQLite, there is no separate class to store dates and times. But you can store date and times as TEXT, REAL or INTEGER values.
| Storage Class | Date Format |
|---|---|
| TEXT | It specifies a date in a format like “yyyy-mm-dd hh:mm:ss.sss”. |
| REAL | It specifies the number of days since noon in Greenwich on November 24, 4714 B.C. |
| INTEGER | It specifies the number of seconds since 1970-01-01 00:00:00 utc. |
Boolean Data Type
In SQLite, there is not a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).
Leave a Reply