Add value to postgres column if that model has no value for column

Question

I need to go through all of my current User models and check if there is a value for attribute new_attribute, if there is then I would like to keep it, if there is not then I would like to give it a value.

So as an example. If the User model has a value in it's school column then it would remain but if it does not have a value then it would be given the value "None".

Is this something I should do in a migration? How would I write that migration?

score 0 · Answer 1 · answered Sep 22 '14 at 19:50

You should write a migration. There are two ways to write the migration.

Execute a direct query

def up
  execute("update users set school='None' where school is null")
end

def down
  raise ActiveRecord::IrreversibleMigration
end

Use ActiveRecord

def up
  User.where("school is null").update_all(school: 'None')
end

def down
  raise ActiveRecord::IrreversibleMigration
end

score 0 · Answer 2 · edited May 23 '17 at 12:29

Migrations are usually best for database schema changes.

From the docs (emphasis mine):

Migrations are a convenient way to alter your database schema over time in a consistent and easy way. ... You can think of each migration as being a new 'version' of the database.

This does not mention data updates. Only schema changes.

They can easily wreak havoc on a data store's data. The plus is that this is a one-way fuse. This migration will never run again once it is run on the database.

However, a better idea may be to create an idempotent rake task that accomplishes the same data migration.

Let's also twist our thinking about this problem a little. Perhaps we could look for only the users who do not have school information. Then we only have to retrieve and update those users. We can ignore all of the other users.

An example rake task is (assuming you have an id field on user):

namespace :remove_nulls_from_user_schools do
  User.where(school: nil).each do |user|
    user.school = 'None'
    if user.save
      puts "Updated school for user with id: #{user.id}"
    else
      puts "Update Failed for user with id: #{user.id}"
    end
  end
end

This rake task has the added benefit of being re-runnable (however, it is not precisely idempotent). If any more users are generated with an empty school field, you can re-run this rake task and fix any new users.

Depending on how many users you are updating, you may want to use something like User.find_each to iterate through the users.

If you are updating a great many users, you could also use the sql that the previous answer provided in the rake task. That would be a very quick rake task which would accomplish the same objective:

namespace :remove_nulls_from_user_schools do
  ActiveRecord::Base.connection.execute('update users set school='None' where school is null')
end

For more information on executing raw sql, see Rails 3 execute custom sql query without a model

Add value to postgres column if that model has no value for column

2 Answers2