So here's a goodie for those of you using postgresql.
Let's say you have a migration to do where you need to change a column from an integer to a boolean. So if you're doing a rails migration, you might write something like
And then postgresql throws up (well, it does on Rails 2.3.5):
== ChangeDatatableAttributes: migrating ======================================
-- change_column(:datatables, :is_numeric, :boolean)
rake aborted!
An error has occurred, this and all later migrations canceled:
PGError: ERROR: column "is_numeric" cannot be cast to type boolean
: ALTER TABLE "datatables" ALTER COLUMN "is_numeric" TYPE boolean
Well, that's no good. The web was not exactly much help here, but I managed to find this in the postgresql docs:
the USING expression is not applied to the column's default value (if any); the result might not be a constant expression as required for a default. This means that when there is no implicit or assignment cast from old to new type, ALTER TYPE might fail to convert the default even though a USING clause is supplied.
Looks like you need to drop the default also, and then set it afterwards.
Or if you'd like, create a method that you can throw into a module to mixin.
And use it like so:
Or if all else fails, you can just follow Heroku's advice and just drop the column and create another. Tip!
There is one simple solution to alter table
ReplyDelete1. For changing type of column:
class AlterUserColumn < ActiveRecord::Migration
def self.up
change_column :users, :admin, :integer, :default=>nil
end
def self.down
end
end
2.To Alter whole table structure :
class AlterUserColumn < ActiveRecord::Migration
def self.up
execute 'SIMPLE mysql alter query'
end
def self.down
execute 'opposite query to above written query'
end
end
Great post! Thanks for the help.
ReplyDeleteTo check for Postgres adapter you may do that:
ReplyDelete> ActiveRecord::Base.retrieve_connection.kind_of?(ActiveRecord::ConnectionAdapters::PostgreSQLAdapter)
=> true