Blog

Understanding of Decimal datatype in MySql

Decimal datatype is widely used datatype while working with finance related projects.
I used decimal datatype for one of my rails project and get trouble to insert value in field.

The Error was :  Out of range value for column ‘db_field’ at row 1

I was getting this error because I was trying to insert value which is out of range. 
Here is what I had in my migration file :

t.decimal :rate, :precision => 3, :scale => 3, :default => 0.0

Above is equivalent to Decimal(3,3). Decimal(3,3) means I can store value in range of -0.999 to 0.999 as a value in rate column. Observe scale.

When I define scale as 3, there is always 3 digits after decimal point.

In short, Decimal(P,S) where P(Precision) is maximum digits allowed in value and S(Scale) is digits allowed after decimal point.

How to decide precision and scale for your column value ?

It is very simple to decide it. Use this formula (Precision - Scale = Maximum digits before decimal point)
For Example : If you want 2 digits before decimal point and 3 digits after decimal point (Scale) then total is 5 digits. So precision will be 5 and scale will be 3 i.e. Decimal(5,3)

More explanation regarding Decimal Datatype is given in MySQL documentation.

Hope that helps!!