If you're coming from another system, you might be familiar with words like float or double or on the other hand, a word like decimal. Coming from the MySQL world, predominantly, I'm familiar with float and double versus decimal. Now why do I put these on different sides? Because float and double are floating point numbers while decimal is not a floating point number and it is exact. The problem or the reality about floating point numbers is they are approximations.

Usually, they are very good approximations but once you start doing computation with floating point numbers, kinda all bets are off, honestly. In SQLite, there's no difference between, how you store fractional numbers. You can't say I wanna store these as floating point and these as decimal. There is only floating point and that is represented in the real data type. Realreal, that is the data type that stores floating point numbers.

I'm gonna show you a few examples and then there are some workarounds to get exact computations with floating point numbers, but we have to use an extension. So let me show you what we're doing here. I'm gonna go ahead and create a table called floats and we'll have one column in here, just a val, and I'm gonna declare it as a float. Now before I do that, if I were to call this a strict table or add the table modifier of strict and I try to run that, it's not gonna work because again real is the actual data type. Float declares an affinity of real, but real is the actual data type.

So I don't really care about strict for this example, so we're just gonna go with float which will make it a real column. Now if I were to insert into floats, I'm gonna insert a few values here. 26.3 and I always do that. Insert values, 26 point 3, 10.52. Let's make these negative.

10.52 is negative and we'll make 15.78 negative as well. So opening up Raycast here, let me just see if I were to do 26.3 less 10.52 less 15.78. Okay, I did that math right. So we should come out to 0 here. So select star from floats.

We've got all of our values in there. Select, let's get full screen. Select sum val from floats. And, oh shoot, super duper close. Super duper close to 0 but not actually 0.

This is not an issue with SQLite. If you've ever used JavaScript, you've run into this issue before. If you've ever used any other database and declared something as a float or a double instead of a decimal or an integer, you've run into this issue before. This is a reality of floating point numbers. There are a few ways we can work around this.

Let's take a look. If we were to do instead of select sum, if we were to select decimal sum, and I think the column name was value from, I think it was called floats, we do get our expected 0. Now this decimal sum comes from a decimal extension that you might have to load depending on your SQLite setup. It does not come in the amalgamation but it does come in the CLI which honestly is a little bit confusing for me. So if I were to take this over here so I'm on the CLI now.

If I were to hop into TablePlus and I was to check, let's get rid of all of that, and I was to check this here, it's gonna say no such function decimal sum because it's not built into my actual SQLite. It's in the CLI instead. So that is an extension that you will have to load. There is also select you can also, you can also, store these values as strings and pass them to these functions with arbitrarily long precision and that's gonna work just fine as well. Now, why would you wanna store them as strings if there is the real data type that is supposed to store these fractional values.

That's because the real data type guarantees 15 digits of precision, which honestly is a lot. And if you have super wide numbers, I can't say big because you might have 0 point and then like a 100 decimal places at the end. That's a wide number, but it's not like necessarily big as far as absolute value from 0. You can't you can't be guaranteed more than 15 digits of precision when you're using the real data type. You could store that as text and then use this decimal extension to calculate, to do computations on it.

But you can't you can't store something that big as a reel. So what is the happy what's the happy medium if you're like, well, you know, I I need some precision. I don't really need, you know, 15 digits of precision, but I would like my math to turn out correct, especially when dealing with something like prices. I think when you're dealing with something like, dollars and cents and prices and that sort of thing, you might be best served converting that value into the lowest denomination. So in the United States, our lowest denomination is cents.

So instead of instead of storing, let's say, instead of saying, this item is $99.99, which is, you know, not that many digits, but then you start doing math on it and you lose all your precision, you might store that value as 99 point 99 times 100. And that would store it that would store it as an integer. So you could put that into an integer column and you'd be just fine. So you have to convert it on the way in and you have to convert it on the way out which honestly that's a pretty good system. Denomination.

If you need if your system needs to support fractional cents, then you gotta go even further. So if you're for some reason your system says, well, something can be $99.99 and 97 partial¢. Why would you need that? I have no idea, but that changes your conversion function to have to add 2 more zeros so that you're storing it in the lowest denomination possible. So to recap, the real data type in SQLite guarantees 15 digits of precision, but once you start doing math on floating point numbers, you're kinda hosed, so you could use the decimal extension that is not a part of the amalgamation, but you can load it.

It does come in the CLI. Or you could convert your fractional values to full values, i.e. integers, by converting them to the lowest denomination possible, and then you're out of the woods, you have no problems dealing with integers, you're on a happy path.