How can I create a column of bigint sql type in migration?

rubyonrails-talk
1

Hi all,

I want to use bigint sql type for a model's id. How can I do this in migration? Should I just use the integer ruby type?

2

i would think that the standard INT column type would be enough, as, even when signed, allows for values over 2 billion (over 4 billion unsigned) where a BIGINT would allow for over 9 quintillion (9,000,000,000,000,000,000).

so if you really do need a BIGINT data type, there is no way to do it via standard migration functionality.

you can issue an alter table command and change the column type however.

self.up   ...   execute("ALTER TABLE things CHANGE column_name column_name BIGINT") end

3

Not to call you out Chris but have you ever tried

class CreateThings < ActiveRecord::Migration def self.up create_table :things do |t| t.column :my_big_int, :bigint t.column :my_medium_text, :mediumtext

  # You can even specify limits!
  t.column :my_char_64, :"char(64)"
end

end

def self.down drop_table :things end end

in your migrations. I think you’d be pleasantly surprised by the results. Yes, it’s database specific. Yes, it doesn’t have DDH’s imprimatur. But sometimes it pays to be agile. :slight_smile: The only downside I saw was that the schema.rb file doesn’t correctly reflect the actual database. What MySQL truly recognizes as CHAR(64) is label in the schema as :string, :limit => 64, which is database agnostic but not exactly true to the DB. What this would entail in actual practice? I dunno. I’ve never used the schema.rb [that I know of] so it’s possible that there are no drawbacks but I thought in the interest of full disclosure that I’d let y’all know.

4

you're right, i've never used :bigint, as I was going by the documentation at

which states that only the following

:primary_key, :string, :text, :integer, :float, :decimal, :datetime, :timestamp, :time, :date, :binary, :boolean

are allowed parameters when specifying column type.

5

I think that the API is aimed at being as db-agnostic as you can. Which I completely do agree with. I was just trying to share the wealth with my twocents. Sometimes you have situations that don’t have to be db-agnostic and if you’re aware of the pros and cons you should go where agile takes you.

RSL

6

I agree whole-heartedly. There are quite a bit of hidden/unknown features that can definitely be taken advantage of if you know about them and the pros outweigh the cons.

7

I want to use bigint sql type for a model's id. How can I do this in migration? Should I just use the integer ruby type?

Just use   t.integer :foo, :limit => 8

I'm pretty sure that doesn't do what you think it does. It creates a an int(8) in mysql (as opposed to the default which is int(11)) but this does not affect the size of the number that can be stored in the field. An int field in mysql is 32 bits long regardless of the :limit you set (that's only the 'display' length).

If you want to store numbers greater than 2147482624 you hvae to use bigint.

steve