Rails migration to change empty strings to null

Question

Say I have a db table called fruit like this:

id  name
1   ““
2   “"
3   ““
4   ““
5   ""
6   melon

I need to write a migration to change the empty strings to null without affecting melon in this case.

Would it be something on these lines?

def change
  update_column fruits, :name, null if :name => ""
end

Pretty basic stuff I guess but I'm kinda stuck here. What is the best approach here?

why not just do an `update_all`, `Fruit.where(name: "").update_all(name: nil)` — lusketeer, Jun 09 '16 at 11:22
anywhere, rails console, migration, you don't really need a migration, but if want to keep things consistent, add it to migration — lusketeer, Jun 09 '16 at 11:25
@lusketeer yeah consistency is the name of the game here :-), that worked a treat in a migration, throw it in an answer and I'll accept it. — Jax, Jun 09 '16 at 11:31

score 0 · Answer 1 · answered Jun 09 '16 at 11:28

0

in console development and production as well:

Fruit.all.each do |fruit|
  if fruit.name == ""
    fruit.name = nil
    fruit.save
  end
end

answered Jun 09 '16 at 11:28

Sean Magyar

score 0 · Answer 2 · edited Jun 09 '16 at 13:12

0

Use the normal command to generate a migration;

rails g migration xxxxx

And then add:

def change
  execute "UPDATE fruits SET name = NULL WHERE name = '' "
end

edited Jun 09 '16 at 13:12

davegson

answered Jun 09 '16 at 12:04

VijayGupta

in sql you use `name IS NULL` – davegson Jun 09 '16 at 12:20
i run the query with ( IS null ) in postgres , it gives error "syntax error at or near "IS" " – VijayGupta Jun 09 '16 at 13:09
ok sorry, I misread your query, it sets `null` - it does not *search* for `null` – davegson Jun 09 '16 at 13:10
I won't upvote, since the answer is poorly formatted and not a great answer, but I removed my downvote. – davegson Jun 09 '16 at 13:13

score 0 · Accepted Answer · answered Jun 09 '16 at 12:17

0

Fruit.where(name: "").update_all(name: nil)

rails console, migration, anywhere you can excute it. You don't really need a migration, but if want to keep things consistent, add it to migration

answered Jun 09 '16 at 12:17

lusketeer

3 Answers3