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:

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:

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.

Type Affinity

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:

• TEXT
• NUMERIC
• INTEGER
• REAL
• NONE

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

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

Previous: Attach, Detach Database
Next: Create, Alter, Drop table