PG::InvalidTextRepresentation: ERROR: invalid input syntax for integer: "M"

Question

So I have a gender column on my user model and it's currently a string, I'd like to change it to a integer and make Male '1', and Female '0' as it's presently Male "M" Female "F". When running this migration:

class ChangeGenderToIntegerOnUser < ActiveRecord::Migration
  def change
    change_column :users, :gender, 'integer USING CAST(gender AS integer)'
  end
end

I get the following error:

error message:

PG::InvalidTextRepresentation: ERROR:  invalid input syntax for integer: "M"
: ALTER TABLE "users" ALTER COLUMN "gender" TYPE integer USING CAST(gender AS integer)/usr/local/rvm/gems/ruby-2.0.0-p247/gems/activerecord-4.0.0.rc1/lib/active_record/connection_adapters/postgresql/database_statements.rb:128:in `exec'

What should I do to properly change gender to integer?

Thanks in advance!

Lukáš Voda · Accepted Answer · 2013-12-21 03:56:05Z

13

You need to first convert the values M to 1 and F to 0 and then change the column type.

class ChangeGenderToIntegerOnUser < ActiveRecord::Migration
  def change
    User.where(gender: 'M').update_all(gender: 1)
    User.where(gender: 'F').update_all(gender: 0)
    change_column :users, :gender, 'integer USING CAST(gender AS integer)'
  end
end

answered Dec 21, 2013 at 3:56

Lukáš Voda

1,2921 gold badge13 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BenKoshy Over a year ago

thank you for your answer. Will doing it this way be performant on large databases - if not, is there a better way?

Collectives™ on Stack Overflow

PG::InvalidTextRepresentation: ERROR: invalid input syntax for integer: "M"

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related