When storing floating-point numbers such as prices or totals in an SQL database, always use a DECIMAL column. Never use FLOAT or kittens will die.
DECIMAL columns are parametrized with a precision and a scale. These parameters describe which numbers can be stored in that column. E.g. a decimal with a precision of 5 and a scale of 2 can store numbers from -999.99 to 999.99, but not 1000 or 1.234.
This card explains what various databases do when you try to store a number in a DECIMAL field, and that number exceeds that column's precision or scale
MySQL (strict mode)
The database connection will throw an error.
MySQL (no strict mode)
Warning
Don't use your production application in
no strict mode. DON'T!
MySQL will store the closest representable value. Here are some examples for a DECIMAL with a precision of 5 and a scale of 2:
| Assigned value | Stored value |
|---|---|
100 |
100 |
1000 |
999.99 |
-1000 |
-999.99 |
1.34 |
1.34 |
1.345 |
1.35 |
-1.34 |
-1.34 |
-1.345 |
-1.35 |
These changes are performed inside MySQL. Note that when you loaded an ActiveRecord model with a DECIMAL column, and you assign an exceeding number to the respective record attribute, you won't notice MySQL's changes until you reload the record. Before you reload the record, standard Ruby BigDecimal arithmetic applies to the attribute value.
PostgreSQL
PostgreSQL will throw an error:
ERROR: numeric field overflow
