7

I have a table called Users with a column Active that is type boolean. I will soon need to change the type of that column to string. I will also need to change all of the column values of Active from :true to "active" and :false to "inactive".

To change the column type I would use Change a column type from Date to DateTime during ROR migration

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 doesn't break it? Will it automatically convert :true to "true"?

Community
  • 1
  • 1
MicFin
  • 2,431
  • 4
  • 32
  • 59

3 Answers3

9

You can do it in one go quite easily using the USING clause of ALTER TABLE:

The optional USING clause specifies how to compute the new column value from the old; if omitted, the default conversion is the same as an assignment cast from old data type to new.

A simple SQL type cast would leave you with the strings 'true' and 'false' so you do want to add a USING. I'd bypass AR and do it by hand:

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 that together with the usual AR-ish change_column stuff by tricking AR into 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

Note that I'm using text as the column type. Rails will using varchar(255) inside the database when you say :string without a limit, that's pretty pointless with PostgreSQL as it handles the storage for all the string types pretty much the same internally, the length restrictions on char(n) and varchar(n) actually make them more expensive to use than text. Then only time :string makes sense with PostgreSQL is when you have a reason to include a specific :limit (and then a text column with a CHECK constraint on the length would make more sense but AR is too dumb to know about "advanced" things like CHECK constraints).

mu is too short
  • 426,620
  • 70
  • 833
  • 800
  • I ran your AR-ish solution as a migration and it did The Right Thing. Thank you for a thorough explanation! – MicFin Oct 13 '14 at 23:43
  • I wonder what would happen if you run this on sqlite? Seems very elegant for Postgres though! good stuff – SomeDudeSomewhere Oct 14 '14 at 00:05
  • @DevDude: It would probably fall over and catch fire. But if you're developing on SQLite and deploying on PostgreSQL then you'll get exactly what you deserve: pain and suffering. If you're expecting ActiveRecord to give you database portability then you're sadly mistaken, it doesn't work that way. – mu is too short Oct 14 '14 at 00:28
  • yeah, definitely mu, I was just stating that someone else should be careful with sqlite and some of these stuff in the migrations. Thanks – SomeDudeSomewhere Oct 14 '14 at 01:40
3

An easy way to make your migration flawlessly is to rename your boolean :active column. Add the new column. Run a SQL update and then remove the unused column. All could be done in the same migration. Like this. Down migration not included, so use at 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
SomeDudeSomewhere
  • 3,928
  • 1
  • 23
  • 27
  • 1
    Caveat: If its postgresql (which he doesn't specifically state but he has tagged it with postgresql) then the values may be set to "t" and "f" rather than "true" and "false". MicFin - do "select distinct active from users" first in your sql console to check what the actual values are. Then you can use those like @DevDude suggests in his answer, – Max Williams Oct 13 '14 at 16:25
  • 1
    @MaxWilliams: PostgreSQL has a native `boolean` type and it allows you to use `true`, `false`, `'t'`, and `'f'` as literal booleans so this is fine. However, this is a lot more work than is necessary: you could say `where active_boolean` and `where not active_boolean` instead of the direction comparisons, one UPDATE with a CASE expression would be sufficient, and you could roll that UPDATE right into the ALTER TABLE by adding a USING clause. – mu is too short Oct 13 '14 at 19:03
  • This answer seems to work as well but @muistooshort's answer seems simpler and safer (down included). Please let me know if there is something that I am missing as to why this should be the correct answer. – MicFin Oct 13 '14 at 23:40
  • SQLite won't like this solution either, it won't know what `true` and `false` mean. SQLite uses C-style 1 and 0 for booleans natively but [stupid AR uses `'t'` and `'f'` strings for booleans when talking to SQLite](http://stackoverflow.com/a/6013177/479863) (at least Rails3 did). – mu is too short Oct 14 '14 at 00:30
  • @muistooshort ah, i see: so "true" will work even if a read shows the value as "t"? That seems really weird :) – Max Williams Oct 14 '14 at 08:12
  • 1
    @MaxWilliams: That's probably the SQL standards committee and PostgreSQL both trying to support multiple competing implementations of [`boolean`](http://www.postgresql.org/docs/current/interactive/datatype-boolean.html). OTOH, it really isn't that different than `11`, `0xb`, `013`, and `0b1011` meaning the same thing. – mu is too short Oct 14 '14 at 17:04
0

I have not done it, but I think it's just adding more to your up method or writing a separate migration to handle the up part. 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 make it an if/else to handle false too. Should work. I just tested it in the console on a single user in my database, so seems fine.

Art
  • 781
  • 4
  • 13
  • Art, I would be very careful with touching the models like this on a migration because of the following reason, by updating the model like so, you will be triggering Active Record Callbacks which could cause all sorts of unexpected issues.. i.e. Observers to fire, etc, be careful with this approach! – SomeDudeSomewhere Oct 13 '14 at 16:30
  • Thank you for the comment. I'm still a Rails noob and am not always sure of how all items fit together. Advice like this is very helpful to me so thank you for taking the time. I think the option above is more complete than mine so it is a better answer anyway :). – Art Oct 13 '14 at 16:45