Documentation Source Text

Check-in [9840457b31]
Login

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

Overview
Comment:Clarification of the automatic conversion rules for inserting TEXT into a NUMERIC column.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 9840457b31a319ae22ea111b962f6fbf97cd10c722843ebd0262546eaa65cbfd
User & Date: drh 2019-10-14 00:20:55.383
Context
2019-10-14
00:26
Further clarification of automatic affinity conversion rules. (check-in: 3a3335a61e user: drh tags: trunk)
00:20
Clarification of the automatic conversion rules for inserting TEXT into a NUMERIC column. (check-in: 9840457b31 user: drh tags: trunk)
2019-10-11
03:26
Version 3.30.1 (check-in: bb0f52c956 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/datatype3.in.
︙︙
136
137
138
139
140
141
142
143


144
145
146
147
148





149
150
151
152
153
154
155
156

<p>^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 into text form before being stored.</p>

<p>^A column with NUMERIC affinity may contain values using all five
storage classes.  ^When text data is inserted into a NUMERIC column, the
storage class of the text is converted to INTEGER or REAL (in order of
preference) if such conversion is lossless and reversible.


^For conversions between TEXT and REAL storage classes, SQLite considers
the conversion to be lossless and reversible if the first 15 significant
decimal digits of the number are preserved.
^If the lossless conversion of TEXT to INTEGER or REAL is not possible then
the value is stored using the TEXT storage class. ^No





attempt is made to convert NULL or BLOB values.</p>

<p>^A string might look like a floating-point literal with
a decimal point and/or exponent notation but as long as
the value can be expressed as an integer, the NUMERIC affinity will convert
it into an integer. ^(Hence, the string '3.0e+5' is stored in a
column with NUMERIC affinity as the integer 300000, not as the floating
point value 300000.0.)^</p>








|
>
>
|
<
|
|
|
>
>
>
>
>
|








136
137
138
139
140
141
142
143
144
145
146

147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162

<p>^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 into text form before being stored.</p>

<p>^A column with NUMERIC affinity may contain values using all five
storage classes.  ^When text data is inserted into a NUMERIC column, the
storage class of the text is converted to INTEGER or REAL (in order of
preference) if the text is a well-formed integer or real literal, respectively.
^If the TEXT value is a well-formed integer literal that is too large
to fit in a 64-bit signed integer, it is converted to REAL.
^For conversions between TEXT and REAL storage classes, only the first

15 significant decimal digits of the number are preserved.
^If the TEXT value is not a well-formed integer or real literal,
then the value is stored as TEXT.
^(For the purposes of this paragraph, hexadecimal integer
literals are not considered well-formed and are stored as TEXT.
(This is done for historical compatibility with versions of SQLite
prior to [version 3.8.6] [dateof:3.8.6] where hexadecimal integer
literals were first introduced into SQLite.))^
^No attempt is made to convert NULL or BLOB values.</p>

<p>^A string might look like a floating-point literal with
a decimal point and/or exponent notation but as long as
the value can be expressed as an integer, the NUMERIC affinity will convert
it into an integer. ^(Hence, the string '3.0e+5' is stored in a
column with NUMERIC affinity as the integer 300000, not as the floating
point value 300000.0.)^</p>
︙︙