1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
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
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
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
|
<!DOCTYPE html>
<html><head>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<link href="sqlite.css" rel="stylesheet">
<title>Datatypes In SQLite version 2</title>
<!-- path= -->
</head>
<body>
<div class=nosearch>
<a href="index.html">
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0">
</a>
<div><!-- IE hack to prevent disappearing logo --></div>
<div class="tagline desktoponly">
Small. Fast. Reliable.<br>Choose any three.
</div>
<div class="menu mainmenu">
<ul>
<li><a href="index.html">Home</a>
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
<li class='wideonly'><a href='about.html'>About</a>
<li class='desktoponly'><a href="docs.html">Documentation</a>
<li class='desktoponly'><a href="download.html">Download</a>
<li class='wideonly'><a href='copyright.html'>License</a>
<li class='desktoponly'><a href="support.html">Support</a>
<li class='desktoponly'><a href="prosupport.html">Purchase</a>
<li class='search' id='search_menubutton'>
<a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
</ul>
</div>
<div class="menu submenu" id="submenu">
<ul>
<li><a href='about.html'>About</a>
<li><a href='docs.html'>Documentation</a>
<li><a href='download.html'>Download</a>
<li><a href='support.html'>Support</a>
<li><a href='prosupport.html'>Purchase</a>
</ul>
</div>
<div class="searchmenu" id="searchmenu">
<form method="GET" action="search">
<select name="s" id="searchtype">
<option value="d">Search Documentation</option>
<option value="c">Search Changelog</option>
</select>
<input type="text" name="q" id="searchbox" value="">
<input type="submit" value="Go">
</form>
</div>
</div>
<script>
function toggle_div(nm) {
var w = document.getElementById(nm);
if( w.style.display=="block" ){
w.style.display = "none";
}else{
w.style.display = "block";
}
}
function toggle_search() {
var w = document.getElementById("searchmenu");
if( w.style.display=="block" ){
w.style.display = "none";
} else {
w.style.display = "block";
setTimeout(function(){
document.getElementById("searchbox").focus()
}, 30);
}
}
function div_off(nm){document.getElementById(nm).style.display="none";}
window.onbeforeunload = function(e){div_off("submenu");}
/* Disable the Search feature if we are not operating from CGI, since */
/* Search is accomplished using CGI and will not work without it. */
if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
document.getElementById("search_menubutton").style.display = "none";
}
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */
function hideorshow(btn,obj){
var x = document.getElementById(obj);
var b = document.getElementById(btn);
if( x.style.display!='none' ){
x.style.display = 'none';
b.innerHTML='show';
}else{
x.style.display = '';
b.innerHTML='hide';
}
return false;
}
var antiRobot = 0;
function antiRobotGo(){
if( antiRobot!=3 ) return;
antiRobot = 7;
var j = document.getElementById("mtimelink");
if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
}
function antiRobotDefense(){
document.body.onmousedown=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousedown=null;
}
document.body.onmousemove=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousemove=null;
}
setTimeout(function(){
antiRobot |= 1;
antiRobotGo();
}, 100)
antiRobotGo();
}
antiRobotDefense();
</script>
<h2>Datatypes In SQLite Version 2</h2>
<h3>1.0 Typelessness</h3>
<p>
SQLite is "typeless". This means that you can store any
kind of data you want in any column of any table, regardless of the
declared datatype of that column.
(See the one exception to this rule in section 2.0 below.)
This behavior is a feature, not
a bug. A database is supposed to store and retrieve data and it
should not matter to the database what format that data is in.
The strong typing system found in most other SQL engines and
codified in the SQL language spec is a misfeature -
it is an example of the implementation showing through into the
interface. SQLite seeks to overcome this misfeature by allowing
you to store any kind of data into any kind of column and by
allowing flexibility in the specification of datatypes.
</p>
<p>
A datatype to SQLite is any sequence of zero or more names
optionally followed by a parenthesized lists of one or two
signed integers. Notice in particular that a datatype may
be <em>zero</em> or more names. That means that an empty
string is a valid datatype as far as SQLite is concerned.
So you can declare tables where the datatype of each column
is left unspecified, like this:
</p>
<blockquote><pre>
CREATE TABLE ex1(a,b,c);
</pre></blockquote>
<p>
Even though SQLite allows the datatype to be omitted, it is
still a good idea to include it in your CREATE TABLE statements,
since the data type often serves as a good hint to other
programmers about what you intend to put in the column. And
if you ever port your code to another database engine, that
other engine will probably require a datatype of some kind.
SQLite accepts all the usual datatypes. For example:
</p>
<blockquote><pre>
CREATE TABLE ex2(
a VARCHAR(10),
b NVARCHAR(15),
c TEXT,
d INTEGER,
e FLOAT,
f BOOLEAN,
g CLOB,
h BLOB,
i TIMESTAMP,
j NUMERIC(10,5)
k VARYING CHARACTER (24),
l NATIONAL VARYING CHARACTER(16)
);
</pre></blockquote>
<p>
And so forth. Basically any sequence of names optionally followed by
one or two signed integers in parentheses will do.
</p>
<h3>2.0 The INTEGER PRIMARY KEY</h3>
<p>
One exception to the typelessness of SQLite is a column whose type
is INTEGER PRIMARY KEY. (And you must use "INTEGER" not "INT".
A column of type INT PRIMARY KEY is typeless just like any other.)
INTEGER PRIMARY KEY columns must contain a 32-bit signed integer. Any
attempt to insert non-integer data will result in an error.
</p>
<p>
INTEGER PRIMARY KEY columns can be used to implement the equivalent
of AUTOINCREMENT. If you try to insert a NULL into an INTEGER PRIMARY
KEY column, the column will actually be filled with an integer that is
one greater than the largest key already in the table. Or if the
largest key is 2147483647, then the column will be filled with a
random integer. Either way, the INTEGER PRIMARY KEY column will be
assigned a unique integer. You can retrieve this integer using
the <b>sqlite_last_insert_rowid()</b> API function or using the
<b>last_insert_rowid()</b> SQL function in a subsequent SELECT statement.
</p>
<h3>3.0 Comparison and Sort Order</h3>
<p>
SQLite is typeless for the purpose of deciding what data is allowed
to be stored in a column. But some notion of type comes into play
when sorting and comparing data. For these purposes, a column or
an expression can be one of two types: <b>numeric</b> and <b>text</b>.
The sort or comparison may give different results depending on which
type of data is being sorted or compared.
</p>
<p>
If data is of type <b>text</b> then the comparison is determined by
the standard C data comparison functions <b>memcmp()</b> or
<b>strcmp()</b>. The comparison looks at bytes from two inputs one
by one and returns the first non-zero difference.
Strings are '\000' terminated so shorter
strings sort before longer strings, as you would expect.
</p>
<p>
For numeric data, this situation is more complex. If both inputs
look like well-formed numbers, then they are converted
into floating point values using <b>atof()</b> and compared numerically.
If one input is not a well-formed number but the other is, then the
number is considered to be less than the non-number. If neither inputs
is a well-formed number, then <b>strcmp()</b> is used to do the
comparison.
</p>
<p>
Do not be confused by the fact that a column might have a "numeric"
datatype. This does not mean that the column can contain only numbers.
It merely means that if the column does contain a number, that number
will sort in numerical order.
</p>
<p>
For both text and numeric values, NULL sorts before any other value.
A comparison of any value against NULL using operators like "<" or
">=" is always false.
</p>
<h3>4.0 How SQLite Determines Datatypes</h3>
<p>
For SQLite version 2.6.3 and earlier, all values used the numeric datatype.
The text datatype appears in version 2.7.0 and later. In the sequel it
is assumed that you are using version 2.7.0 or later of SQLite.
</p>
<p>
For an expression, the datatype of the result is often determined by
the outermost operator. For example, arithmetic operators ("+", "*", "%")
always return a numeric results. The string concatenation operator
("||") returns a text result. And so forth. If you are ever in doubt
about the datatype of an expression you can use the special <b>typeof()</b>
SQL function to determine what the datatype is. For example:
</p>
<blockquote><pre>
sqlite> SELECT typeof('abc'+123);
numeric
sqlite> SELECT typeof('abc'||123);
text
</pre></blockquote>
<p>
For table columns, the datatype is determined by the type declaration
of the CREATE TABLE statement. The datatype is text if and only if
the type declaration contains one or more of the following strings:
</p>
<blockquote>
BLOB<br>
CHAR<br>
CLOB<br>
TEXT
</blockquote>
<p>
The search for these strings in the type declaration is case insensitive,
of course. If any of the above strings occur anywhere in the type
declaration, then the datatype of the column is text. Notice that
the type "VARCHAR" contains "CHAR" as a substring so it is considered
text.</p>
<p>If none of the strings above occur anywhere in the type declaration,
then the datatype is numeric. Note in particular that the datatype for columns
with an empty type declaration is numeric.
</p>
<h3>5.0 Examples</h3>
<p>
Consider the following two command sequences:
</p>
<blockquote><pre>
CREATE TABLE t1(a INTEGER UNIQUE); CREATE TABLE t2(b TEXT UNIQUE);
INSERT INTO t1 VALUES('0'); INSERT INTO t2 VALUES(0);
INSERT INTO t1 VALUES('0.0'); INSERT INTO t2 VALUES(0.0);
</pre></blockquote>
<p>In the sequence on the left, the second insert will fail. In this case,
the strings '0' and '0.0' are treated as numbers since they are being
inserted into a numeric column but 0==0.0 which violates the uniqueness
constraint. However, the second insert in the right-hand sequence works. In
this case, the constants 0 and 0.0 are treated a strings which means that
they are distinct.</p>
<p>SQLite always converts numbers into double-precision (64-bit) floats
for comparison purposes. This means that a long sequence of digits that
differ only in insignificant digits will compare equal if they
are in a numeric column but will compare unequal if they are in a text
column. We have:</p>
<blockquote><pre>
INSERT INTO t1 INSERT INTO t2
VALUES('12345678901234567890'); VALUES(12345678901234567890);
INSERT INTO t1 INSERT INTO t2
VALUES('12345678901234567891'); VALUES(12345678901234567891);
</pre></blockquote>
<p>As before, the second insert on the left will fail because the comparison
will convert both strings into floating-point number first and the only
difference in the strings is in the 20-th digit which exceeds the resolution
of a 64-bit float. In contrast, the second insert on the right will work
because in that case, the numbers being inserted are strings and are
compared using memcmp().</p>
<p>
Numeric and text types make a difference for the DISTINCT keyword too:
</p>
<blockquote><pre>
CREATE TABLE t3(a INTEGER); CREATE TABLE t4(b TEXT);
INSERT INTO t3 VALUES('0'); INSERT INTO t4 VALUES(0);
INSERT INTO t3 VALUES('0.0'); INSERT INTO t4 VALUES(0.0);
SELECT DISTINCT * FROM t3; SELECT DISTINCT * FROM t4;
</pre></blockquote>
<p>
The SELECT statement on the left returns a single row since '0' and '0.0'
are treated as numbers and are therefore indistinct. But the SELECT
statement on the right returns two rows since 0 and 0.0 are treated
a strings which are different.</p>
<p align="center"><small><i>This page last modified on <a href="https://sqlite.org/docsrc/honeypot" id="mtimelink" data-href="https://sqlite.org/docsrc/finfo/pages/datatypes.in?m=52a7afb2f5">2022-01-08 05:02:57</a> UTC </small></i></p>
|
