How do I move a column (with contents) to another

问题:

I need to move some columns from one existing table to another. How do I do it using a rails migration?

class AddPropertyToUser < ActiveRecord::Migration
  def self.up
    add_column :users, :someprop, :string
    remove_column :profiles, :someprop
  end

  def self.down
    add_column :profiles, :someprop, :string
    remove_column :users, :someprop
  end
end

The above just creates the new columns, but values are left empty...

I want to avoid logging in to the database to manually update the tables.

If there is a way to move column values programmatically, what are the performance characteristics? Would it do row-by-row, or is there a way to update in bulk?

回答1:

I ended up using this migration (tested, it works, and rolls back successfully):

class AddPropertyToUser < ActiveRecord::Migration
  def self.up
    add_column :users, :someprop, :string
    execute "UPDATE users u, profiles p SET u.someprop = p.someprop WHERE u.id = p.user_id"
    remove_column :profiles, :someprop
  end

  def self.down
    add_column :profiles, :someprop, :string
    execute "UPDATE profiles p, users u SET p.someprop = u.someprop WHERE p.user_id = u.id"
    remove_column :users, :someprop
  end
end

I like it because it avoids the row-by-row updates on a large database.

回答2:

I would do this as three migrations, or a three part migration. The first part is adding the column, the second part is copying data over, and the third part is dropping the column.

It sounds like the middle step is what you're asking about, you can do this in ruby by looping over all users and setting the property, like this:

Users.each do |user|
   user.someprop = user.profile.some_prop
   user.save
end 

I don't love this way of doing it, because it is seriously slow. I would suggest executing raw sql like this:

execute "UPDATE users u, profiles p SET u.someprop=p.someprop WHERE u.id=p.user_id"

These both assume something about your profile/user association, which you can adjust if I assumed wrong.

回答3:

You can avoid the hard coded, database specific sql statements with update_all and/or find_each

回答4:

The syntax does not work for later versions of Postgres. For an updated answer of @Eero's for Postges 9.4.5 do the following:

class AddPropertyToUser < ActiveRecord::Migration
  def self.up
    add_column :users, :someprop, :string
    execute "UPDATE users u SET someprop = (SELECT p.someprop FROM profiles p WHERE u.id = p.user_id);"
    remove_column :profiles, :someprop
  end

  def self.down
    add_column :profiles, :someprop, :string
    execute "UPDATE profiles p SET someprop = (SELECT u.someprop FROM users u WHERE p.user_id = u.id);"
    remove_column :users, :someprop
  end
end

回答5:

The following UPDATE syntax works for recent Postgres versions and avoids a subquery:

class MoveSomePropertyToUser < ActiveRecord::Migration
  def self.up
    add_column :users, :some_property, :string
    execute "UPDATE users u SET some_property = p.some_property FROM profiles p WHERE u.id = p.user_id;"
    remove_column :profiles, :some_property
  end

  def self.down
    add_column :profiles, :some_property, :string
    execute "UPDATE profiles p SET some_property = u.some_property FROM users u WHERE p.user_id = u.id;"
    remove_column :users, :some_property
  end
end

回答6:

This is what I did in my project:-

class MoveColumnDataToUsersTable < ActiveRecord::Migration[5.1]
  def up
    add_column :users, :someprop, :string
    User.find_each do |u|
        Profile.create!(user_id: u.id, someprop: someprop)
    end
    remove_column :profiles, :someprop
  end

  def down
    add_column :profiles, :someprop, :someprop_data_type
    Profile.find_each do |p|
      User.find_by(id: p.user_id).update_columns(someprop: p.someprop)   
    end
    Profile.destroy_all
  end
end

回答7:

For me (postgreSQL 9.1) the RAW SQL didn't worked. I've changed it:

" UPDATE users u
  SET someprop = (SELECT p.someprop
                  FROM profiles p
                  WHERE u.id = p.user_id );"