Why you should probably be using SQLite

Re:Why you should probably be using SQLite by kruador on Friday October 27, 2023 @12:01PM (#38040259)

In Microsoft SQL Server you should use MONEY or DECIMAL. In Oracle you should use NUMBER. In Postgres you should use MONEY or NUMERIC. In MySQL you should use NUMERIC or DECIMAL. In IBM DB2 you should use DECIMAL.

These are all backed by integer data storage and arithmetic, but the database handles scaling the values for you, to whatever number of decimal places you have configured. SQL Server and Postgres MONEY type will additionally format values with a currency symbol, when converted to a character string.

In SQLite you're out of luck - if you want accounting values you'll have to store them as integers and scale the values yourself.

Source: I work on a mobile app with offline storage of pricing and weighed quantities, using SQLite in the app, and SQL Server on the back end.

[ Parent | on HN ]

Re:Why you should probably be using SQLite by mercxry on Saturday October 28, 2023 @05:29AM (#38048417)

You should still not use MONEY in Postgres, even their own website says so [1].

I still wouldn't use floats/numerics/decimals to store currency either in any db generally, as said by others you're going to end up with inaccurate numbers [2].

Therefore using integers is in fact very good for this use-case, especially if you are in accounting or book-keeping!

Source: I work for a Fintech company that processes millions of payments.

[1] https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use... [2] https://www.youtube.com/watch?v=PZRI1IfStY0

[ Parent | on HN ]