Posted over 8 years ago. Visible to the public. Repeats.

How DECIMAL columns deal with numbers exceeding their precision or scale

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 (no strict mode)

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:

Copy
ERROR: numeric field overflow

Does your version of Ruby on Rails still receive security updates?
Rails LTS provides security patches for old versions of Ruby on Rails (3.2 and 2.3).

Owner of this card:

Avatar
Henning Koch
Last edit:
over 1 year ago
by Henning Koch
About this deck:
We are makandra and do test-driven, agile Ruby on Rails software development.
License for source code
Posted by Henning Koch to makandra dev
This website uses cookies to improve usability and analyze traffic.
Accept or learn more