How to use SQLite decimal precision notation

Question

Again I find myself frustrated by the awful SQLite documentation.

http://www.sqlite.org/datatype3.html gives an example of defining a decimal field as decimal(10,5) but it doesn't explain what 10 is and what 5 is.

I am sure that 10 is the total number of digits stored, but I don't know what 5 means. Is it the number of digits before the decimal place or after the decimal place?

Answer 1

That page says:

In SQLite, the datatype of a value is associated with the value itself, not with its container.

Which means that the declared data type has no effect on the values that can be stored in the column.

The example explains that a DECIMAL(10, 5) column has a numeric affinity, i.e., values stored in that column are preferred to be numbers:

> CREATE TABLE MyTable(X DECIMAL(10, 5)); > INSERT INTO MyTable VALUES (1), ('2'), (1.23456789), ('hello'), (x'42'); > SELECT X, typeof(X) FROM MyTable; 1           integer 2           integer 1.23456789  real hello       text B           blob 

Otherwise, the type is ignored; you could use just as well use DECIMAL(-123, 999999), or FLUFFY BUNNIES.

Answer 2

According to http://www.sqlite.org/datatype3.html

[decimal is one of several] common datatype names from more traditional SQL implementations are converted into [SQLite] affinities

(An affinity is about as close to the idea of a data type that SQLite has. Read http://www.sqlite.org/datatype3.html for more about this.)

By this logic, it seems safe to use the documentation from MySQL to explain the SQLite decimal precision notation.

According to http://www.sqlite.org/datatype3.html

Standard SQL requires that DECIMAL(5,2) be able to store any value with five digits and two decimals, so values that can be stored in the salary column range from -999.99 to 999.99.

So decimal(10,5) indicates that the field should be used to store a value up to ten digits in length, with up to five digits before the decimal point and up to five digits after the decimal point.

Of course, any value entered will be stored, even if it doesn't keep to these rules, which means that the field definition is basically documentation.