Numerical precision in SQLite


About numerical precision in SQLite v3.20.0. See the command sequence below and the .dump result. The value associated with the INSERT statement is not the same value as that obtained. I enter the value 123456.789 and I get 123456.7890000000043. How can this happen?

SQLite version 3.20.0 2017-08-01 13:24:15
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE Teste (Campo Number(9,3));
sqlite> INSERT INTO Teste Values (123456.789);
sqlite> SELECT * From Teste;
sqlite> .dump
PRAGMA foreign_keys=OFF;
CREATE TABLE Teste (Campo Number(9,3));
INSERT INTO Teste VALUES(123456.7890000000043);
asked by anonymous 06.09.2017 / 21:41

1 answer


The binary floating-point math is like this. In most programming languages (not only in SQLLite), it is based on the IEEE 754 standard. For example, JavaScript uses the 64-bit floating-point representation, which is the same as double Java. The crux of the problem is that the numbers are represented in this format, since an integer has a power of two; rational numbers (such as 0,1, which is 1/10) whose denominator is not a power of two, can not be exactly represented. Here are the references: Wiki - Float Binary Representation

06.09.2017 / 22:18