8 ms·

> numeric column Worth mentioning that numeric in Sqlite is still just a float.[0] A table of two rows where column a is 0.1 and 0.2, respectively, sum(a) wil

by Svip 3y ago

> numeric column Worth mentioning that numeric in Sqlite is still just a float.[0] A table of two rows where column a is 0.1 and 0.2, respectively, sum(a) will not yield 0.3. [0] https://www.sqlite.org/datatype3.html https://www.sqlite.org/datatype3.html

LispSporks22 3y ago
Huh what do folks use for money in Sqlite?
- Yiin 3y ago
  same thing you should use in any data store for money, integers
  - kruador 3y ago
    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.
    - mercxry 3y ago
      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_money https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use... [2] https://www.youtube.com/watch?v=PZRI1IfStY0 https://www.youtube.com/watch?v=PZRI1IfStY0
  - nly 3y ago
    Using a fixed point representation doesn't solve your SUM() problem. If you add 0.1 to 1000000 with an integer representation scaled by 10^6 then you are still boned. Binary floats may actually produce a better result here. What you really want is a decimal floating point type with a suitable amount of significant digit precision.
    - cdelsolar 3y ago
      int64?
michaelcampbell 3y ago
You've checked this? Perhaps you're correct in a general sense, but in this specific case, neither "number" nor "numeric" datatypes show this issue. > sqlite3 --interactive ./floats.db SQLite version 3.37.2 2022-01-06 13:25:41 Enter ".help" for usage hints. sqlite> create table floats(colA number); sqlite> insert into floats(colA) values (0.1); sqlite> insert into floats(colA) values (0.2); sqlite> select sum(colA) from floats; 0.3 sqlite> create table floats2(colA numeric); sqlite> insert into floats2(colA) values (0.1); sqlite> insert into floats2(colA) values (0.2); sqlite> select sum(colA) from floats2; 0.3 sqlite>

by Svip 3y ago

LispSporks22 3y ago

Huh what do folks use for money in Sqlite?

Yiin 3y ago
same thing you should use in any data store for money, integers
- kruador 3y ago
  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.
  - mercxry 3y ago
    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_money https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use... [2] https://www.youtube.com/watch?v=PZRI1IfStY0 https://www.youtube.com/watch?v=PZRI1IfStY0
- nly 3y ago
  Using a fixed point representation doesn't solve your SUM() problem. If you add 0.1 to 1000000 with an integer representation scaled by 10^6 then you are still boned. Binary floats may actually produce a better result here. What you really want is a decimal floating point type with a suitable amount of significant digit precision.
  - cdelsolar 3y ago
    int64?

michaelcampbell 3y ago

You've checked this? Perhaps you're correct in a general sense, but in this specific case, neither "number" nor "numeric" datatypes show this issue. > sqlite3 --interactive ./floats.db SQLite version 3.37.2 2022-01-06 13:25:41 Enter ".help" for usage hints. sqlite> create table floats(colA number); sqlite> insert into floats(colA) values (0.1); sqlite> insert into floats(colA) values (0.2); sqlite> select sum(colA) from floats; 0.3 sqlite> create table floats2(colA numeric); sqlite> insert into floats2(colA) values (0.1); sqlite> insert into floats2(colA) values (0.2); sqlite> select sum(colA) from floats2; 0.3 sqlite>