How do I move a column (with contents) to another table in a Rails migration?

Question

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?

See if [this helps](http://stackoverflow.com/questions/2270702/is-there-a-ruby-database-migration-gem-that-helps-you-move-content-from-an-old-s) — Zabba, May 26 '11 at 08:28

score 41 · Accepted Answer · answered May 26 '11 at 09:04

41

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.

answered May 26 '11 at 09:04

Eero

4,704
4
37
40

2

This doesn't work with (PostgreSQL) 9.4.5. There's a syntax error at the first comma. See below for an updated version – pthamm Jan 26 '16 at 16:35
1

Note that behavior will vary depending on the relationship between the tables. For example, if users has_many profiles, after the migration each profile that has a matching id will get :someprop set. And if there are some users that don't have profiles, the :someprop data will be lost. In some use cases this will be fine, it's just something to be aware of. – Will Mar 20 '16 at 21:21
How do I move columns into a new created tables? For example I want to move :someprop to profiles, but profiles does not exist yet? – DenicioCode Aug 17 '16 at 15:54
1

A bit late to the party... but my 2 cents: 1) Great solution. 2) There is one pitfall to watch for here: http://pedro.herokuapp.com/past/2011/7/13/rails_migrations_with_no_downtime/. It effectively says that you might need to deploy in 2 steps when removing columns from an existing table. – stratis May 15 '20 at 12:32

score 14 · Answer 2 · answered Sep 05 '16 at 12:14

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

Note, this is very similar to the [accepted answer](https://stackoverflow.com/a/6136106/1323144), but replaces `UPDATE users u, profiles p` with only `UPDATE users u` to avoid the syntax error on later Postgres versions. — Ollie Bennett, Apr 25 '19 at 08:58

score 8 · Answer 3 · answered May 26 '11 at 08:25

8

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.

answered May 26 '11 at 08:25

andrewmitchell

1,559
13
15

Thanks, I ended up using the "execute" call. But what is the benefit of breaking the migration up into three migrations? If the statement in the execute call fails, the whole migration stops, so is there really any risk or disadvantage of having it all in one? – Eero May 26 '11 at 09:06
2

I've just found out that there *is* a really good reason for breaking it into three separate migrations. MySQL cannot do migrations in a transaction, so if it crashes halfway through, you're left with a mess to clean up manually. Postgres users should be fine though. – Eero Nov 26 '12 at 19:56
2

"On databases that support transactions with statements that change the schema (such as PostgreSQL or SQLite3), migrations are wrapped in a transaction. If the database does not support this (for example MySQL) then when a migration fails the parts of it that succeeded will not be rolled back. You will have to rollback the changes that were made by hand." ( Feb 15, 2013 --- http://guides.rubyonrails.org/migrations.html ) – richardaday Feb 16 '13 at 01:55

score 5 · Answer 4 · answered Jan 26 '16 at 16:34

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

You can also achieve this without a subquery; see [my answer](http://stackoverflow.com/a/39330402/1323144). — Ollie Bennett, Sep 05 '16 at 12:17

score 2 · Answer 5 · answered Nov 13 '12 at 16:10

2

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

answered Nov 13 '12 at 16:10

Matt Scilipoti

1,091
2
11
15

score 1 · Answer 6 · answered May 31 '18 at 17:26

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

Jeff · Answer 7 · 2016-11-08T16:17:00.957

-1

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 );"

edited Nov 08 '16 at 16:17

answered Nov 20 '14 at 15:09

Jeff

69
7

seems to hang for me even when I try to do it for a few records – pthamm Jan 26 '16 at 00:31
you need to add a semi-colon at the end of that statement – pthamm Jan 26 '16 at 00:43

How do I move a column (with contents) to another table in a Rails migration?

7 Answers7

Linked