Data Types --- Storage Class and Column Affinity

[Fred Meier]

I find the way SQLite handles data typing to be a nice feature.  I
normally create columns with no data type, which will result in an
affinity of none.  The point of this post is that a good understanding of
storage class and affinities will assist you in understanding and
designing SQLite databases. 

The following was taken from http://www.sqlite.org/datatype3.html

Most SQL database engines (every SQL database engine other than SQLite, as
far as we know) uses static typing.  With static typing, the datatype of a
value is determined by its container - the particular column the value is
stored in. 

SQLite uses a more general dynamic type system.  In SQLite, the datatype
of a value is associated with the value itself, not with the container in
which it is stored.  The dynamic type system of SQLite is backwards
compatible with the more common static type systems of other database
engines in the sense that SQL statement that work on statically typed
databases should would the same way in SQLite.  However, the dynamic
typing in SQLite allowed it to do things which are not possible in
traditional statically typed databases. 

                 ****** Storage Classes ******

Each value stored in an SQLite database (or manipulated by the database
engine) has one of the following storage classes:
   NULL. The value is a NULL value.
   INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the value.
   REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.
   TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16-LE).
   BLOB. The value is a blob of data, stored exactly as it was input.

Any column in a version 3 database, except an INTEGER PRIMARY KEY column,
may be used to store any type of value. 

Storage classes are initially assigned as follows:
Values specified as literals as part of SQL statements are assigned
storage class TEXT if they are enclosed by single or double quotes,
INTEGER if the literal is specified as an unquoted number with no decimal
point or exponent, REAL if the literal is an unquoted number with a
decimal point or exponent and NULL if the value is a NULL.  Literals with
storage class BLOB are specified using the X'ABCD' notation. 

                    ****** Column Affinity ******

The type of a value is associated with the value itself, not with the
column or variable in which the value is stored.  (This is sometimes
called manifest typing or duck typing.) All other SQL databases engines
that we are aware of use the more restrictive system of static typing
where the type is associated with the container, not the value.  To look
at it another way, SQLite provides dynamic datatypes such as one finds in
"script" programming languages such as Awk, Perl, Tcl, Python, and Ruby,
whereas other SQL database engines provide only compile-time fixed, static
typing such as found in Pascal, C++, and Java. 

In order to maximize compatibility between SQLite and other database
engines, SQLite support the concept of "type affinity" on columns.  The
type affinity of a column is the recommended type for data stored in that
column.  The key here is that the type is recommended, not required.  Any
column can still store any type of data, in theory.  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 to text form before being stored. 

A column with NUMERIC affinity may contain values using all five storage
classes.  When text data is inserted into a NUMERIC column, an attempt is
made to convert it to an integer or real number before it is stored.  If
the conversion is successful (meaning that the conversion occurs without
loss of information), then the value is stored using the INTEGER or REAL
storage class.  If the conversion cannot be performed without loss of
information then the value is stored using the TEXT storage class.  No
attempt is made to convert NULL or blob values. 

A column that uses INTEGER affinity behaves in the same way as a column
with NUMERIC affinity, except that if a real value with no fractional
component and a magnitude that is less than or equal to the largest
possible integer (or text value that converts to such) is inserted it is
converted to an integer and stored using the INTEGER storage class. 

A column with REAL affinity 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 are stored on disk as integers in order to take up
less space and are converted back into floating point as the value is read
out.)

A column with affinity NONE does not prefer one storage class over
another.  No attempt is made to coerce data from one storage class into
another.  The data is stored on disk exactly as specified. 

The type affinity of a column is determined by the declared type of the
column, according to the following rules:

If the datatype contains the string "INT" then it is assigned INTEGER affinity.

If the datatype of the column contains any of the strings "CHAR", "CLOB",
or "TEXT" then that column has TEXT affinity.  Notice that the type
VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity. 

If the datatype for a column contains the string "BLOB" or if no datatype
is specified then the column has affinity NONE. 

If the datatype for a column contains any of the strings "REAL", "FLOA",
or "DOUB" then the column has REAL affinity

Otherwise, the affinity is NUMERIC.

If a table is created using a "CREATE TABLE <table> AS SELECT..."
statement, then all columns have no datatype specified and they are given
no affinity.