Handling Decimal Precision in Rails
Ruby on Rails allows us to specify how precise we want decimals to be by defining precision and scale in database migrations. Rails also provides a way of adding frontend validation to forms that accept decimal values. I’ll be using an expense
model that lets a user track expense amounts as an example. I’ll also point out differences between SQLite and PostgreSQL in regards to saving decimals beyond constraints.
Migration
The migration for an expense model that has a decimal field looks like this:


Notice that we’re specifying precision
and scale
in the decimal column:
 Precision is the total number of digits in the number, both before and after the decimal point.
 Scale is the number of digits after the decimal
So this field will take a decimal value up to 999.99.
Model
For the expense model, we want to add validations so that the amount is positive and is less than 1000. This is based on the precision and scale we defined in the migration. We can also add validation to take into account decimal places by using a regular expression that allows values up to 999.99.


Note for the regular expression: \A
is the same as ^
, while \z
is the same as $
.
Controller
For the expense model, we have a standard controller. We don’t need to do anything here in regards to decimal precision. I’m showing this for completeness of the example.


ERB Form
In the form, we need to use use step
to add frontend validation and to be able to accept decimal values in the field.


In this form, step: 0.01
is the same as specifying a scale of 2 in the database. Decimal values will only be accepted if they have two decimal places and are in increments of 0.01. (If we had specified step: 0.05
, then values accepted would have to be in increments of 0.05, such as 1, 1.05, and 1.10). Without step
, the form would only take whole numbers without decimals.
Thanks to our model validation, a user won’t be able to submit values like 555.555
even if they were clever enough to skip frontend validation.
SQLite vs PostgreSQL Validation
Lets say we didn’t have any frontend or model validations. How would the database handle decimal inputs that exceed both precision and scale? We can do some experiments in the Rails console.
Exceeding Scale Constraints
First, we’ll try the value 555.555
, which exceeds the scale of 2.
Rails console with SQLite:


After saving a new expense with the amount 555.555
, the resulting amount is 0.55556e3
, or 555.56
. The database rounded our input since it was set to a scale of 2 in the migration.
Rails console with PostgreSQL:


PostgreSQL behaves the same way and rounds to two decimal places if the scale is exceeded.
Exceeding Precision Constraints
Next we’ll try the value 123456.01
, which exceeds the precision of 5.
Rails console with SQLite:


Interestingly, SQLite saves the value 123456.01
incorrectly as 1234560.00
with no errors whatsoever. This is undesired behavior because it defeats the point of defining a precision in the first place.
Rails console with PostgreSQL:


PostgreSQL rejects the value and shows us an error telling us that the number does not fit in with the precision and scale constraints. This is preferable to storing an incorrect value.
While Rails offers several ways of validating decimals, it’s still important to choose the correct database to handle decimals.