2

I'm using PostgreSQL with Rails, and I have a table with int primary keys. I'm pretty sure it will run out of uids the way I'm using it (billions of inserts eventually). Is there any way I convert the int primary keys to bigint? I've only seen solutions on SO involving creating a new table with bigint primary keys.

Tsubaki
  • 145
  • 2
  • 7

1 Answers1

3

You have some options. One of them is using raw SQL:

class TheMigration < ActiveRecord::Migration
    def up
        execute "alter table .....;" 
    end

    def down
        raise ActiveRecord::IrreversibleMigration
    end
end

But you should be OK with the :limit option (http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/TableDefinition.html#method-i-column) - which allows you to define the number of bytes for integer types:

t.some_col :integer, limit: 8.

https://moeffju.net/blog/using-bigint-columns-in-rails-migrations

Ernest
  • 8,701
  • 5
  • 40
  • 51
  • What comes after "alter table"? Your "normal" way doesn't create primary keys, it only creates a new arbitrary bigint column, not primary. – Tsubaki Jan 26 '14 at 22:56
  • @user2805279 What comes after alter table? You asked about rails migrations, I assumed that you know basic SQL. And the answer to the second question is in the blog post I linked. – Ernest Jan 26 '14 at 23:00
  • @user2805279 `ALTER TABLE mytable ALTER COLUMN my_id_column TYPE bigint` . The `ALTER TABLE` documentation would tell you that, as would the links in the above post. **However** this gets more complicated if there are foreign key references to the table. – Craig Ringer Jan 27 '14 at 09:29
  • How about `change_column :table, :id , "bigint NOT NULL AUTO_INCREMENT"`? Would that work? – Tsubaki Jan 27 '14 at 11:03
  • @user2805279 Have you tried the version with ```limit```? Also, in general when you're using ActiveRecord, you don't need to define FK constrains (as this is done for you on application level). This is a bit SQL antipattern, but.. – Ernest Jan 27 '14 at 12:28