Debian Codegraph @

www/floatingpoint.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>Floating Point Numbers</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 <div class=fancy>
119 <div class=nosearch>
120 <div class="fancy_title">
121 Floating Point Numbers
122 </div>
123 <details class="fancy_toc">
124 <summary>Table Of Contents</summary>
125 <div id="toc_sub"><div class="fancy-toc1"><a href="#how_sqlite_stores_numbers">1. How SQLite Stores Numbers</a></div>
126 <div class="fancy-toc2"><a href="#floating_point_accuracy">1.1. Floating-Point Accuracy</a></div>
127 <div class="fancy-toc2"><a href="#floating_point_numbers">1.2. Floating Point Numbers</a></div>
128 <div class="fancy-toc3"><a href="#unrepresentable_numbers">1.2.1. Unrepresentable numbers</a></div>
129 <div class="fancy-toc3"><a href="#is_it_close_enough_">1.2.2. Is it close enough?</a></div>
130 <div class="fancy-toc2"><a href="#rounding_floating_point_numbers_for_display">1.3. Rounding Floating-Point Numbers For Display</a></div>
131 <div class="fancy-toc3"><a href="#choosing_the_number_of_floating_point_digits_to_display">1.3.1. Choosing The Number Of Floating-Point Digits To Display</a></div>
132 <div class="fancy-toc1"><a href="#extensions_for_dealing_with_floating_point_numbers">2. Extensions For Dealing With Floating Point Numbers</a></div>
133 <div class="fancy-toc2"><a href="#the_ieee754_c_extension">2.1. The ieee754.c Extension</a></div>
134 <div class="fancy-toc3"><a href="#the_ieee754_function">2.1.1. The ieee754() function</a></div>
135 <div class="fancy-toc3"><a href="#the_ieee754_mantissa_and_ieee754_exponent_functions">2.1.2. The ieee754_mantissa() and ieee754_exponent() functions</a></div>
136 <div class="fancy-toc3"><a href="#the_ieee754_from_blob_and_ieee754_to_blob_functions">2.1.3. The ieee754_from_blob() and ieee754_to_blob() functions</a></div>
137 <div class="fancy-toc2"><a href="#the_decimal_c_extension">2.2. The decimal.c Extension</a></div>
138 <div class="fancy-toc3"><a href="#the_decimal_add_a_b_decimal_sub_a_b_and_decimal_mul_a_b_functions">2.2.1. The decimal_add(A,B), decimal_sub(A,B), and decimal_mul(A,B) functions</a></div>
139 <div class="fancy-toc3"><a href="#the_decimal_pow2_n_function">2.2.2. The decimal_pow2(N) function</a></div>
140 <div class="fancy-toc3"><a href="#the_decimal_x_and_decimal_exp_x_functions">2.2.3. The decimal(X) and decimal_exp(X) functions</a></div>
141 <div class="fancy-toc3"><a href="#the_decimal_cmp_x_function">2.2.4. The decimal_cmp(X) function</a></div>
142 <div class="fancy-toc3"><a href="#the_decimal_sum_x_aggregate_function">2.2.5. The decimal_sum(X) aggregate function</a></div>
143 <div class="fancy-toc3"><a href="#the_decimal_collating_sequence">2.2.6. The decimal collating sequence</a></div>
144 </div>
145 </details>
146 </div>
147
148
149
150
151
152
153 <h1 id="how_sqlite_stores_numbers"><span>1. </span>How SQLite Stores Numbers</h1>
154
155 <p>
156 SQLite stores integer values in the 64-bit
157 <a href="https://en.wikipedia.org/wiki/Two%27s_complement">twos-complement</a>
158 format&sup1.
159 This gives a storage range of -9223372036854775808 to +9223372036854775807,
160 inclusive. Integers within this range are exact.
161
162 <a name="fpapprox"></a>
163
164 </p><p>
165 So-called "REAL" or floating point values are stored in the
166 <a href="https://en.wikipedia.org/wiki/IEEE_754">IEEE 754</a>
167 <a href="https://en.wikipedia.org/wiki/Double-precision_floating-point_format">Binary-64</a>
168 format&sup1.
169 This gives a range of positive values between approximately
170 1.7976931348623157e+308 and 4.9406564584124654e-324 with an equivalent
171 range of negative values. A binary-64 can also be 0.0 (and -0.0), positive
172 and negative infinity and "NaN" or "Not-a-Number". Floating point
173 values are approximate.
174
175 </p><p>
176 Pay close attention to the previous sentence:
177 </p><blockquote><b>
178 Floating point values are approximate.
179 </b></blockquote>
180
181 <p>
182 If you need an exact answer, you should not use binary-64 floating-point
183 values, in SQLite or in any other product. This is not an SQLite limitation.
184 It is a mathematical limitation inherent in the design of floating-point numbers.
185
186 </p><p>&mdash;<br>&sup1;
187 Exception: The <a href="rtree.html">R-Tree extension</a> stores information as 32-bit floating
188 point or integer values.
189
190 </p><h2 id="floating_point_accuracy"><span>1.1. </span>Floating-Point Accuracy</h2>
191
192 <p>
193 SQLite will preserve as many significant digits of a floating
194 point value as it can. But an IEEE 754 double only holds about
195 15.95 significant digits. Furthermore, SQLite makes no guarantees that
196 the accuracy of computations on floating point values will even be
197 this accurate, because no such guarantees are possible.
198 Performing math on floating-point values introduces error.
199 For example, consider what happens if you attempt to subtract two floating-point
200 numbers of similar magnitude:
201
202 </p><blockquote>
203 <table border="0" cellpadding="0" cellspacing="0">
204 <tr><td align="right">1152693165.1106291898</td></tr>
205 <tr><td align="right">-1152693165.1106280772</td></tr>
206 <tr><td><hr>
207 </td></tr><tr><td align="right">0.0000011126
208 </td></tr></table>
209 </blockquote>
210
211 <p>The result shown above (0.0000011126) is the correct answer. But if you
212 do this computation using binary-64 floating-point, the answer you get is
213 0.00000095367431640625 - an error of about 14%. If you do many similar
214 computations as part of your program, the errors add up so that your final
215 result might be completely meaningless.
216
217 </p><p>The error arises because only about 15.95 significant digits of
218 each number are stored accurately using the IEEE 754 binary-64 format, and the
219 first difference between the two numbers being subtracted is in the 16th digit.
220
221 </p><h2 id="floating_point_numbers"><span>1.2. </span>Floating Point Numbers</h2>
222
223 <p>
224 The binary-64 floating-point format uses 64 bits per number. Hence there
225 are 1.845e+19 different possible floating point values. On the other hand
226 there are infinitely many real numbers in the range of
227 1.7977e+308 and 4.9407e-324. It follows then that binary-64 cannot possibly
228 represent all possible real numbers within that range. Approximations are
229 required.
230
231 </p><p>
232 An IEEE 754 floating-point value is an integer multiplied by a power
233 of two:
234
235 </p><blockquote>
236 <big>M &times 2<sup><small>E</small></sup></big>
237 </blockquote>
238
239 <p>The M value is the "mantissa" and E is the "exponent". Both
240 M and E are integers.
241
242 </p><p>For Binary-64, M is a 53-bit integer and E is an 11-bit integer that is
243 offset so that represents a range of values between -1074 and +972, inclusive.
244
245 </p><p><i>(NB: The usual description of IEEE 754 is more complex, and it is important
246 to understand the added complexity if you really want to appreciate the details,
247 merits, and limitations of IEEE 754. However, the integer description shown
248 here, while not exactly right, is easier to understand and is sufficient for
249 the purposes of this article.)</i></p>
250
251 <h3 id="unrepresentable_numbers"><span>1.2.1. </span>Unrepresentable numbers</h3>
252
253 <p>Not every decimal number with fewer than 16 significant digits can be
254 represented exactly as a binary-64 number. In fact, most decimal numbers
255 with digits to the right of the decimal point lack an exact binary-64
256 equivalent. For example, if you have a database column that is intended
257 to hold an item price in dollars and cents, the only cents value that
258 can be exactly represented are 0.00, 0.25, 0.50, and 0.75. Any other
259 numbers to the right of the decimal point result in an approximation.
260 If you provide a "price" value of 47.49, that number will be represented
261 in binary-64 as:
262
263 </p><blockquote>
264 6683623321994527 &times; 2<sup><small>-47</small></sup>
265 </blockquote>
266
267 <p>Which works out to be:
268
269 </p><blockquote>
270 47.49000000000000198951966012828052043914794921875
271 </blockquote>
272
273 <p>That number is very close to 47.49, but it is not exact. It is a little
274 too big. If we reduce M by one to 6683623321994526 so that we have the
275 next smaller possible binary-64 value, we get:
276
277 </p><blockquote>
278 47.4899999999999948840923025272786617279052734375
279 </blockquote>
280
281
282 <p>
283 This second number is too small.
284 The first number is closer to the desired value of 47.49, so that is the
285 one that gets used. But it is not exact. Most decimal values work this
286 way in IEEE 754. Remember the key point we made above:
287
288 </p><blockquote><b>
289 Floating point values are approximate.
290 </b></blockquote>
291
292 <p>If you remember nothing else about floating-point values,
293 please don't forget this one key idea.
294
295 </p><h3 id="is_it_close_enough_"><span>1.2.2. </span>Is it close enough?</h3>
296
297 <p>The precision provided by IEEE 754 Binary-64 is sufficient for most computations.
298 For example, if "47.49" represents a price and inflation is running
299 at 2% per year, then the price is going up by about 0.0000000301 dollars per
300 second. The error in the recorded value of 47.49 represents about 66 nanoseconds
301 worth of inflation. So if the 47.49 price is exact
302 when you enter it, then the effects of inflation will cause the true value to
303 exactly equal the value actually stored
304 (47.4900000000000019895196601282805204391479492187) in less than
305 one ten-millionth of a second.
306 Surely that level of precision is sufficient for most purposes?
307
308 <a name="*rounding"></a>
309
310 </p><h2 id="rounding_floating_point_numbers_for_display"><span>1.3. </span>Rounding Floating-Point Numbers For Display</h2>
311
312 <p>
313 When converting a binary-64 number into text for display purposes
314 (for example by calling <a href="c3ref/column_blob.html">sqlite3_column_text()</a> or <a href="c3ref/value_blob.html">sqlite3_value_text()</a>
315 or by using the <a href="lang_expr.html#castexpr">CAST operator</a> in SQL), the value
316 is normally rounded to a reasonable number of significant digits. One
317 rarely wants to see all 49 signficant digits of
318 6683623321994527&times;pow(2,-47). The first four digits, "47.49" in this case,
319 is usually a better display option.
320
321 </p><p>
322 There are also performance reasons for rounding.
323 6683623321994527&times;pow(2,-47) can be converted into "47.49" relatively
324 quickly, but generating the full 49-digit expansion requires a lot more
325 computation.
326
327 </p><p>At what point should rounding occur?
328 Rounding to 17 significant digits or more results in text that, when coverted
329 back into binary-64, gives the exact same value that we started with.
330 Rounding to 15 significant digits or less can often gives this exact round-trip result,
331 but not always.
332 On the other hand, rounding to 15 digits tends to give
333 results that are more pleasing to human readers, whereas rounding to 17
334 digits can result in long numbers with a lot of '0's or '9's in the middle.
335
336 </p><p>Prior to SQLite 3.52.0 (2026-03-06), if you tried to round
337 6683623321994527&times;pow(2,-47) to 17 digits, SQLite would look at the first
338 18 digits ("47.4900000000000019"), see that the last digit was 5 or more
339 and hence round up to "47.490000000000002". For that reason, in earlier versions
340 of SQLite, rounding occurred at 15 digits. It would look at the 16th digit,
341 see that it was zero and hence just truncate, then remove trailing zeros,
342 and you are left with "47.49", which is the answer most people want.
343 And it turns out that converting text "47.49" back to binary-64 results
344 in 6683623321994527&times;pow(2,-47), so the value does not change if you
345 round-trip binary-64&rarr;text&rarr;binary-64. All is well.
346
347 </p><p>But this does not work for every case.
348 Consider, for example, the product of 1.23 and 2.34.
349 The number 1.23 is:
350
351 </p><blockquote>
352 2769713770832855 &times 2<sup><small>-51</small></sup><br>
353 &emsp;&emsp; = 1.229999999999999982236431605997495353221893310546875
354 </blockquote>
355
356 <p>And 2.34 is:
357
358 </p><blockquote>
359 5269211564023480 &times 2<sup><small>-51</small></sup><br>
360 &emsp;&emsp; = 2.339999999999999857891452847979962825775146484375
361 </blockquote>
362
363 <p>If you do an exact multiplication of those two values you get:
364
365 </p><blockquote>
366 2.878199999999999783639736961049495926597557229698714817531408904915934954260592348873615264892578125
367 </blockquote>
368
369 <p>Which cannot be represented in binary-64. The floating point hardware
370 in your CPU truncates this to:
371
372 </p><blockquote>
373 6481130223748880 &times; 2<sup><small>-51</small></sup><br>
374 &emsp;&emsp; = 2.87819999999999964757080306299030780792236328125
375 </blockquote>
376
377 <p>If you round that last value to 15 significant digits, you get 2.8782, which is
378 the answer most people expect. If you round to 17 digits, you get
379 2.8781999999999997 instead. However, 2.8782 and 2.8781999999999997 are not
380 the same number in binary-64.
381
382 </p><blockquote>
383 2.8782 &rarr; 6481130223748881 &times; 2<sup><small>-51</small></sup><br>
384 2.8781999999999997 &rarr; 6481130223748880 &times; 2<sup><small>-51</small></sup>
385 </blockquote>
386
387 <p>The mantissa is different by one. So converting the text "2.8782" back to
388 binary-64 will <u>not</u> go back to the original product of 1.23&times;2.34.
389 It will be very close, but not exact. Converting the text "2.8781999999999997"
390 back to binary-64 <u>does</u> go back to original value though. And so in
391 that sense, 2.8781999999999997 is a more accurate answer.
392
393 <a name="*fpdigits"></a>
394
395 </p><h3 id="choosing_the_number_of_floating_point_digits_to_display"><span>1.3.1. </span>Choosing The Number Of Floating-Point Digits To Display</h3>
396
397 <p>So there is a trade-off. Do you (A) round to 15 digits, and provide answers
398 that are closer to what humans expect to see, or do you (B) round to 17 digits and
399 provide answers that sometimes have lots '0's or '9's after the decimal point
400 but which will round-trip back to the original binary-64?
401
402 </p><p>With SQLite 3.51.2 and earlier, solution (A) was the only option. Beginning
403 with SQLite 3.52.0 (2026-03-06), solution (B) is available and is the default,
404 though applications can choose to do back to solution (A). In C-code, one can
405 choose option (A) by running code like this:
406
407 </p><blockquote>
408 sqlite3_db_config(db, <a href="c3ref/c_dbconfig_defensive.html#sqlitedbconfigfpdigits">SQLITE_DBCONFIG_FP_DIGITS</a>, 15, 0);
409 </blockquote>
410
411 <p>Or in the <a href="cli.html">CLI</a>, one can do:
412
413 </p><blockquote>
414 .dbconfig fp_digits 15
415 </blockquote>
416
417 <p>This change occurred because in version 3.52.0, the rounding algorithm
418 was enhanced to work something like this:
419
420 </p><ol type="a">
421 <li> Round to 15 digits.
422 </li><li> Convert the text result back to binary-64.
423 </li><li> If the binary-64 value from the previous step is different, then:<br>
424 &emsp;&emsp;&bull; Redo step (a) with 17-digit rounding.
425 </li></ol>
426
427 <p> Rounding to 15 digits is usually enough.
428 Step (c) seldom occurs.
429 The text renderings with lots of '0's and '9's only appear
430 in the unusual cases where they make a difference. The reason this enhancement
431 was made in version 3.52.0 was that the binary-64&rarr;text&rarr;binary-64 conversion
432 routines were rewritten to use 64-bit integer arithmetic instead of floating-point
433 computations, and so step (b) in the algorithm above became computationally
434 reasonable to perform. The binary-64&rarr;text conversion in 3.52.0 is still
435 faster than before, even with the addition of steps (b) and (c).
436 If you do not want the (negligible) overhead of step (b) and (c), just
437 set <a href="c3ref/c_dbconfig_defensive.html#sqlitedbconfigfpdigits">SQLITE_DBCONFIG_FP_DIGITS</a> to 15.
438
439 </p><h1 id="extensions_for_dealing_with_floating_point_numbers"><span>2. </span>Extensions For Dealing With Floating Point Numbers</h1>
440
441 <a name="ieee754ext"></a>
442
443 <h2 id="the_ieee754_c_extension"><span>2.1. </span>The ieee754.c Extension</h2>
444
445 <p>The ieee754 extension converts a floating point number between its
446 binary-64 representation and the M&times;2<sup><small>E</small></sup> format.
447 In other words in the expression:
448
449 </p><blockquote>
450 <big>F = M &times 2<sup><small>E</small></sup></big>
451 </blockquote>
452
453 <p>The ieee754 extension converts between F and (M,E) and back again.
454
455 </p><p>The ieee754 extension is not part of the <a href="amalgamation.html">amalgamation</a>, but it is included
456 by default in the <a href="cli.html">CLI</a>. If you want to include the ieee754 extension in your
457 application, you will need to compile and load it separately.
458
459 <a name="ieee754"></a>
460
461 </p><h3 id="the_ieee754_function"><span>2.1.1. </span>The ieee754() function</h3>
462
463 <p>The ieee754(F) SQL function takes a single floating-point argument
464 as its input and returns a string that looks like this:
465
466 </p><blockquote>
467 'ieee754(M,E)'
468 </blockquote>
469
470 <p>Except that the M and E are replaced by the mantissa and exponent of the
471 floating point number. For example:
472
473 </p><div class="codeblock"><pre>sqlite> .mode box
474 sqlite> SELECT ieee754(47.49) AS x;
475 ┌───────────────────────────────┐
476 │ x │
477 ├───────────────────────────────┤
478 │ ieee754(6683623321994527,-47) │
479 └───────────────────────────────┘
480 </pre></div>
481
482 <p>
483 Going in the other direction, the 2-argument version of ieee754() takes
484 the M and E values and converts them into the corresponding F value:
485
486 </p><div class="codeblock"><pre>sqlite> select ieee754(6683623321994527,-47) as x;
487 ┌───────┐
488 │ x │
489 ├───────┤
490 │ 47.49 │
491 └───────┘
492 </pre></div>
493
494 <a name="ieee754m"></a>
495
496 <h3 id="the_ieee754_mantissa_and_ieee754_exponent_functions"><span>2.1.2. </span>The ieee754_mantissa() and ieee754_exponent() functions</h3>
497
498 <p>The text output of the one-argument form of ieee754() is great for human
499 readability, but it is awkward to use as part of a larger expression. Hence
500 the ieee754_mantissa() and ieee754_exponent() routines were added to return
501 the M and E values corresponding to their single argument F
502 value.
503 For example:
504
505 </p><div class="codeblock"><pre>sqlite> .mode box
506 sqlite> SELECT ieee754_mantissa(47.49) AS M, ieee754_exponent(47.49) AS E;
507 ┌──────────────────┬─────┐
508 │ M │ E │
509 ├──────────────────┼─────┤
510 │ 6683623321994527 │ -47 │
511 └──────────────────┴─────┘
512 </pre></div>
513
514 <a name="ieee754b"></a>
515
516 <h3 id="the_ieee754_from_blob_and_ieee754_to_blob_functions"><span>2.1.3. </span>The ieee754_from_blob() and ieee754_to_blob() functions</h3>
517
518 <p>The ieee754_to_blob(F) SQL function converts the floating point number F
519 into an 8-byte BLOB that is the big-endian binary-64 encoding of that number.
520 The ieee754_from_blob(B) function goes the other way, converting an 8-byte
521 blob into the floating-point value that the binary-64 encoding represents.
522
523 </p><p>So, for example, if you read
524 <a href="https://en.wikipedia.org/wiki/Double-precision_floating-point_format">on
525 Wikipedia</a> that the encoding for the minimum positive binary-64 value is
526 0x0000000000000001, then you can find the corresponding floating point value
527 like this:
528
529 </p><div class="codeblock"><pre>sqlite> .mode box
530 sqlite> SELECT ieee754_from_blob(x'0000000000000001') AS F;
531 ┌───────────────────────┐
532 │ F │
533 ├───────────────────────┤
534 │ 4.94065645841247e-324 │
535 └───────────────────────┘
536 </pre></div>
537
538 <p>Or go the other way:
539
540 </p><div class="codeblock"><pre>sqlite> .mode box
541 sqlite> SELECT quote(ieee754_to_blob(4.94065645841247e-324)) AS binary-64;
542 ┌─────────────────────┐
543 │ binary-64 │
544 ├─────────────────────┤
545 │ X'0000000000000001' │
546 └─────────────────────┘
547 </pre></div>
548
549 <a name="decext"></a>
550
551 <h2 id="the_decimal_c_extension"><span>2.2. </span>The decimal.c Extension</h2>
552
553 <p>The decimal extension provides arbitrary-precision decimal arithmetic on
554 numbers stored as text strings. Because the numbers are stored to arbitrary
555 precision and as text, no approximations are needed. Computations can be
556 done exactly.
557
558 </p><p>The decimal extension is not (currently) part of the SQLite <a href="amalgamation.html">amalgamation</a>.
559 However, it is included in the <a href="cli.html">CLI</a>. The source code to this extension
560 can be found at
561 <a href="https://sqlite.org/src/file/ext/misc/decimal.c?ci=trunk">ext/misc/decimal.c</a>.
562
563 </p><p>The decimal extension supports the following SQL functions and collating sequences:
564
565 <a name="*decmth"></a>
566
567 </p><h3 id="the_decimal_add_a_b_decimal_sub_a_b_and_decimal_mul_a_b_functions"><span>2.2.1. </span>The decimal_add(A,B), decimal_sub(A,B), and decimal_mul(A,B) functions</h3>
568
569 <p>These functions compute the sum, difference, and product, respectively, of
570 the two inputs A and B and return the result as text. The inputs can be either
571 decimal text or numeric values. Numeric inputs are converted into decimal
572 text before the computation is performed.
573
574 </p><p>There is no "decimal_div(A,B)" function because division does not always
575 have a finite-length decimal result.
576
577 <a name="decimal_pow2"></a>
578
579 </p><h3 id="the_decimal_pow2_n_function"><span>2.2.2. </span>The decimal_pow2(N) function</h3>
580
581 <p>The decimal_pow2(N) function computes the exact decimal representation of
582 the N-th power of 2. N must be an integer between -20000 and +20000.
583
584 </p><p>This function can be slow and can use a lot of memory for large values of N.
585
586 <a name="decimal"></a>
587
588 </p><h3 id="the_decimal_x_and_decimal_exp_x_functions"><span>2.2.3. </span>The decimal(X) and decimal_exp(X) functions</h3>
589
590 <p>The decimal(X) and decimal_exp(X) generate a decimal representation for input X.
591 The input X can be an integer or a floating point number, or a text decimal.
592 The decimal_exp(X) function returns the result in exponential notation (with a "e+NN"
593 at the end) and decimal(X) returns a pure decimal (without the "e+NN").
594
595 </p><p>If the input
596 X is a floating point value, it is expanded to its exact decimal equivalent. For
597 example:
598
599 </p><div class="codeblock"><pre>sqlite> .mode qbox
600 sqlite> select decimal(47.49);
601 ┌──────────────────────────────────────────────────────┐
602 │ decimal(47.49) │
603 ├──────────────────────────────────────────────────────┤
604 │ '47.49000000000000198951966012828052043914794921875' │
605 └──────────────────────────────────────────────────────┘
606 </pre></div>
607
608 <a name="decimal_cmp"></a>
609
610 <h3 id="the_decimal_cmp_x_function"><span>2.2.4. </span>The decimal_cmp(X) function</h3>
611
612 <p>The decimal_cmp(A,B) function compares two decimal values A and B. The result will
613 be negative, zero, or positive if A is less than, equal to, or greater than B,
614 respectively.
615
616 <a name="decimal_sum"></a>
617
618 </p><h3 id="the_decimal_sum_x_aggregate_function"><span>2.2.5. </span>The decimal_sum(X) aggregate function</h3>
619
620 <p>The decimal_sum(X) function is an aggregate, like the built-in
621 <a href="lang_aggfunc.html#sumunc">total() aggregate function</a>, except that decimal_sum() computes its result
622 to arbitrary precision and is therefore precise.
623
624 <a name="*deccseq"></a>
625
626 </p><h3 id="the_decimal_collating_sequence"><span>2.2.6. </span>The decimal collating sequence</h3>
627
628 <p>The decimal extension provides the "decimal" collating sequence
629 that compares decimal text strings in numeric order.
630 </p><p align="center"><small><i>This page was last updated on 2026-05-20 11:35:23Z </small></i></p>
631