2

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

Ismael Abreu
  • 16,443
  • 6
  • 61
  • 75
Ringo Blancke
  • 2,444
  • 6
  • 30
  • 54
  • Your code should work only if all values in prize column are convertible to integer, see also [this answer](http://stackoverflow.com/questions/3537064/rails-migration-to-convert-string-to-integer) – gshilin Jun 30 '12 at 19:14
  • 1
    Is there any reason you're not using the same database for development as you are for production? – Sam Dolan Jun 30 '12 at 19:49

2 Answers2

2

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

Ismael Abreu
  • 16,443
  • 6
  • 61
  • 75
2

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.

mu is too short
  • 426,620
  • 70
  • 833
  • 800
  • I'm getting this error: Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'type integer using cast(prize as integer) – Ringo Blancke Jun 30 '12 at 19:35
  • @RingoBlancke: You'll have to use different things in your migration for different databases. Check my update. – mu is too short Jun 30 '12 at 20:10