When should one use data type REAL versus NUMERIC in sqlite?

Question

In sqlite, REAL and NUMERIC data types seem similar. Since NUMERIC supports Decimal(10,5), when should one use REAL instead?

https://www.tutorialspoint.com/sqlite/sqlite_data_types.htm

I am using sqlite3.

Answer 1

SQLite, unlike most other RDBMS, uses a dynamic type system. This means that any of SQLite's five storage classes can be present in any column, despite that the column belongs to one type. But obviously, it is not best practice to mix types within a single column. For example, mixing numeric and character data in the same column is bad practice, as it would be in any database. The five actual storage classes in SQLite are:

• NULL
• INTEGER
• REAL
• TEXT
• BLOB

The REAL storage class is used for numeric data with a decimal component. Floats would fit into this category. Other numbers without a decimal component would be assigned to the INTEGER storage class.

SQLite introduced a concept known as type "affinities." They were introduced in order to maximize compatibility between SQLite and other databases. From the documentation, we can see that the NUMERIC affinity is associated with the following types:

• NUMERIC
• DECIMAL(10,5)
• BOOLEAN
• DATE
• DATETIME

So, if you are intending to store exact DECIMAL(10,5) data, then a NUMERIC affinity would make sense. On the other hand, the affinity REAL is associated with floating point (not exact) decimal data. Again, you may store either type in either column, but from a compatibility point of view, you may follow the affinity rules.