A common internal format is used to represent all numbers in SQLite4. This format is able to losslessly represent all integer values from the maximum unsigned 64-bit integer down to the smallest (most negative) signed 64-bit integer. The format is also able to represent floating point numbers spanning a greater range and precision than IEEE 754 binary64.

SQLite4 makes no distinction between integer and floating point numbers. It does, however, distinguish between exact and approximate numbers. In C/C++, integers are exact and floating point numbers are approximate. But this is not necessarily true of SQLite4. Floating point numbers can be exact in SQLite4. Integers that can be represented in 64-bits are always exact in SQLite4 but larger integers might be approximate.

The SQLite4 numeric format is for internal use. Numbers can be translated between integers or doubles for input and output. The on-disk storage space required for SQLite4 numbers varies from between 1 and 10 bytes, depending on the magnitude and the number of significant digits in the value.

The SQLite4 numeric format is a decimal floating point format. The significant is a 18-digit decimal number, represented internally as an unsigned 64-bit integer. A base-10 exponent with 3-digits of precision is used, represented internally as an unsigned 11-bit integer. Both the significant and the exponent can be signed, of course. This gives a maximum value of:

999,999,999,999,999,999 e 999

Infinities are represented by any non-zero number with an exponent of 1000 or more. A zero significant with an exponent of 1000 or more is a NaN.

The IEEE 754 decimal representation was considered as the format for SQLite4 but was rejected as having insufficient precision and being overly complex.

Because a base-10 exponent is used, there are no round-off error problems when converting the fractional part between decimal and the internal representation. Furthermore, this format can exactly represent any signed 64-bit integer, which IEEE 754 binary64 cannot do. Any decimal floating point number with an 3-digit exponent and 18 or fewer significant digits can be represented exactly in this representation.

The on-disk storage format for numbers uses between 1 and 13 bytes of space, depending on the magnitude of the number. Numbers with fewer significant digits require less storage than numbers with more significant digits. There are two encoding format, one for keys (that must sort in lexicographical order) and another for data.

A proposed interface to decimal math routines is as follows:

   typedef struct sqlite4_num sqlite4_num;
   struct sqlite4_num {
     unsigned char sign;     /* Sign of the overall value */
     unsigned char approx;   /* True if the value is approximate */
     signed short e;         /* The exponent. */
     sqlite4_uint64 m;       /* The significant */
   };
   sqlite4_num sqlite4_num_add(sqlite4_num, sqlite4_num);
   sqlite4_num sqlite4_num_sub(sqlite4_num, sqlite4_num);
   sqlite4_num sqlite4_num_mul(sqlite4_num, sqlite4_num);
   sqlite4_num sqlite4_num_div(sqlite4_num, sqlite4_num);
   int sqlite4_num_isinf(sqlite4_num);
   int sqlite4_num_isnan(sqlite4_num);
   sqlite4_num sqlite4_num_round(sqltie4_num, int iDigit);
   int sqlite4_num_compare(sqlite4_num, sqlite4_num);
   
   sqlite4_num sqlite4_num_from_string(const char*, int n, int *pUsed);
   sqlite4_num sqlite4_num_from_int64(sqlite4_int64);
   sqlite4_num sqlite4_num_from_double(double);
   
   int sqlite4_num_to_int32(sqlite4_num, int*);
   int sqlite4_num_to_int64(sqlite4_num, sqlite4_int64*);
   double sqlite4_num_to_double(sqlite4_num);

Use the sqlite4_*printf() routines to render sqlite4_num objects as decimal.