CODEWITHSUNDEEP
ruby-on-railspostgresqlheroku
user1483441
user1483441

Reputation: 649

Why is my database migration not working and how I can fix it?

everyone. I'm trying to run a migration for my database (postgre) on Heroku, and when I run it, I get the following error:

PGError: ERROR: column "morning_meds" cannot be cast to type "pg_catalog.bool" : ALTER TABLE "users" ALTER COLUMN "morning_meds" TYPE boolean

The migration file in question has the following code: 

class ChangeUserMedsFieldsToBoolean < ActiveRecord::Migration
  def down
    change_column :users, :morning_meds, :string
    change_column :users, :lunch_meds, :string
    change_column :users, :night_meds, :string
  end

  def up
    change_column :users, :morning_meds, :boolean
    change_column :users, :lunch_meds, :boolean
    change_column :users, :night_meds, :boolean
  end
end

I'm not sure how to fix the error or what could be causing the error, so any help you could give me would be great!

Upvotes: 2

Views: 751

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324285

It appears that Rails doesn't offer support for specifying a cast or transform function, which PostgreSQL requires for datatype changes where no implicit cast for that type pair exists.

You need to get Rails to execute:

ALTER TABLE users ALTER COLUMN col_name SET DATA TYPE morning_meds USING bool(col_name)

and since Rails apparently doesn't let you specify the USING clause via migrations, you need to do it manually. See this excellent answer.

Alternately you could, pre-migration, run:

CREATE FUNCTION bool(text) RETURNS BOOLEAN AS $$
SELECT bool($1); 
$$ LANGUAGE 'sql';

CREATE CAST (text AS boolean) WITH FUNCTION bool(text) AS IMPLICIT;

which will allow the ALTER to proceed without an explicit USING clause. You can and probably should drop the cast and bool(text) function after the migration:

DROP CAST (text AS boolean);
DROP FUNCTION bool(text);

Upvotes: 3

Related Questions