PG::InvalidTextRepresentation错误求助：gl_code字段转整数失败

Hey there, I get it—frustrating when you’ve tried all the usual fixes and still hit that PostgreSQL conversion error. Let’s break down why this is happening and walk through actionable steps to fix it.

The Root Problem #

The error PG::InvalidTextRepresentation: ERROR: invalid input syntax for integer: “aaa” tells us exactly what’s wrong: your gl_code column has non-integer values (like "aaa") stored in it. PostgreSQL can’t automatically convert these strings to integers, so the migration fails. On top of that, since you’re using the Apartment gem for multi-tenancy, you need to make sure every tenant’s database gets cleaned up before the type change.

Step-by-Step Fixes #

1. First: Clean Up Invalid Data Across All Tenants

Before you even attempt to change the column type, you need to handle those non-integer values. Create a rake task to iterate through every tenant and fix the dirty data:

# Add this to lib/tasks/clean_gl_codes.rake
namespace :db do
  desc "Remove or fix invalid gl_code values across all tenants"
  task clean_gl_codes: :environment do
    Apartment.tenant_names.each do |tenant|
      Apartment::Tenant.switch(tenant) do
        # First, identify invalid records
        invalid_cates = Cate.where("gl_code !~ '^[0-9]+$'")
        puts "Tenant #{tenant}: Found #{invalid_cates.count} invalid gl_code entries"

        # Choose one of these options based on your business needs:
        # Option 1: Set invalid values to NULL
        invalid_cates.update_all(gl_code: nil)
        # Option 2: Delete the records entirely
        # invalid_cates.destroy_all
        # Option 3: Replace with a default integer (e.g., 0)
        # invalid_cates.update_all(gl_code: 0)
      end
    end
  end
end

Run this task with:

rake db:clean_gl_codes

2. Write a Safe Migration for Type Conversion

Don’t use a direct change_column—it’ll fail if any invalid data slips through. Instead, use a multi-step migration that safely transitions the column, and make sure it runs for every tenant:

# db/migrate/[timestamp]_change_gl_code_to_integer.rb
class ChangeGlCodeToInteger < ActiveRecord::Migration[6.1]
  def change
    Apartment.tenant_names.each do |tenant|
      Apartment::Tenant.switch(tenant) do
        # Step 1: Add a temporary integer column
        add_column :cates, :gl_code_temp, :integer

        # Step 2: Copy valid string values to the temp column (convert to integer)
        execute <<-SQL
          UPDATE cates 
          SET gl_code_temp = CASE 
            WHEN gl_code ~ '^[0-9]+$' THEN gl_code::integer 
            ELSE NULL 
          END;
        SQL

        # Step 3: Remove the original string column
        remove_column :cates, :gl_code

        # Step 4: Rename the temp column to gl_code
        rename_column :cates, :gl_code_temp, :gl_code

        # Optional: Add constraints if needed (e.g., not null with default)
        # change_column_null :cates, :gl_code, false, 0
      end
    end
  end
end

3. Run Migrations Correctly for Multi-Tenancy

Instead of the standard rake db:migrate, use Apartment’s built-in command to run migrations across all tenants:

rake apartment:migrate

Deployment Tips #

• If you’re using deployment tools like Capistrano, update your deploy script to run rake db:clean_gl_codes before rake apartment:migrate.
• When executing commands via SSH, make sure you’re in the project directory, load the correct environment (e.g., RAILS_ENV=production), and run the tasks in order.

Critical Precaution #

Always back up your database before making these changes—especially in production. Test the entire flow in a staging environment first to avoid unexpected data loss.

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