Read more

How DECIMAL columns deal with numbers exceeding their precision or scale

Henning Koch
April 29, 2011Software engineer at makandra GmbH

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.

Illustration online protection

Rails Long Term Support

Rails LTS provides security patches for old versions of Ruby on Rails (2.3, 3.2, 4.2 and 5.2)

  • Prevents you from data breaches and liability risks
  • Upgrade at your own pace
  • Works with modern Rubies
Read more Show archive.org snapshot

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
Henning Koch
April 29, 2011Software engineer at makandra GmbH
Posted by Henning Koch to makandra dev (2011-04-29 17:05)