Posted about 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 will throw an error:

ERROR: numeric field overflow
Growing Rails Applications in Practice
Check out our new e-book:
Learn to structure large Ruby on Rails codebases with the tools you already know and love.

Owner of this card:

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