Rails change column type and update column values
I have a table called "Users" with a column "Asset" which is of type boolean. I will soon need to change the type of this column to string. I will also need to change all the values of the Active column from: true to "active" and: false to "inactive".
To change the column type I would use Change column type from date to DateTime during ROR transfer
class ChangeColumnTypeInUsers < ActiveRecord::Migration
def up
change_column :users, :active, :string
end
def down
change_column :users, :active, :boolean
end
end
How would I update the column value so that the type change does not break it? Will it be automatically converted: true to "true"?
source to share
You can do this quickly enough by using the USING clause in ALTER TABLE :
An optional clause
USING
specifies how to compute the new column value from the old one; if omitted, the default conversion is the same as the conversion converted from the old data type to the new one.
A simple SQL cast will leave you with strings 'true'
and 'false'
so you really want to add USING. I would bypass AR and do it manually:
connection.execute(%q(
alter table users
alter column active
type text
using case when active then 'active' else 'inactive' end
))
The important part for you is the using case...
part at the end. You can use this alongside the usual AR-ish stuff by change_column
tricking AR by doing the right thing:
class ChangeColumnTypeInUsers < ActiveRecord::Migration
def up
change_column :users, :active, "text using case when active then 'active' else 'inactive' end"
end
def down
change_column :users, :active, "boolean using active = 'active'"
end
end
Please note that I am using text
as the column type. Rails will use varchar(255)
inside the database when you say :string
no limitation, which is pretty pointless with PostgreSQL, since it handles storage for all string types internally , almost the same , length constraints for char(n)
and varchar(n)
actually make them more expensive than text
. Then only timing :string
makes sense with PostgreSQL when you have a reason to include a certain :limit
(and then text
a constrained column CHECK
would make more sense, but AR is too stupid to know about "advanced" things like CHECK
constraints).
source to share
An easy way to migrate flawlessly is to rename your boolean: active column. Add a new column. Run SQL update and drop the unused column. Everything could be done with the same migration. Like this. Down migration is not included, so use your own peril :).
class ChangeColumnTypeInUsers < ActiveRecord::Migration
def up
rename_column :users, :active, :active_boolean
add_column :users, :active, :string
execute "UPDATE users SET active = 'true' WHERE active_boolean = true"
execute "UPDATE users SET active = 'inactive' WHERE active_boolean = false"
remove_column :users, :active_boolean
end
end
source to share
I didn't do that, but I think it's just adding your method up or a separate migration to handle up. Such that ...
class ChangeColumnTypeInUsers < ActiveRecord::Migration
def up
change_column :users, :active, :string
User.find_each do |user|
user.active = "active" if user.active = true
user.save!
end
end
def down
change_column :users, :active, :boolean
end
end
You can do this if / else to handle false. It should work. I just tested it in the console on one user in my database so that seems fine.
source to share