Project

General

Profile

Actions

Defect #6943

closed

Migration from boolean to varchar fails on PostgreSQL 8.1

Added by Jakob Skjerning over 13 years ago. Updated over 13 years ago.

Status:
Closed
Priority:
Normal
Assignee:
-
Category:
Database
Target version:
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

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

Also available in: Atom PDF