Search code examples
sqlitefloating-pointdecimalsqldatatypes

sqlite datatype for storing decimals


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.


Solution

  • 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.