How to write SQL in a migration in Rails

Solution 1:

For your up migration:

execute "CREATE TABLE cars_users2 AS SELECT DISTINCT * FROM cars_users;" 
drop_table :car_users  
rename_table :car_users2, :car_users  

and for down:

raise ActiveRecord::IrreversibleMigration

Full migration:

class TheMigration < ActiveRecord::Migration
    def up
        execute "CREATE TABLE cars_users2 AS SELECT DISTINCT * from cars_users;" 
        drop_table :car_users  
        rename_table :car_users2, :car_users  
    end

    def down
        raise ActiveRecord::IrreversibleMigration
    end
end

Solution 2:

You could try to use the execute method.

Something like this (it's untested, some kind of brainchild)

class UpdateCarUserTable < ActiveRecord::Migration
  def up
    execute "CREATE TABLE cars_users2 AS SELECT DISTINCT * FROM cars_users"
    execute "DROP TABLE cars_users"
    execute "ALTER TABLE cars_users2 RENAME TO cars_users"
  end

As there is no equivalent down method, an ActiveRecord::IrreversibleMigration should be raised when trying to migrate down.

Solution 3:

I prefer here doc:

execute <<-SQL
  CREATE TABLE cars_users2 AS SELECT DISTINCT * FROM cars_users;
  DROP TABLE cars_users;
  ALTER TABLE cars_users2 RENAME TO cars_users;
SQL

notice: This only works for PostgreSQL, if you are using MySQL you should set CLIENT_MULTI_STATEMENTS for the adapter.