SQLite Data Types
What is data type?
- A data type specifies a particular type of data, such as integer, floating-point, Boolean etc.
- A data type also specifies the possible values for that type, the operations that can be performed on that type and the way the values of that type are stored.
SQLite Data Types: Version 3
Maximum SQL database engines use static, rigid typing. In static typing system, the data type of a value is determined by its container ( e.g. integer type always accept integer values) the particular column in which the value is stored. In SQLite, the data type of a value is associated with the value itself, not with its container. Here are two examples where MySQL did not accept a string type data (e.g. 'abcd) in INTEGER type declared column where as SQLite accept the same data in INTEGER type declared column and executes the SELECT statement.
Test rigid data typing:In MySQL (rigid data types):
mysql> CREATE TABLE t1(c1 INTEGER, c2 DATE); Query OK, 0 rows affected (1.07 sec) mysql> INSERT INTO t1 VALUES('abcd', '14/23/2033'); ERROR 1366 (HY000): Incorrect integer value: 'abcd' for column 'c1' at row
In SQLite3 (no rigidity)
sqlite> CREATE TABLE t1(c1 INTEGER, c2 DATE); sqlite> INSERT INTO t1 VALUES('abcd', '14/23/2033'); sqlite> SELECT * FROM t1; abcd|14/23/2033 sqlite>
SQLite Storage Classes and Data types
SQLite supports a number of data types in various categories. Here is a list of storage classes:
|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.|
Note that a storage class is slightly more general than a data type. The INTEGER storage class, for example, includes 6 different integer data types of different lengths. This makes a difference on disk. But as soon as INTEGER values are read off of disk and into memory for processing, they are converted to the most general data type (8-byte signed integer). And so for the most part, "storage class" is indistinguishable from "data type" and the two terms can be used interchangeably.
SQLite Boolean Data type
SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).
SQLite Date and Time Data type
SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:
- TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
- REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
- INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.
Applications can choose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.
In order to maximize compatibility between SQLite and other database engines, SQLite supports the concept of "type affinity" on columns. The type affinity of a column is the recommended type for data stored in that column. The important idea here is that the type is recommended, not required. Any column can still store any type of data. It is just that some columns, given the choice, will prefer to use one storage class over another. The preferred storage class for a column is called its "affinity".
Each column in an SQLite 3 database is assigned one of the following type affinities:
A column with TEXT affinity stores all data using storage classes NULL, TEXT or BLOB. If numerical data is inserted into a column with TEXT affinity it is converted into text form before being stored.
Affinity Name Examples
|Example Typenames From The
CREATE TABLE Statement
or CAST Expression
|Resulting Affinity||Rule Used To Determine Affinity|
UNSIGNED BIG INT
no datatype specified
2.3 Column Affinity Behavior Example
The following SQL demonstrates how SQLite uses column affinity to do type conversions when values are inserted into a table.
CREATE TABLE t1( t TEXT, -- text affinity by rule 2 nu NUMERIC, -- numeric affinity by rule 5 i INTEGER, -- integer affinity by rule 1 r REAL, -- real affinity by rule 4 no BLOB -- no affinity by rule 3 ); -- Values stored as TEXT, INTEGER, INTEGER, REAL, TEXT. INSERT INTO t1 VALUES('500.0', '500.0', '500.0', '500.0', '500.0'); SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1; text|integer|integer|real|text -- Values stored as TEXT, INTEGER, INTEGER, REAL, REAL. DELETE FROM t1; INSERT INTO t1 VALUES(500.0, 500.0, 500.0, 500.0, 500.0); SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1; text|integer|integer|real|real -- Values stored as TEXT, INTEGER, INTEGER, REAL, INTEGER. DELETE FROM t1; INSERT INTO t1 VALUES(500, 500, 500, 500, 500); SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1; text|integer|integer|real|integer -- BLOBs are always stored as BLOBs regardless of column affinity. DELETE FROM t1; INSERT INTO t1 VALUES(x'0500', x'0500', x'0500', x'0500', x'0500'); SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1; blob|blob|blob|blob|blob -- NULLs are also unaffected by affinity DELETE FROM t1; INSERT INTO t1 VALUES(NULL,NULL,NULL,NULL,NULL); SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1; null|null|null|null|null