How to write SQL in a migration in Rails

Question

I have the following SQL which I need to do

CREATE TABLE cars_users2 AS SELECT DISTINCT * FROM cars_users;  DROP TABLE cars_users;  ALTER TABLE cars_users2 RENAME TO cars_users; 

since I cannot use heroku dataclips to drop a table, I cannot use dataclips.

So I guess I need to do this in a migration.

How do I write this sql as a migration?

Answer 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 

Answer 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.