Working around these sorts of problems with integers is just not worth the trouble.
https://stackoverflow.com/questions/3730019/why-not-use-doub...
I can understand the convenience if you are setting up a simple online store, but more complicated cases (like the one in your example) absolutely need a proper handling of decimal values.
I entirely agree that numeric is a good solution for this case.
But to try to answer your question nevertheless: I'd say there is potential for storage density and math performance issues. You really need to have a number of numerics for each row, and a lot of rows to make storage density matter. If you do a lot of aggregates over numerics you definitely can notice the CPU overhead of them over say a plain float. But most of the time that doesn't matter.
(I don't disagree with you, just answering your question)
Resource consumption isn't an issue at all. 100MB+ is less than loading an average news website in a browser and it costs almost nothing. The reason why I would have wanted to use SQLite sometimes is that it makes it easy to distribute and run the entire app on premises if needed. People are rightly concerned about losing access to web based software.
Serious question, what's the proper way to handle this case? I'm having hard time to believing it's IEEE-754 floats.
Of course, there's an issue of how to handle mul/div (i.e., rounding/truncation), but that's a separate question -- at least you'd be making decisions yourself rather than relying on how IEEE-754 floats work.
Some DBs like PG, do, as a matter of fact, support decimals natively which makes things quite a bit simpler.
> E.g., you won't find floating point fields in binary protocols for any of the major stock exchanges.
That's true of NASDAQ's OUCH, but some (many?) other protocols use decimal floating point.
NYSE's UTP Direct[0] (See section 4.15) Is not fixed point, but actually their own decimal floating point system with a 32-bit significand and single ASCII character exponent ("0" to "6", indicating negated base 10 exponent, not allowing "5").
FIX SBE uses an 8 byte significand and a 1 byte (power-of-10) exponent, if I remember correctly.
It's a bit unfortunate that (as far as I know) there aren't any major messaging formats supporting IEEE 754-2008 decimal64 values.
[0]https://www.nyse.com/publicdocs/nyse/markets/nyse/NYSEUTPDir...
"up to 131072 digits before the decimal point; up to 16383 digits after the decimal point"
Non jokingly: I think precision wise that's really plenty enough for pretty much any halfway realistic usecase (no, don't do public key crypto using SQL datatypes). There's some issues with the numeric / decimal type, but lack of potential precision isn't one of them.
From the docs for [`BigDecimal`](http://ruby-doc.org/stdlib-1.9.3/libdoc/bigdecimal/rdoc/BigD...):
> Ruby provides built-in support for arbitrary precision integer arithmetic. For example: > > 4213 -> 1265437718438866624512 > > BigDecimal provides similar support for very large or very accurate floating point numbers.
My takeaway from the above is that, if arbitrary precision is needed, you should still just use integers. Tho, in practice, it may not be significant.
> Ruby provides built-in support for arbitrary precision integer arithmetic.
[I'm also assuming that it's really arbitrary precision and not just 'really big but limited' precision.]
Tracking transactional values for large stock and currency trades would be an example. That said, could use integers to represent arbitrary precision.
Store your as a single integer then write a small layer that converts that integer into decimal (that's all a language or DB does anyway). Alternatively, use two entries in the database for each side of the decimal if you want even more precision or store it as a string if you want arbitrary precision.
Well, that's my point. Postgres does it for me. SQLite doesn't. I'm lazy.
Also, SQLite's largest integer type is 64 bits. That's awfully tight for some use cases.
Also, seems like the obvious thing to do to store money in an integer would be to store it in pennies. Or if the numbers get too big, do what corporate financial reports do and use thousands.
If you implement multiplication and division with integer arithmetic, you need the additional decimals to keep the error small. See https://en.wikipedia.org/wiki/Salami_slicing
I recall something about Oracle sending people down there to help with the bank or perhaps government databases deal with those numbers. I can't find confirmation of that, but https://en.wikipedia.org/wiki/Hyperinflation_in_Zimbabwe says "Computers could not handle the amount of zeros such that other forms of money had to be used to act as normal money (bearer’s cheques)."
But if you sacrifice 7 places you can no longer represent the market cap of Toyota Motor at the Tokyo stock exchange (24 trillion Yen) or the US GDP (~19 trillion USD).
So if you don't have a data type that can represent all of these quantities as well as the results of any calculations up to the required precision then your code will become a lot more complex and error prone.