How do I migrate a database column from integer to bigint in rails?

Question

Based on what I've found, I'm using this right now, but it's not working...

def change
 change_column :users, :twitter_id, :integer, :limit => 8
end

Still, if someone has an answer for how to do this with a change, I will mark it as the correct answer. Doing what I did solved the problem, but isn't really an answer to the question as it won't help anyone who comes across this problem later in the game when they can't just drop a table from their database — Carl, Oct 22 '15 at 10:59

score 53 · Answer 1 · answered Oct 18 '16 at 20:51

53

I just ran into the same issue. The following worked for me:

def up 
  change_column :my_table, :my_column, :bigint
end

answered Oct 18 '16 at 20:51

malikilam

672
8
10

Does this just change the schema, or will every row in the table be updated? I.e. will it lock my table? – TheJKFever Sep 11 '18 at 17:11
1

Doing this did not lock my table. In all honestly, I didn't check to see whether every row was changed or whether it was just the schema that was changed. It worked seamlessly, so I had no need to dig into further. – malikilam Sep 13 '18 at 01:56
13

It definitely does lock the table. I'm pretty sure it's changing every row also. I added a couple dozen million records before a test migration, and I was not able to insert into the table during the migration which took hours. For anyone looking for zero downtime, I found this answer: https://stackoverflow.com/questions/33504982/postgresql-concurrently-change-column-type-from-int-to-bigint – TheJKFever Sep 14 '18 at 19:58
1

Can you please help me same for the small integer? – Foram May 01 '19 at 11:14
@ForamThakral see my answer – wscourge Jun 06 '19 at 06:07
Purely out of curiosity, how long does it take to apply a migration like this? I guess it could result in hours of downtime if my_table is large? – stevec Oct 22 '20 at 00:05
Unfortunately, it was so long ago that I did this that I don't have a specific recollection of the amount of time it took. And my table wasn't particularly large, so my experience probably wouldn't be applicable. – malikilam Oct 30 '20 at 19:08
I'd say this depends on your database. Postgress copies every row, and locks the table. In one scenario, I saw PG process about 1GB of table size/minute...but YMMV. – David Hempy Jul 29 '21 at 19:51

score 5 · Accepted Answer · answered Jun 06 '19 at 06:05

For anybody looking for different data types than :bigint, you can use :tinyint, :smallint and :mediumint as well, according to ActiveRecord type of integer (tinyint, smallint, mediumint, int, bigint)

# activerecord-3.0.0/lib/active_record/connection_adapters/mysql_adapter.rb
# Maps logical Rails types to MySQL-specific data types.
def type_to_sql(type, limit = nil, precision = nil, scale = nil)
  return super unless type.to_s == 'integer'

  case limit
  when 1; 'tinyint'
  when 2; 'smallint'
  when 3; 'mediumint'
  when nil, 4, 11; 'int(11)'  # compatibility with MySQL default
  when 5..8; 'bigint'
  else raise(ActiveRecordError, "No integer type has byte size #{limit}")
  end
end

Also, do use up and down for rails db:rollback; this is what worked for me:

class ChangeCarNumberOfKeysToSmallInt < ActiveRecord::Migration[5.2]
  def up
    change_column :cars, :number_of_keys, :tinyint
  end

  def down
    change_column :cars, :number_of_keys, :int
  end
end

How do I migrate a database column from integer to bigint in rails?

2 Answers2