SQLite Data Types
SQLite uses a dynamic type system called flexible typing. Values have a storage class, and columns have a type affinity — not a strict type. Understanding this system is essential for writing correct SQLite queries.
Storage Classes
Every value stored in SQLite has one of five storage classes. These are the actual representations on disk.
NULLThe value is a NULL value — the absence of any value. NULL is distinct from an empty string or the integer zero. Any comparison involving NULL returns NULL (unknown), so NULL-safe checks require IS NULL or IS NOT NULL rather than = or !=.
SELECT typeof(NULL); -- 'null'INTEGERThe value is a signed integer, stored in 0, 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value. The INTEGER storage class actually covers seven distinct integer datatypes of different byte widths on disk, but all are converted to the most general 8-byte signed integer representation when loaded into memory for processing.
SELECT typeof(42); -- 'integer'REALThe value is a floating-point number, stored as an 8-byte IEEE 754 double-precision float. All floating-point values in SQLite use this format. Small floating-point values that have no fractional component may be stored on disk as integers as an internal optimization, but they are automatically converted back to REAL when read.
SELECT typeof(3.14); -- 'real'TEXTThe value is a text string, stored using the database encoding: UTF-8, UTF-16BE, or UTF-16LE. The encoding is chosen when the database is created. SQLite text strings may contain NUL characters (0x00), though this can cause unexpected behavior with some host-language interfaces that use NUL-terminated strings.
SELECT typeof('hello'); -- 'text'BLOBThe value is a binary large object (blob) of data, stored exactly as it was input with no transformation or encoding applied. BLOBs are useful for storing binary data such as images, serialized objects, or any byte sequence where the exact bytes must be preserved verbatim.
SELECT typeof(x'DEADBEEF'); -- 'blob'Type Affinities
Every column has a type affinity — a preferred storage class. SQLite determines affinity from the declared column type name using a set of rules applied in order.
TEXTColumns with TEXT affinity store all data using the NULL, TEXT, or BLOB storage classes. If numerical data is inserted into a TEXT affinity column it is converted into text form before being stored. Numeric values become their textual representation.
Column types: CHARACTER(20), VARCHAR(255), VARYING CHARACTER(255), NCHAR(55), NATIVE CHARACTER(70), NVARCHAR(100), TEXT, CLOB
NUMERICColumns with NUMERIC affinity may contain values using all five storage classes. Text is converted to INTEGER or REAL if it is a well-formed numeric literal; otherwise it is stored as TEXT. A floating-point value that can be expressed exactly as an integer is stored as INTEGER. Hexadecimal integer literals are not considered well-formed and are stored as TEXT for historical compatibility.
Column types: NUMERIC, DECIMAL(10,5), BOOLEAN, DATE, DATETIME
INTEGERColumns with INTEGER affinity behave the same as NUMERIC affinity with one exception: in a CAST expression, CAST(4.0 AS INT) returns integer 4, whereas CAST(4.0 AS NUMERIC) leaves it as floating-point 4.0. INTEGER affinity is the most common affinity for primary keys and counter columns.
Column types: INT, INTEGER, TINYINT, SMALLINT, MEDIUMINT, BIGINT, UNSIGNED BIG INT, INT2, INT8
REALColumns with REAL affinity behave like NUMERIC affinity except that integer values are forced into floating-point representation. As an internal optimization, small floats with no fractional component may be written to disk as integers to save space, but they are transparently converted back to REAL when read. This optimization is invisible at the SQL level.
Column types: REAL, DOUBLE, DOUBLE PRECISION, FLOAT
BLOBColumns with BLOB affinity (historically called NONE affinity) do not prefer one storage class over another. No attempt is made to coerce data from one storage class into another. A value inserted into a BLOB affinity column is stored exactly as provided, preserving its original type. This is the affinity for columns declared as BLOB or with no type declaration at all.
Column types: BLOB, (no datatype specified)
Type Affinity Determination Rules (applied in order)
| Declared Type Contains | Affinity | Examples |
|---|---|---|
| contains 'INT' | INTEGER | Matches INT, INTEGER, BIGINT, SMALLINT, TINYINT, MEDIUMINT, UNSIGNED BIG INT, INT2, INT8, and any other declared type containing the substring 'INT'. Note: 'FLOATING POINT' also matches this rule due to 'INT' in 'POINT', giving INTEGER affinity rather than REAL. |
| contains 'CHAR', 'CLOB', or 'TEXT' | TEXT | Matches CHARACTER, VARCHAR (contains 'CHAR'), VARYING CHARACTER, NCHAR, NVARCHAR, CLOB, TEXT, and any other type containing 'CHAR', 'CLOB', or 'TEXT'. Note: 'CHARINT' would match both rule 1 and rule 2, but rule 1 takes precedence, giving INTEGER affinity. |
| contains 'BLOB' or is empty | BLOB | Matches BLOB explicitly, or any column with no type declaration at all. This is the 'no affinity' case — formerly called NONE affinity, renamed BLOB affinity to avoid confusion with 'having no affinity at all'. |
| contains 'REAL', 'FLOA', or 'DOUB' | REAL | Matches REAL, FLOAT (contains 'FLOA'), DOUBLE (contains 'DOUB'), DOUBLE PRECISION. Only reached if rules 1–3 do not match first. |
| anything else | NUMERIC | The catch-all rule. Matches NUMERIC, DECIMAL, BOOLEAN, DATE, DATETIME, NUMBER, and any other declared type that does not contain 'INT', 'CHAR', 'CLOB', 'TEXT', 'BLOB', 'REAL', 'FLOA', or 'DOUB'. Note: 'STRING' has NUMERIC affinity, not TEXT, because it does not contain 'CHAR', 'CLOB', or 'TEXT'. |
Key Differences from PostgreSQL
No strict types: In PostgreSQL, a column declared as INTEGER can only store integers. In SQLite, any column can store any type of value — the declared type is advisory, not enforced.
No BOOLEAN type: SQLite has no dedicated BOOLEAN storage class. Boolean values are stored as INTEGER 0 (false) or 1 (true).
No DATETIME type: SQLite has no dedicated datetime storage class. Dates are stored as TEXT (ISO 8601), REAL (Julian day numbers), or INTEGER (Unix timestamps). Use strftime() and the date functions to work with them.
typeof(): Use typeof(x) to check the actual storage class of a value at runtime. This is essential for debugging flexible-typing surprises.