Heroku部署Rails应用数据库迁移报错求助：PG::DatatypeMismatch

Hey there, let's work through this PostgreSQL datatype mismatch issue you're hitting on Heroku! Let's break down why your previous attempts didn't work and walk through a reliable fix.

Why Your Previous Migrations Failed #

1. Remove + Add Column Approach:
   This would only work if you don't care about preserving existing data in the downtown_id column—since remove_column deletes all data in that field. If you need to keep the data (which I assume you do), this approach isn't viable. Also, if the migration was already partially applied in production, it could leave your database in an inconsistent state.

2. Change Column with Raw USING String:
   Your syntax was almost right, but there might be two key issues:

   • Your downtown_id column might contain non-numeric values (like strings with letters or symbols) that can't be cast to integers. PostgreSQL will throw an error if it encounters any unconvertible value.
   • Using a raw SQL string instead of Rails' built-in using parameter might lead to unexpected behavior depending on your Rails version.

Step-by-Step Solution #

1. First: Test Locally (Critical!) #

Yes, you must test these migrations in your development environment first—never run untested migrations against production data. Here's how to replicate the production state locally:

• Check what type downtown_id is in production: run heroku run rails console, then type Property.columns_hash['downtown_id'].type to get the current type.
• Update your local development database to match that type (e.g., if it's string, run a temporary migration to change it back), and add test data that mirrors production (including any potentially problematic values).
• Validate your migration code locally before deploying to Heroku.

2. Fix the Migration Code #

If All downtown_id Values Are Numeric

Use Rails' native change_column syntax with the using option—it's cleaner and more Rails-friendly:

def change
  change_column :properties, :downtown_id, :integer, using: 'downtown_id::integer'
end

If There Are Non-Numeric Values

First, clean up invalid data before converting the column type. Create a two-step migration:

def up
  # Fix or remove records with non-numeric downtown_id values
  # Adjust this logic to fit your business needs (e.g., set to nil, delete, or correct)
  Property.where("downtown_id !~ '^\\d+$'").each do |property|
    property.update!(downtown_id: nil) # Example: set invalid values to nil
  end

  # Now convert the column type
  change_column :properties, :downtown_id, :integer, using: 'downtown_id::integer'
end

def down
  # Revert back to original type (e.g., string) if needed
  change_column :properties, :downtown_id, :string
end

3. Deploy and Run Migrations Safely #

• Before running migrations on Heroku, back up your database:

  heroku pg:backups:capture
  

• Deploy your updated migration code to Heroku:

  git push heroku main
  

• Run the migration:

  heroku run rake db:migrate
  

4. Fix Inconsistent Migration States (If Needed) #

If your previous migrations left the database broken:

• Check migration status:

  heroku run rake db:migrate:status
  

• Roll back to the last successful migration:

  heroku run rake db:rollback
  

• Then re-run the corrected migration.

内容的提问来源于stack exchange，提问作者kdweber89