Debian Codegraph @

www/datatypes.html

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