SQLite | Data Type | Data Types Available in SQLite

Tags:

When defining columns for storing values in a table, you can specify data types according to the values each column will hold. This article explains the data types that can be assigned to SQLite columns.

Data Types of Stored Values

Many databases assign a data type to each column, which determines the values that the column can store. SQLite3 does not require a data type for every column when defining a table. A column without a declared type can store values of various forms.

Even without a declared column type, each stored value has a data type. SQLite classifies values into five storage classes: NULL, INTEGER, REAL, TEXT, and BLOB.

Data type Description
NULL A NULL value
INTEGER A signed integer stored in 1, 2, 3, 4, 6, or 8 bytes
REAL A floating-point number stored in 8 bytes
TEXT Text stored using UTF-8, UTF-16BE, or UTF-16LE
BLOB A Binary Large Object that stores the input data unchanged

The notation used in an SQL statement determines a value’s data type. For example, a value enclosed in single quotes (') is TEXT; a number without a decimal point or exponent is INTEGER; and a number with a decimal point or exponent is REAL.

Column Data Types

You can specify a data type for a column when creating a table, but it is not required.

The following example creates a table with an INTEGER column named id and a TEXT column named name.

create table customer (id integer, name text);

SQLite3 uses five type affinities for columns: TEXT, NUMERIC, INTEGER, REAL, and NONE.

TEXT
NUMERIC
INTEGER
REAL
NONE

A column can store a value of another data type even when a type is declared. SQLite may convert the value depending on the combination of the column’s affinity and the value’s data type.

  1. An INTEGER or REAL value inserted into a TEXT column is converted to TEXT before being stored.

  2. When a TEXT value is inserted into a NUMERIC column, SQLite attempts to convert it to INTEGER or REAL. If conversion succeeds, the converted value is stored; otherwise, it remains TEXT.

  3. A REAL value that can be represented as an integer, such as 34.0, or an equivalent TEXT value inserted into an INTEGER column is converted to INTEGER.

  4. An INTEGER value inserted into a REAL column is converted to REAL.

  5. No conversion is performed for a column with NONE affinity.

Declaring a Column Data Type

Although SQLite3 has five type affinities—TEXT, NUMERIC, INTEGER, REAL, and NONE—you can declare int instead of integer, partly for compatibility with other databases. The following rules determine a column’s affinity.

A declared type containing the string INT has INTEGER affinity.

create table customer (id int, name text); -- use int instead of integer

A declared type containing CHAR, CLOB, or TEXT has TEXT affinity.

create table customer (id integer, name varchar); -- use varchar instead of text

A declared type containing BLOB, or a column without a declared type, has NONE affinity.

A declared type containing REAL, FLOA, or DOUB has REAL affinity.

Any declared type that does not match the preceding rules has NUMERIC affinity.