How might you clone a database table via Rails migration?

Question

I want a migration to create a clone of an existing table by just suffixing the name, including all the indexes from the original table.

So there's a "snapshots" table and I want to create "snapshots_temp" as an exact copy of the table (not the data, just the table schema, but including the indexes).

I could just copy and paste the block out of the schema.rb file and manually rename it.

But I'm not sure by the time this migration is applied if the definition from schema.rb will still be accurate. Another developer might have changed the table and I don't want to have to update my migration script.

So how might I get the schema of the table at runtime? Essentially, how does 'rake schema:dump' reverse engineer the table so I can do the same in my migration? (but changing the table name).

score 33 · Accepted Answer · answered Oct 12 '09 at 03:47

33

Try doing it with pure SQL. This will do what you want:

CREATE TABLE new_tbl LIKE orig_tbl;

answered Oct 12 '09 at 03:47

zenazn

14,295
2
36
26

Good call. "Use LIKE to create an empty table based on the definition of another table, including any column attributes and indexes defined in the original table" http://dev.mysql.com/doc/refman/5.1/en/create-table.html – Teflon Ted Oct 12 '09 at 16:40
Works with MySQL (and possibly most databases) but if you use Sqlite, it won't work. I ran into this problem in development environment. Production is fine (it's MySQL). – MiniQuark Sep 17 '10 at 14:38
1

Awesome thanks. Note that your copied table will get the indexes, but not any foreign keys. You'll have to recreate those separately. – Rich Sutton Oct 10 '12 at 17:59
12

For those on PostgreSQL: `CREATE TABLE new_tbl (LIKE orig_tbl INCLUDING DEFAULTS INCLUDING INDEXES);` or `INCLUDING ALL` [to clone everything](http://www.postgresql.org/docs/9.3/static/sql-createtable.html) – Rescribet Sep 19 '14 at 20:25
Here's a useful answer in another post for the two commands to clone and copy all data into another table: http://stackoverflow.com/a/3280042/513739 – Excalibur Jun 03 '15 at 19:20
could you explain how to use that in the migration? – wuliwong Nov 07 '18 at 15:47
Will this properly synchronize with `schema.rb`? EDIT - Answering my own question: yes -- https://stackoverflow.com/questions/3815447/does-rake-dbschemadump-recreate-schema-rb-from-migrations-or-the-database-itse – David Bodow Jul 23 '19 at 20:32

score 7 · Answer 2 · answered May 10 '14 at 17:31

7

In Rails 4 & PostgreSQL, create a new migration and insert:

ActiveRecord::Base.connection.execute("CREATE TABLE clone_table_name AS SELECT * FROM source_table_name;")

This will create the clone with the exact structure of the original table, and populate the new table with old values.

More info: http://www.postgresql.org/docs/9.0/static/sql-createtableas.html

answered May 10 '14 at 17:31

Hassen

6,966
13
45
65

4

Careful with this as it will copy the ID column too and it won't set it as primary in the new table. – Guillermo Siliceo Trueba Apr 14 '15 at 03:14
And it won't recreated indices. – Thomas Andrews Mar 22 '18 at 17:00

score 5 · Answer 3 · edited Sep 09 '14 at 17:14

This will do. It's not perfect, because it won't copy table options or indices. If you do have any table options set, you will have to add them to this migration manually.

To copy indices you'll have to formulate a SQL query to select them, and then process them into new add_index directives. That's a little beyond my knowledge. But this works for copying the structure.

class CopyTableSchema < ActiveRecord::Migration
  def self.up
    create_table :new_models do |t|
      Model.columns.each do |column|
        next if column.name == "id"   # already created by create_table
        t.send(column.type.to_sym, column.name.to_sym,  :null => column.null, 
          :limit => column.limit, :default => column.default, :scale => column.scale,
          :precision => column.precision)
      end
    end

    # copy data 

    Model.all.each do |m|
      NewModel.create m.attributes
    end
  end

  def self.down
    drop_table :new_models
  end
end

> it won't copy table options or indices Thanks but indexes is a requirement. — Teflon Ted, Oct 12 '09 at 16:38

score 2 · Answer 4 · answered May 05 '16 at 21:26

2

Copy the tables entry from your projects db/schema.rb straight into your migration. Just change the table name and your good to go.

answered May 05 '16 at 21:26

Weston Ganger

6,324
4
41
39

score 0 · Answer 5 · answered Oct 11 '09 at 17:40

0

It looks like this logic is wrapped up in ActiveRecord::SchemaDumper but it only exposes an all-in-one "dump" method and you can't dump just a specific table (the "table" method is private).

answered Oct 11 '09 at 17:40

Teflon Ted

8,696
19
64
78

How might you clone a database table via Rails migration?

5 Answers5