Defect #6943
closedMigration from boolean to varchar fails on PostgreSQL 8.1
0%
Description
After upgrading to trunk revision 4411, I went ahead and attempted to migrate the database:
$ rake db:migrate (in /var/www/apps/redmine) == ChangeUsersMailNotificationToString: migrating ============================ -- change_column(:users, :mail_notification, :string, {:null=>false, :default=>""}) rake aborted! An error has occurred, this and all later migrations canceled: PGError: ERROR: column "mail_notification" cannot be cast to type "pg_catalog.varchar" : ALTER TABLE "users" ALTER COLUMN "mail_notification" TYPE character varying(255) (See full trace by running task with --trace)
I am running:
- Rails 2.3.5
- Ruby 1.8.7
- PostgreSQL 8.1
- Redmine r4411
Looks like PostgreSQL doesn't know how to cast a boolean to a varchar so db/migrate/20100129193402_change_users_mail_notification_to_string.rb isn't going to work on PostgreSQL as far as I can tell.
What values are the booleans expected to be cast to? I am guessing MySQL will cast false and true to '0' and '1' seeing how it doesn't have a boolean type.
The process for getting that in PostgreSQL (and probably other DBMS with boolean types) is a bit more complex than simply changing the column type. We're looking at something like:
ALTER TABLE users RENAME COLUMN mail_notification TO boolean_mail_notification; ALTER TABLE users ADD COLUMN mail_notification VARCHAR(255) NOT NULL DEFAULT ''; UPDATE users SET mail_notification = '1' WHERE boolean_mail_notification = true; UPDATE users SET mail_notification = '0' WHERE boolean_mail_notification = false; ALTER TABLE users DROP COLUMN boolean_mail_notification;
Is that the expected behavior?
Files
Updated by Jean-Philippe Lang almost 14 years ago
- Subject changed from Migration fails on PostgreSQL 8.1 to Migration from boolean to varchar fails on PostgreSQL 8.1
- Target version set to 1.1.0
Changing a boolean column to varchar was a bad idea. I'll have to fix it before it goes in stable branch.
Anyway, what you describe is pretty much what was expected. You should be able to run the migrations after running these alter/update.
Updated by Erik Ordway almost 14 years ago
I think it is closer to
ALTER TABLE users RENAME COLUMN mail_notification TO boolean_mail_notification;
ALTER TABLE users ADD COLUMN mail_notification VARCHAR NOT NULL DEFAULT '';
UPDATE users SET mail_notification = 'selected' WHERE boolean_mail_notification = true;
Note the last line. Your question help me thanks. It is annoying when mysql'ism sneak in like this.
Updated by Jean-Philippe Lang almost 14 years ago
Erik Ordway wrote:
I think it is closer to
ALTER TABLE users RENAME COLUMN mail_notification TO boolean_mail_notification;
ALTER TABLE users ADD COLUMN mail_notification VARCHAR NOT NULL DEFAULT '';
UPDATE users SET mail_notification = 'selected' WHERE boolean_mail_notification = true;Note the last line. Your question help me thanks. It is annoying when mysql'ism sneak in like this.
Actually, values are updated by another migration, see r4216 that added the 2 migrations.
Your UPDATE is not really what is expected, see the second migration.
I'll fix the migration asap so that it runs more smoothly.
Updated by Jean-Philippe Lang almost 14 years ago
I don't have a postgresql 8.1 running, can you give the attached patch a try?
It was tested with postgresql 8.3, mysql 5.1 and sqlite 3.
Updated by Erik Ordway almost 14 years ago
Yep that seems to do it and the resulting data looks like this.
"all"
"only_my_events"
"only_my_events"
"only_my_events"
== ChangeUsersMailNotificationToString: migrating ============================ -- rename_column(:users, :mail_notification, :mail_notification_bool) -> 0.0013s -- add_column(:users, :mail_notification, :string, {:default=>"", :null=>false}) -> 0.0043s -- remove_column(:users, :mail_notification_bool) -> 0.0017s == ChangeUsersMailNotificationToString: migrated (0.0318s) =================== == UpdateMailNotificationValues: migrating =================================== == UpdateMailNotificationValues: migrated (0.0000s) ==========================
Updated by Jean-Philippe Lang almost 14 years ago
- Status changed from New to Closed
- Resolution set to Fixed
Committed in r4413.