Actions
Defect #6943
closed
Migration from boolean to varchar fails on PostgreSQL 8.1
Start date:
2010-11-19
Due date:
% Done:
0%
Estimated time:
Resolution:
Fixed
Affected version:
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 
Updated by
Actions