Madproject

Always know where your towel is.

Gotcha: MySQL decimal type vs PostgreSQL decimal type

I encountered this issue in a Rails app where I had my local development DB set as PostgreSQL and my staging set as MySQL. A big no-no, I should have known better.

On my staging environment, when I was saving a number such as 99.99 into the decimal field the result was 99. I was losing the fractional part!

Essentially, if you create a field of type decimal without specifying a scale (the amount of allowed digits on the right of the decimal point), you’ll get different results depending on your database.

MySQL will just assume a scale of 0 and therefore ditch everything after the decimal.
PostgreSQL will just let you have up to its max scale (16383 digits after the decimal)

This obviously makes a massive difference when dealing with money!

Leave a Reply

Your email address will not be published. Required fields are marked *