Documentation Source Text

formats and freely convert between formats using the built-in
[date and time functions].</p>


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

<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>
................................................................................
</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>Determination Of Column Affinity For Views And Subqueries</h3>

<p>^The affinity for a column of a view or subquery is the affinity
   of the corresponding result set entry for the [SELECT] statement
   that implements the view or subquery.
^When the [SELECT] statement that implements a view or subqueries
is a [compound SELECT] then the affinity is determined by the
left-most query in the compound.

<h2>Affinity Name Examples</h2>

<p>The following table shows how many common datatype 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
datatype 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
................................................................................
<td align="center">5
</table>)^
</blockquote>

<p>^Note that a declared type of "FLOATING POINT" would give INTEGER
affinity, not REAL affinity, due to the "INT" at the end of "POINT".
^And the declared type of "STRING" has an affinity of NUMERIC, not TEXT.






































































<h2>Column Affinity Behavior Example</h2>

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

^(<blockquote>
................................................................................
  are compared an appropriate collating sequence is used to determine 
  the result.  </p>

  <li><p>^When two BLOB values are compared, the result is
  determined using memcmp().</p>
</ul>

<tcl>hd_fragment expraff {expression affinity}</tcl>
<h2>Affinity Of Comparison Operands</h2>

<p>^SQLite may attempt to convert values between the storage classes
INTEGER, REAL, and/or TEXT before performing a comparison.
^Whether or not any conversions are attempted before the comparison takes
place depends on the type affinity of the operands.

<p>Every table column has a type affinity (one of BLOB, TEXT, INTEGER,
REAL, or NUMERIC) but expressions do no necessarily have an affinity.

<p>Operand affinity is determined by the following rules:

<ul>
  <li><p>^The right-hand operand of an IN or NOT IN
  operator has no affinity if the operand is a list and has the same
  affinity as the affinity of the result set expression if the operand
  is a SELECT.

  <li><p>^An expression that is a simple reference to a column value
  has the same affinity as the column.
  <ul>
  <li><p>
  ^If X and Y.Z 
  are column names, then +X and +Y.Z are considered expressions for the
  purpose of determining affinity.^</p>
  <li><p>
  ^This rule applies regardless of whether the column belongs to a table,
  subquery, or [VIEW].
  </ul>

  <li><p>^(An expression of the form "CAST(<i>expr</i> AS <i>type</i>)"
  has an affinity that is the same as a column with a declared
  type of "<i>type</i>".)^

  <li><p>^Otherwise, an expression has no affinity.
</ul>

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

<p>To "apply affinity" means to convert an operand to a particular storage
class if and only if the conversion is lossless and reversible.
^(Affinity is applied to operands of a comparison operator prior to
the comparison according to the following rules in the order shown:)^</p>

<ul>
<li><p>^If one operand has INTEGER, REAL or NUMERIC affinity

formats and freely convert between formats using the built-in
[date and time functions].</p>


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

<p>
SQL database engines that use rigid typing will usually try to
automatically convert values to the appropriate datatype.  Consider this:

<blockquote><pre>
CREATE TABLE t1(a INT, b VARCHAR(10));
INSERT INTO t1(a,b) VALUES('123',456);
</pre></blockquote>

<p>
Rigidly-typed database will convert the string '123' into an
integer 123 and the integer 456 into a string '456' prior to
doing the insert.

<p>
In order to maximize compatibility between SQLite and other database
engines, and so that the example above will work on SQLite as it does
on other SQL 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>
................................................................................
</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>Affinity Name Examples</h3>

<p>The following table shows how many common datatype 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
datatype 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
................................................................................
<td align="center">5
</table>)^
</blockquote>

<p>^Note that a declared type of "FLOATING POINT" would give INTEGER
affinity, not REAL affinity, due to the "INT" at the end of "POINT".
^And the declared type of "STRING" has an affinity of NUMERIC, not TEXT.

<tcl>hd_fragment expraff {expression affinity} {Affinity Of Expressions}</tcl>
<h2>Affinity Of Expressions</h2>

<p>Every table column has a type affinity (one of BLOB, TEXT, INTEGER,
REAL, or NUMERIC) but expressions do no necessarily have an affinity.

<p>Expression affinity is determined by the following rules:

<ul>
  <li><p>^The right-hand operand of an IN or NOT IN
  operator has no affinity if the operand is a list and has the same
  affinity as the affinity of the result set expression if the operand
  is a SELECT.

  <li><p>^When an expression is a simple reference to a column of a
  real table (not a [VIEW] or subquery) then the expression
  has the same affinity as the table column.
  <ul>
  <li><p>^(Parentheses around the column name are ignored.  Hence if
  X and Y.Z are column names, then (X) and (Y.Z) are also considered
  column names and have the affinity of the corresponding columns.)^
  <li><p>
  ^(Any operators applied to column names, including the no-op 
   unary "+" operator, convert the column name into an expression which
   always has no affinity.  Hence even if X and Y.Z are column names, the
   expressions +X and +Y.Z are not column names and have no affinity.)^
  </ul>

  <li><p>^(An expression of the form "CAST(<i>expr</i> AS <i>type</i>)"
  has an affinity that is the same as a column with a declared
  type of "<i>type</i>".)^

  <li><p>^Otherwise, an expression has no affinity.
</ul>

<tcl>hd_fragment affview {rules for determining column affinity in VIEWs}</tcl>
<h2>Column Affinity For Views And Subqueries</h2>

<p>The "columns" of a [VIEW] or FROM-clause subquery are really
the expressions
in the result set of the [SELECT] statement that implements the VIEW
or subquery.  Thus, the affinity for columns of a VIEW or subquery
are determined by the expression affinity rules above.
Consider an example:

<blockquote><pre>
CREATE TABLE t1(a INT, b TEXT, c REAL);
CREATE VIEW v1(x,y,z) AS SELECT b, a+c, 42 FROM t1 WHERE b!=11;
</pre></blockquote>

<p>The affinity of the v1.x column will be the same as the affinity
of t1.b (INTEGER), since v1.x maps directly into t1.b.  But
columns v1.y and v1.z both have no affinity, since those columns
map into expression a+c and 42, and expressions always have no
affinity.

<p>When the [SELECT] statement that implements a [VIEW] or 
FROM-clause subquery is a [compound SELECT] then the affinity of 
each supposed column of the VIEW or subquery will
be the affinity of the corresponding result column for
one of the individual SELECT statements that make up 
the compound.  
However, it is indeterminate which of the SELECT statements will 
be used to determine affinity.
Different constituent SELECT statements might be used to determine 
affinity at different times during query evaluation.
Best practice is to avoid mixing affinities in a compound SELECT.


<h2>Column Affinity Behavior Example</h2>

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

^(<blockquote>
................................................................................
  are compared an appropriate collating sequence is used to determine 
  the result.  </p>

  <li><p>^When two BLOB values are compared, the result is
  determined using memcmp().</p>
</ul>

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

<p>^SQLite may attempt to convert values between the storage classes
INTEGER, REAL, and/or TEXT before performing a comparison.
^Whether or not any conversions are attempted before the comparison takes
place depends on the type affinity of the operands.


































<p>To "apply affinity" means to convert an operand to a particular storage
class if and only if the conversion is lossless and reversible.
^(Affinity is applied to operands of a comparison operator prior to
the comparison according to the following rules in the order shown:)^</p>

<ul>
<li><p>^If one operand has INTEGER, REAL or NUMERIC affinity