1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
3
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
4
<title>Datatypes In SQLite version 2</title>
5
<style type="text/css">
8
font-family: Verdana, sans-serif;
13
a:visited { color: #734559 }
15
.logo { position:absolute; margin:3px; }
31
.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }
32
.toolbar a:visited { color: white; }
33
.toolbar a:hover { color: #044a64; background: white; }
35
.content { margin: 5%; }
36
.content dt { font-weight:bold; }
37
.content dd { margin-bottom: 25px; margin-left:20%; }
38
.content ul { padding:0px; padding-left: 15px; margin:0px; }
41
.se { background: url(images/se.gif) 100% 100% no-repeat #044a64}
42
.sw { background: url(images/sw.gif) 0% 100% no-repeat }
43
.ne { background: url(images/ne.gif) 100% 0% no-repeat }
44
.nw { background: url(images/nw.gif) 0% 0% no-repeat }
46
/* Things for "fancyformat" documents start here. */
47
.fancy img+p {font-style:italic}
48
.fancy .codeblock i { color: darkblue; }
49
.fancy h1,.fancy h2,.fancy h3,.fancy h4 {font-weight:normal;color:#044a64}
50
.fancy h2 { margin-left: 10px }
51
.fancy h3 { margin-left: 20px }
52
.fancy h4 { margin-left: 30px }
53
.fancy th {white-space:nowrap;text-align:left;border-bottom:solid 1px #444}
54
.fancy th, .fancy td {padding: 0.2em 1ex; vertical-align:top}
55
.fancy #toc a { color: darkblue ; text-decoration: none }
56
.fancy .todo { color: #AA3333 ; font-style : italic }
57
.fancy .todo:before { content: 'TODO:' }
58
.fancy p.todo { border: solid #AA3333 1px; padding: 1ex }
59
.fancy img { display:block; }
60
.fancy :link:hover, .fancy :visited:hover { background: wheat }
61
.fancy p,.fancy ul,.fancy ol { margin: 1em 5ex }
62
.fancy li p { margin: 1em 0 }
63
/* End of "fancyformat" specific rules. */
69
<div><!-- container div to satisfy validator -->
72
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite Logo"
74
<div><!-- IE hack to prevent disappearing logo--></div>
75
<div class="tagline">Small. Fast. Reliable.<br>Choose any three.</div>
77
<table width=100% style="clear:both"><tr><td>
78
<div class="se"><div class="sw"><div class="ne"><div class="nw">
79
<table width=100% style="padding:0;margin:0;cell-spacing:0"><tr>
82
<a href="about.html">About</a>
83
<a href="sitemap.html">Sitemap</a>
84
<a href="docs.html">Documentation</a>
85
<a href="download.html">Download</a>
86
<a href="copyright.html">License</a>
87
<a href="news.html">News</a>
88
<a href="support.html">Support</a>
91
gMsg = "Search SQLite Docs..."
92
function entersearch() {
93
var q = document.getElementById("q");
94
if( q.value == gMsg ) { q.value = "" }
95
q.style.color = "black"
96
q.style.fontStyle = "normal"
98
function leavesearch() {
99
var q = document.getElementById("q");
100
if( q.value == "" ) {
102
q.style.color = "#044a64"
103
q.style.fontStyle = "italic"
108
<div style="padding:0 1em 0px 0;white-space:nowrap">
109
<form name=f method="GET" action="http://www.sqlite.org/search">
110
<input id=q name=q type=text
111
onfocus="entersearch()" onblur="leavesearch()" style="width:24ex;padding:1px 1ex; border:solid white 1px; font-size:0.9em ; font-style:italic;color:#044a64;" value="Search SQLite Docs...">
112
<input type=submit value="Go" style="border:solid white 1px;background-color:#044a64;color:white;font-size:0.9em;padding:0 1ex">
116
</div></div></div></div>
118
<div class=startsearch></div>
122
<h2>Datatypes In SQLite Version 2</h2>
124
<h3>1.0 Typelessness</h3>
126
SQLite is "typeless". This means that you can store any
127
kind of data you want in any column of any table, regardless of the
128
declared datatype of that column.
129
(See the one exception to this rule in section 2.0 below.)
130
This behavior is a feature, not
131
a bug. A database is suppose to store and retrieve data and it
132
should not matter to the database what format that data is in.
133
The strong typing system found in most other SQL engines and
134
codified in the SQL language spec is a misfeature -
135
it is an example of the implementation showing through into the
136
interface. SQLite seeks to overcome this misfeature by allowing
137
you to store any kind of data into any kind of column and by
138
allowing flexibility in the specification of datatypes.
142
A datatype to SQLite is any sequence of zero or more names
143
optionally followed by a parenthesized lists of one or two
144
signed integers. Notice in particular that a datatype may
145
be <em>zero</em> or more names. That means that an empty
146
string is a valid datatype as far as SQLite is concerned.
147
So you can declare tables where the datatype of each column
148
is left unspecified, like this:
152
CREATE TABLE ex1(a,b,c);
156
Even though SQLite allows the datatype to be omitted, it is
157
still a good idea to include it in your CREATE TABLE statements,
158
since the data type often serves as a good hint to other
159
programmers about what you intend to put in the column. And
160
if you ever port your code to another database engine, that
161
other engine will probably require a datatype of some kind.
162
SQLite accepts all the usual datatypes. For example:
177
k VARYING CHARACTER (24),
178
l NATIONAL VARYING CHARACTER(16)
183
And so forth. Basically any sequence of names optionally followed by
184
one or two signed integers in parentheses will do.
187
<h3>2.0 The INTEGER PRIMARY KEY</h3>
190
One exception to the typelessness of SQLite is a column whose type
191
is INTEGER PRIMARY KEY. (And you must use "INTEGER" not "INT".
192
A column of type INT PRIMARY KEY is typeless just like any other.)
193
INTEGER PRIMARY KEY columns must contain a 32-bit signed integer. Any
194
attempt to insert non-integer data will result in an error.
198
INTEGER PRIMARY KEY columns can be used to implement the equivalent
199
of AUTOINCREMENT. If you try to insert a NULL into an INTEGER PRIMARY
200
KEY column, the column will actually be filled with an integer that is
201
one greater than the largest key already in the table. Or if the
202
largest key is 2147483647, then the column will be filled with a
203
random integer. Either way, the INTEGER PRIMARY KEY column will be
204
assigned a unique integer. You can retrieve this integer using
205
the <b>sqlite_last_insert_rowid()</b> API function or using the
206
<b>last_insert_rowid()</b> SQL function in a subsequent SELECT statement.
209
<h3>3.0 Comparison and Sort Order</h3>
212
SQLite is typeless for the purpose of deciding what data is allowed
213
to be stored in a column. But some notion of type comes into play
214
when sorting and comparing data. For these purposes, a column or
215
an expression can be one of two types: <b>numeric</b> and <b>text</b>.
216
The sort or comparison may give different results depending on which
217
type of data is being sorted or compared.
221
If data is of type <b>text</b> then the comparison is determined by
222
the standard C data comparison functions <b>memcmp()</b> or
223
<b>strcmp()</b>. The comparison looks at bytes from two inputs one
224
by one and returns the first non-zero difference.
225
Strings are '\000' terminated so shorter
226
strings sort before longer strings, as you would expect.
230
For numeric data, this situation is more complex. If both inputs
231
look like well-formed numbers, then they are converted
232
into floating point values using <b>atof()</b> and compared numerically.
233
If one input is not a well-formed number but the other is, then the
234
number is considered to be less than the non-number. If neither inputs
235
is a well-formed number, then <b>strcmp()</b> is used to do the
240
Do not be confused by the fact that a column might have a "numeric"
241
datatype. This does not mean that the column can contain only numbers.
242
It merely means that if the column does contain a number, that number
243
will sort in numerical order.
247
For both text and numeric values, NULL sorts before any other value.
248
A comparison of any value against NULL using operators like "<" or
249
">=" is always false.
252
<h3>4.0 How SQLite Determines Datatypes</h3>
255
For SQLite version 2.6.3 and earlier, all values used the numeric datatype.
256
The text datatype appears in version 2.7.0 and later. In the sequel it
257
is assumed that you are using version 2.7.0 or later of SQLite.
261
For an expression, the datatype of the result is often determined by
262
the outermost operator. For example, arithmetic operators ("+", "*", "%")
263
always return a numeric results. The string concatenation operator
264
("||") returns a text result. And so forth. If you are ever in doubt
265
about the datatype of an expression you can use the special <b>typeof()</b>
266
SQL function to determine what the datatype is. For example:
270
sqlite> SELECT typeof('abc'+123);
272
sqlite> SELECT typeof('abc'||123);
277
For table columns, the datatype is determined by the type declaration
278
of the CREATE TABLE statement. The datatype is text if and only if
279
the type declaration contains one or more of the following strings:
290
The search for these strings in the type declaration is case insensitive,
291
of course. If any of the above strings occur anywhere in the type
292
declaration, then the datatype of the column is text. Notice that
293
the type "VARCHAR" contains "CHAR" as a substring so it is considered
296
<p>If none of the strings above occur anywhere in the type declaration,
297
then the datatype is numeric. Note in particular that the datatype for columns
298
with an empty type declaration is numeric.
301
<h3>5.0 Examples</h3>
304
Consider the following two command sequences:
308
CREATE TABLE t1(a INTEGER UNIQUE); CREATE TABLE t2(b TEXT UNIQUE);
309
INSERT INTO t1 VALUES('0'); INSERT INTO t2 VALUES(0);
310
INSERT INTO t1 VALUES('0.0'); INSERT INTO t2 VALUES(0.0);
313
<p>In the sequence on the left, the second insert will fail. In this case,
314
the strings '0' and '0.0' are treated as numbers since they are being
315
inserted into a numeric column but 0==0.0 which violates the uniqueness
316
constraint. However, the second insert in the right-hand sequence works. In
317
this case, the constants 0 and 0.0 are treated a strings which means that
318
they are distinct.</p>
320
<p>SQLite always converts numbers into double-precision (64-bit) floats
321
for comparison purposes. This means that a long sequence of digits that
322
differ only in insignificant digits will compare equal if they
323
are in a numeric column but will compare unequal if they are in a text
327
INSERT INTO t1 INSERT INTO t2
328
VALUES('12345678901234567890'); VALUES(12345678901234567890);
329
INSERT INTO t1 INSERT INTO t2
330
VALUES('12345678901234567891'); VALUES(12345678901234567891);
333
<p>As before, the second insert on the left will fail because the comparison
334
will convert both strings into floating-point number first and the only
335
difference in the strings is in the 20-th digit which exceeds the resolution
336
of a 64-bit float. In contrast, the second insert on the right will work
337
because in that case, the numbers being inserted are strings and are
338
compared using memcmp().</p>
341
Numeric and text types make a difference for the DISTINCT keyword too:
345
CREATE TABLE t3(a INTEGER); CREATE TABLE t4(b TEXT);
346
INSERT INTO t3 VALUES('0'); INSERT INTO t4 VALUES(0);
347
INSERT INTO t3 VALUES('0.0'); INSERT INTO t4 VALUES(0.0);
348
SELECT DISTINCT * FROM t3; SELECT DISTINCT * FROM t4;
352
The SELECT statement on the left returns a single row since '0' and '0.0'
353
are treated as numbers and are therefore indistinct. But the SELECT
354
statement on the right returns two rows since 0 and 0.0 are treated
355
a strings which are different.</p>
added
removed
datatypes.html
