SQLite: Data Types

• SQLite is essentially type-less
• The value stored in a column determines its data type, irregardless of the column's declared data type
• In SQLite, you do not have to declare a data type for a column in your CREATE TABLE statement
• SQLite provides 5 primitive data types (storage classes)

Storage Classes

Regardless of the type, Sqlite stores your values using one of the following storage classes:

• NULL - NULL value
• INTEGER - signed integer
• REAL - 8 bytes IEEE floating point
• TEXT - text string using the database encoding (UTF-8, UTF-16BE or UTF-16LE). Maximum length is UNLIMITED.
• BLOB - stored exactly as it was input. Maximum length is UNLIMITED.

Storage Class Rules

If a value has:

• Enclosing quotes, the value is stored using TEXT storage class
• No enclosing quotes, decimal point or exponent, the value is stored using INTEGER storage class
• No enclosing quotes. Has decimal or exponent, the value is stored using REAL storage class
• NULL without quotes, the value is stored using NULL storage class
• Of the form X'ABCD' or x'abcd', the value is stored using BLOB storage class

• SQLite provides the typeof() function to verify the storage class of a value

SELECT typeof ("100")                   # text
    , typeof (100)                      # integer
    , typeof (100.)                     # real
    , typeof (NULL)                     # null  
    , typeof (x'1234')                  # blob - must be complete number of bytes (i.e. can't be odd number of hexadecimal digits)
    , typeof ('2020-01-01 12:00:00');   # text