Documentation Source Text

Check-in [3da8351ec8]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Fix some typos in datatype3.html.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 3da8351ec80491d921a3a657e8f384941795bba3
User & Date: dan 2009-12-30 15:32:56
Context
2009-12-30
18:17
The FLOATING POINT datatype has INTEGER affinity due to the "INT" at the end of "POINT". Make this clear in the datatype3.html documentation file. check-in: 5c8750d0ff user: drh tags: trunk
15:32
Fix some typos in datatype3.html. check-in: 3da8351ec8 user: dan tags: trunk
14:53
Update the documentation in preparation for the release of 3.6.22. check-in: 758f10c6da user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/datatype3.in.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
..
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
..
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
...
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
...
221
222
223
224
225
226
227
228

229
230
231
232
233
234
235
...
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
...
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
...
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
...
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
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
would the same way in SQLite.  However, the dynamic typing in SQLite allowed
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>
................................................................................
  <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-16-LE).</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 as 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 ideay 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".
</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 datatype contains the string "INT" then it
  is assigned INTEGER affinity.</p>

  <li><p>^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.</p>

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

  <li><p>^If the datatype 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 datatype 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
legacy SQL 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 or 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

<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|text

-- 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 or 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 does not
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 &lt; 40,   b &lt; 60,   b &lt; 600 FROM t1;
0|0|1

-- Numeric affinity is applied to operands no the right, converting them
-- from text to integers.  Then a numeric comparison occurs.
SELECT b &lt; '40', b &lt; '60', b &lt; '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.







|







 







|







|









|







 







|
|







 







|


|




|

|

|







|





|
|

|



|







 







|
>







 







|







 







|







 







|



|







 







|







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
..
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
..
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
...
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
...
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
...
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
...
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
...
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
...
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
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>
................................................................................
  <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 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
(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 &lt; 40,   b &lt; 60,   b &lt; 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 &lt; '40', b &lt; '60', b &lt; '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.