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


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

On 4/9/07, Chris H. removed_email_address@domain.invalid wrote:

if you really do need a BIGINT data type, there is no way to do it


#4

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


#5

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

http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/TableDefinition.html#M000762

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.


#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


#8

allen wrote:

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?

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