SQLite much more simplistic data types compared to SQL.  The datatype limitations can be cumbersome especially if you add time durations, dates etc as SQLite has very few built-in functions for these. The up side however is that SQLite provides an easy way to make your own built in functions for adding things like time durations (the sqlite3_create_function library function). You use that facility in place of traditional stored procedures.

A SQLite column does not have a type requirement, but it does have a type preference, called an affinity.  What this means is that under the hood sometimes SQLite will change the type of a value to fit match the affinity of the column, but you usually don't need to know this if you stick to storing an INTEGER in an INTEGER column, a REAL in a REAL column, etc.

Data Types

https://www.sqlite.org/datatype3.html

SQLite 3 values can be one of the following types:

TEXT

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.

Declare as a type that includes the characters: "CHAR", "CLOB", or "TEXT"
Notice that the type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity.

Length – Specifying the length of a TEXT column is allowed but the value is ignored (You can declare a VARCHAR(10) and SQLite will be happy to store a 500-million character string there).   There is no max length limitation for text.

NUMERIC

May contain values using all five storage classes. When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if such conversion is lossless and reversible. For conversions between TEXT and REAL storage classes, SQLite considers the conversion to be lossless and reversible if the first 15 significant decimal digits of the number are preserved. If the lossless conversion of TEXT to INTEGER or REAL is not possible then the value is stored using the TEXT storage class. No attempt is made to convert NULL or BLOB values.

Declare as: anythign other than the other declaration character definitions, or simple "NUMERIC"

INTEGER

Behaves the same as a column with NUMERIC affinity. The difference between INTEGER and NUMERIC affinity is only evident in a CAST expression.
Signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.

Declare as a type that includes the characters: "INT"

REAL

Behaves like a column with NUMERIC affinity except that it forces integer values into floating point representation.
(As an internal optimization, small floating point values with no fractional component and stored in columns with REAL affinity are written to disk as integers in order to take up less space and are automatically converted back into floating point as the value is read out. This optimization is completely invisible at the SQL level and can only be detected by examining the raw bits of the database file.)

Declare as a type that includes the characters: "REAL", "FLOA", or "DOUB" 

NONE

Does not prefer one storage class over another and no attempt is made to coerce data from one storage class into another.

Declare as a type that includes the characters: "BLOB" or don't specify a type

Conversion from more common SQL data types (SQLite 'Affinity')

sqlite_affinity_examples

 

 

USEFUL?
We benefit hugely from resources on the web so we decided we should try and give back some of our knowledge and resources to the community by opening up many of our company’s internal notes and libraries through mini sites like this. We hope you find the site helpful.
Please feel free to comment if you can add help to this page or point out issues and solutions you have found, but please note that we do not provide support on this site. If you need help with a problem please use one of the many online forums.