CODEWITHSUNDEEP
mysqlruby-on-railsrubypostgresqlactiverecord
Ringo Blancke
Ringo Blancke

Reputation: 2444

RoR: Cannot change_column in postgres, fine in MySQL (MySQL for development, Postgres on Heroku)

I had a column called "prize":

create_table :contests do |t|
  t.text :prize

I recently realized that this will always be an integer and I wanted to set a default value:

def change
  change_column :contests, :prize, :integer, :default => 200

This works fine on my local machine, where I am using MySQL DB. However, when I push to my production site (which is hosted on Heroku, which gives me a Postgres DB) I get the following error:

PGError: ERROR:  column "prize" cannot be cast to type "pg_catalog.int4"
: ALTER TABLE "contests" ALTER COLUMN "prize" TYPE integer

In this article: http://www.postgresonline.com/periodical.php?i_id=3 they discuss the use of the USING to solve this problem. But I don't know how I can do that, and whether this is appropriate for what I'm trying to do.

Any insight into figuring this out would be very much appreciated.

Thanks! Ringo

Upvotes: 2

Views: 1369

Answers (2)

mu is too short
mu is too short

Reputation: 434795

I think you'll have to do it by hand:

def up
  connection.execute(%q{
    alter table contests
    alter column prize type integer using cast(prize as integer),
    alter column price set default 200
  })
end

If you want to run the same migration in both MySQL and PostgreSQL then:

def up
  case ActiveRecord::Base.connection
  when ActiveRecord::ConnectionAdapters::PostgreSQLAdapter
    connection.execute(%q{
      alter table contests
      alter column prize type integer using cast(prize as integer),
      alter column price set default 200
    })
  when ActiveRecord::ConnectionAdapters::MySQLAdapter
    # MySQL version...
  end
end

Once you get around this problem, your next task will be to switch your development environment over to PostgreSQL so that you can start fixing all the other little problems (such as GROUP BY issues, case sensitive LIKE, column truncation behavior, ...) that you'll run into.

Upvotes: 2

Ismael
Ismael

Reputation: 16730

First you should use the same DB in both environments to prevent this kind of surprises.

To run raw sql in migrations see this example http://guides.rubyonrails.org/migrations.html#using-the-up-down-methods

Upvotes: 2

Related Questions