Adding a decimal type to turso?

[pdenapo]

I see that you are working in improving the turso type system. I believe that a key feature that sqlite does not have is a decimal type.

This is critical for any application working with financial data, where the errors introduced by floating point operations are not acceptable, see for instance

https://www.moderntreasury.com/journal/floats-dont-work-for-storing-cents

[This means that using floats is bad for every app handling money data!]

Many other databases like postgresql, mariadb and duckdb support this. See

1. https://www.postgresql.org/docs/current/datatype-numeric.html
2. https://mariadb.com/docs/server/reference/data-types/numeric-data-types/decimal
3. https://duckdb.org/docs/current/sql/data_types/numeric

But sqlite does not! Your re-witting of sqlite from scratch gives you the opportunity to fix this!

Of course, internally you would store the data scaled as integers. But, if there were a way of telling that "this is a decimal number" in the database schema, this might help other applications for example when showing the tables in a human readable form. Or might simplify client code. avoiding conversions from scaled integers to the decimal type of the language [for instance Python has a Decimal type for this, using mpdecimal].

https://docs.python.org/3/library/decimal.html

I know that there is proposal in #5515 for adding the sqlite decimal extension, but this is not a good solution (though probably easier to implement) as storing decimal numbers as text is highly inefficient.

[cateagle]

I think that this is actually already kind of supported, but a little hidden. If you enable the the custom types and use strict tables, then you have a lot more built in types available:
https://docs.turso.tech/sql-reference/data-types#custom-types

A decimal type is not among them, but you can easily define one yourself. I think I have seen a showcase of a decimal type before in some blog post, but I did not find it again.

I think adding a built in decimal extension or adding a decimal type to the built in custom types would be a good idea. Until then, I would just use a custom defined type.

Hope this helps you.

[cateagle]

Looks, like I can't read the documentation page that I gave you. There is a numeric built in type in turso once you activate the custom types. That type has a fixed point precision and is stored as blob.

It is defined like this in core/schema.rs:

CREATE TYPE numeric(value any, precision integer, scale integer) 
BASE blob 
ENCODE numeric_encode(value, precision, scale) 
DECODE numeric_decode(value) 
OPERATOR '+' numeric_add 
OPERATOR '-' numeric_sub 
OPERATOR '*' numeric_mul 
OPERATOR '/' numeric_div 
OPERATOR '<' numeric_lt 
OPERATOR '=' numeric_eq

As far as I understand it from core/numeric/decimal.rs, the numeric values get stored like this:

Format (all multi-byte values little-endian):
[version: 1 byte (0x01)]
[flags:   1 byte (bit 0 = sign, 0=positive/zero, 1=negative)]
[scale:   8 bytes, i64 LE]
[num_limbs: 4 bytes, u32 LE]
[limb0: 4 bytes u32 LE] [limb1: 4 bytes u32 LE] ... [limbN: 4 bytes u32 LE]

If you truly want to minimize storage size for your use case, then this should be possible with a custom type, but I guess that it is not really necessary. Numeric values are rarely responsible for storage overhead.

Personally, I would just use the built in numeric type storage to reduce the need for documentation and participate in any performance optimizations that might happen for numeric operations in the future.