Documentation Source Text

which the value is stored.</p>

<p>SQLite uses a more general dynamic type system.  In SQLite, the datatype
of a value is associated with the value itself, not with its container.
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
work the same way in SQLite.  However, the dynamic typing in SQLite allows
it to do things which are not possible in traditional rigidly typed
databases.</p>

<h2>1.0 Storage Classes and Datatypes</h2>

<p>Each value stored in an SQLite database (or manipulated by the
database engine) has one of the following storage classes:</p>
<ul>
  <li><p><B>NULL</B>.
  The value is a NULL value.</p>

  <li><p><B>INTEGER</B>. The value is a signed integer, stored in 1,
  2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.</p>

  <li><p><B>REAL</B>. The value is a floating point value, stored as
  an 8-byte IEEE floating point number.</p>

  <li><p><B>TEXT</B>. The value is a text string, stored using the
  database encoding (UTF-8, UTF-16BE or UTF-16LE).</p>

  <li><p><B>BLOB</B>. The value is a blob of data, stored exactly as
  it was input.</p>
</ul>

<p>Note that a storage class is slightly more general than a datatype.
The INTEGER storage class, for example, includes 6 different integer
datatypes of different lengths.  This makes a difference on disk.  But
as soon as INTEGER values are read off of disk and into memory for processing,
they are converted to the most general datatype (8-byte signed integer).
And so for the most part, "storage class" is indistinguishable from 
"datatype" and the two terms can be used interchangably.</p>

<p>^Any column in an SQLite version 3 database,
except an [INTEGER PRIMARY KEY] column, may be used to store a value 
of any storage class.</p>

<p>All values in SQL statements, whether they are literals embedded in SQL
statement text or [parameters] bound to 
[prepared statements | pre-compiled SQL statements]
have an implicit storage class.
Under circumstances described below, the
database engine may convert values between numeric storage classes
(INTEGER and REAL) and TEXT during query execution. 
</p>

<tcl>hd_fragment affinity affinity {column affinity} {type affinity} {*affinities}</tcl>
<h2>2.0 Type Affinity</h2>

<p>
In order to maximize compatibility between SQLite and other database
engines, SQLite supports the concept of "type affinity" on columns.
The type affinity of a column is the recommended type for data stored
in that column.  The important idea here is that the type is recommended, not
required.  Any column can still store any type of data.
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".
</p>

<p>^(Each column in an SQLite 3 database is assigned one of the
following type affinities:</p>

<tcl>hd_fragment affname {rules for determining column affinity}</tcl>
<h3>2.1 Determination Of Column Affinity</h3>

<p>^(The affinity of a column is determined by the declared type
of the column, according to the following rules in the order shown:)^</p>

<ol>
  <li><p>^If the declared type contains the string "INT" then it
  is assigned INTEGER affinity.</p>

  <li><p>^If the declared type 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.</p>

  <li><p>^If the declared type for a column
  contains the string "BLOB" or if
  no type is specified then the column has affinity NONE.</p>

  <li><p>^If the declared type for a column
  contains any of the strings "REAL", "FLOA",
  or "DOUB" then the column has REAL affinity.</p>

  <li><p>^Otherwise, the affinity is NUMERIC.</p>
</ol>

<p>^Note that the order of the rules for determining column affinity
is important.  ^A column whose declared type is "CHARINT" will match
both rules 1 and 2 but the first rule takes precedence and so the 
column affinity will be INTEGER.</p>

<h3>2.2 Affinity Name Examples</h3>

<p>The following table shows how many common data type names from
more traditional SQL implementations are converted into affinities by the five rules of the
previous section.  This table shows only a small subset of the
data type names that SQLite will accept.  Note that ^(numeric arguments
in parentheses that following the type name (ex: "VARCHAR(255)") are
ignored)^ by SQLite - SQLite does not impose any length restrictions
(other than the large global [SQLITE_MAX_LENGTH] limit) on the length of
strings, BLOBs or numeric values.</p>

<blockquote> ^(
<table border="1" cellpadding="5">
<tr><th>Example Typenames From The<br>CREATE TABLE Statement<br>
        or CAST Expression
    <th>Resulting Affinity
    <th>Rule Used To Determine Affinity

  NVARCHAR(100)<br>
  TEXT<br>
  CLOB
<td align="center">TEXT
<td align="center">2

<tr><td align="center" valign="top">
  BLOB<br>
  <i>no datatype specified</i>
<td align="center">NONE
<td align="center">3

<tr><td align="center" valign="top">
  REAL<br>
  DOUBLE<br>
  DOUBLE PRECISION<br>

  DECIMAL(10,5)<br>
  BOOLEAN<br>
  DATE<br>
  DATETIME
<td align="center">NUMERIC
<td align="center">5
</table>)^
</blockquote>

<h3>2.3 Column Affinity Behavior Example</h3>

<p>The following SQL demonstrates how SQLite uses column affinity
to do type conversions when values are inserted into a table.</p>

^(<blockquote>

SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
text|integer|integer|real|text

-- Values stored as TEXT, INTEGER, INTEGER, REAL, REAL.
DELETE FROM t1;
INSERT INTO t1 VALUES(500.0, 500.0, 500.0, 500.0, 500.0);
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
text|integer|integer|real|real

-- Values stored as TEXT, INTEGER, INTEGER, REAL, INTEGER.
DELETE FROM t1;
INSERT INTO t1 VALUES(500, 500, 500, 500, 500);
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
text|integer|integer|real|integer

<tcl>hd_fragment compaff {comparison affinity rules}</tcl>
<h3>3.3 Type Conversions Prior To Comparison</h3>

<p>Storage class conversions of operands prior to a binary comparison
operation occur according to the following rules in the order shown:</p>

<ul>
<li><p>^If either operand has INTEGER, REAL or NUMERIC affinity and the
other operand does not, then NUMERIC
affinity is applied to the other operand.

<li><p>^If either operand has TEXT affinity and the other has no affinity,
then TEXT affinity is applied to the other operand.

<li><p>^Otherwise, no affinity is applied and both operands are compared
as is.</p>
</ul>

<p>In the comparison affinity rules above, to "apply affinity" means to

-- Column "b" has numeric affinity and so numeric affinity is applied
-- to the operands on the right.  Since the operands are already numeric,
-- the application of affinity is a no-op; no conversions occur.  All
-- values are compared numerically.
SELECT b < 40,   b < 60,   b < 600 FROM t1;
0|0|1

-- Numeric affinity is applied to operands on the right, converting them
-- from text to integers.  Then a numeric comparison occurs.
SELECT b < '40', b < '60', b < '600' FROM t1;
0|0|1

-- No affinity conversions occur.  Right-hand side values all have
-- storage class INTEGER which are always less than the TEXT values
-- on the left.