Defect #6943

Migration from boolean to varchar fails on PostgreSQL 8.1

Added by Jakob Skjerning about 7 years ago. Updated about 7 years ago.

Status:ClosedStart date:2010-11-19
Priority:NormalDue date:
Assignee:-% Done:

0%

Category:Database
Target version:1.1.0
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?

mail_notification_migration.patch Magnifier (2.55 KB) Jean-Philippe Lang, 2010-11-19 19:39

Associated revisions

Revision 4413
Added by Jean-Philippe Lang about 7 years ago

Fixed: Migration from boolean to varchar fails on PostgreSQL 8.1 (#6943).

History

#1 Updated by Jean-Philippe Lang about 7 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.

#2 Updated by Erik Ordway about 7 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.

#3 Updated by Jean-Philippe Lang about 7 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.

#4 Updated by Jean-Philippe Lang about 7 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.

#5 Updated by Erik Ordway about 7 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) ==========================

#6 Updated by Jean-Philippe Lang about 7 years ago

Thanks for your help.

#7 Updated by Jean-Philippe Lang about 7 years ago

  • Status changed from New to Closed
  • Resolution set to Fixed

Committed in r4413.

Also available in: Atom PDF