I would like to store cryptocurrency financial data in sqlite, but am not able to figure out how the precision of the numbers is handled.. The crypto precision/scale could go as low as 8 digits, and the real number part to 10-12 digits. I.e. following number is possible: 123456789111.12345678
If we init a table with a record:
PRAGMA journal_mode=WAL;
PRAGMA foreign_keys = ON;
CREATE TABLE datas (
id INTEGER PRIMARY KEY AUTOINCREMENT,
price decimal NOT NULL
);
insert into datas (price) values (123456789111.12345678);
then the inserted record will be rounded to 123456789111.12346.
123456789111.12346 is only a 16 digit number. The input was a 20 digit number. Why does the sqlite is only storing 16 digits?
I am planning on using it with the C#, where decimals could be up to 28 digits long. The 16 digits actually correlate with the double datatype, not a decimal, according to C# datatypes.
What I have tried doing is setting the column type to REAL, CURRENCY, DECIMAL, DECIMAL(28,8), MONEY, nothing seems to work.. I know for sure that other databases such as sqlserver are able to handle decimals just fine.. So perhaps there is something I am missing with the sqlite?
using sqlite version 3.43.2
I am sorry if the question has been answered someplace. I was not able to find the exact answer on stackoverflow. Most of the answers/questions were 10 years old, but if I am wrong please point me to the right direction :) thank you.
While unintuitive, the only way around this I've found is to just store the full decimal number as datatype TEXT and parse it back to a decimal in code. As you mentioned, DECIMAL only has 16 digit precision. TEXT will allow you to store numbers as strings with much higher precision.