On Jun 26, 2019, at 2:22 PM, Warren Young <war...@etr-usa.com> wrote:
>
> 3. …types…table…more comprehensive, so that whatever weird data type you
> search for, you either get a simple mapping to one of SQLite’s few base data
> types or to a recipe showing how to construct a suitable alternative.
…or a pointer to a SQLite extension to provide the feature.
For example, the current Affinity Name Examples table I linked in the prior
post says that DECIMAL(10,5) maps to NUMERIC, but you have to read the rest of
that page carefully to realize that it’s giving you a limit case, beyond which
SQLite will lose precision. It’s not clear just from that type table that
DECIMAL(11,6) gives you no more precision than DECIMAL(10,5) in SQLite:
sqlite> create table x (a DECIMAL(11,6));
sqlite> insert into x values(123456789.123456789);
sqlite> select * from x;
123456789.123457
Note that it’s a silent clamp on the value. There is no warning that you’ve
done something silly, either on CREATE TABLE or on INSERT.
Ultimately, this is because SQLite uses platform IEEE 754 doubles and platform
ints for storage, whereas big-boy DBMSes tend to use arbitrary-precision math
libraries for DECIMAL.
The place for that warning isn’t in SQLite itself, but instead in its docs,
where it should make the limit more clear, then point those who need to get
beyond that limit to this:
https://chiselapp.com/user/lifepillar/repository/sqlite3decimal/index
Granted that that’s a third-party, not-for-production, function-only extension,
not a true SQLite data type, but at least pointing it out will clue people into
the fact that SQLite’s DECIMAL isn’t really doing anything for you other that
you can’t already do with REAL or INT.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
