Does changing data type decimal to float cause data loss?

Question

I need to change a couple of fields in my database from:

:decimal, :precision => 8, :scale => 5

to:

:float

Does this migation result in data loss? The current data consists of integers between 0 and 999.

If this migration will impact these numbers already stored, how can I keep this data safe?

Setup: Ruby on Rails 3 running on Heroku (solution would need to work for both PostgreSQL and MySQL).

score 1 · Answer 1 · edited May 23 '17 at 09:59

1

It will, if you ever need to do exact comparisons or floating point arithmetics on your numbers. Open up PostgreSQL and try this:

select floor(.8::float * 10.0::float); -- 8
select floor((.1::float + .7::float) * 10.0::float); -- 7

See this related question for the reason:

edited May 23 '17 at 09:59

Community

answered May 15 '11 at 05:05

Denis de Bernardy

thanks for your answer - I'm more concerned with literally losing my numbers than having them slightly off their former value. If the field goes from that restrictive decimal to either float or a more expansive decimal (higher precision), I assume this means my data would remain safe? – sscirrus May 15 '11 at 05:08
Well, in this case yes, you're safe if you use an `ALTER TABLE` statement to modify the column's type. RoR might get the job done too, I've honestly no idea -- though if so, I'd place more trust in what I type than in a ruby-based SQL diff tool. – Denis de Bernardy May 15 '11 at 05:14

score 0 · Accepted Answer · answered May 15 '11 at 04:56

0

Integers between 0 and 999 will fit in either and the data won't be impacted. If it is just integers - why not use ints?

answered May 15 '11 at 04:56

zsalzbank

It won't always be integers :) – sscirrus May 15 '11 at 05:06
so, as long as the current data literally fits inside the new data type, it'll be fine? – sscirrus May 15 '11 at 05:07
I would think so - you can always make a backup of your `development.db` just in case. Migrate back from the change if things go wrong before restoring the backup. – zsalzbank May 15 '11 at 05:10

2 Answers2