Migration from string to Boolean Rails

Question

I have two columns in one of my tables that I need to convert from a string to a boolean. The objects already have data attached to these columns (yes, no or nil) and so would need to convert the yes results to true and no/nil to false.

I am running a simple migration for change_column but that is clearly not the way to go and I am having trouble finding a solution.

My table is as follows and the two columns are inner_sleeve and inserts.

create_table "listings", force: :cascade do |t|
    t.string "front_cover"
    t.bigint "version_id"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.string "back_cover"
    t.string "front_label"
    t.string "back_label"
    t.integer "step", default: 0
    t.string "cover_conditions", default: [], array: true
    t.string "cover_grading"
    t.text "cover_info"
    t.boolean "original_sleeve"
    t.string "disc_grading"
    t.text "disc_info"
    t.string "inner_sleeve"
    t.string "inserts"
    t.text "inner_inserts_info"
    t.text "other_info"
    t.bigint "user_id"
    t.string "disc_conditions", default: [], array: true
    t.integer "status", default: 0
    t.date "first_active_at"
    t.boolean "order_dispatched", default: false
    t.bigint "purchaser_id"
    t.index ["purchaser_id"], name: "index_listings_on_purchaser_id"
    t.index ["user_id"], name: "index_listings_on_user_id"
    t.index ["version_id"], name: "index_listings_on_version_id"
  end

Thank you!

Does this post help? https://stackoverflow.com/questions/26344257/rails-change-column-type-and-update-column-values — Thang, Feb 12 '20 at 15:29
Unfortunately not. I get the following error:`PG::DatatypeMismatch: ERROR: column "inner_sleeve" cannot be cast automatically to type boolean` — David Gordon, Feb 12 '20 at 15:59

score 1 · Answer 1 · answered Feb 12 '20 at 17:31

In times like this I really recommend falling back to good ol' SQL to handle this kind of things. To present you with an example, I created a small table in PostgreSQL:

convert_test=# select * from listings;
 id | inserts | inner_sleeve 
----+---------+--------------
  1 | yes     | no
  2 | yes     | 
  3 | no      | 
  4 |         | 
(4 rows)

Then I executed two raw SQL statements to change column type:

ALTER TABLE listings ALTER COLUMN inserts TYPE boolean USING CASE WHEN inserts = 'yes' THEN true ELSE false END;
ALTER TABLE listings ALTER COLUMN inner_sleeve TYPE boolean USING CASE WHEN inner_sleeve = 'yes' THEN true ELSE false END;

This should be self-explaining, except maybe that USING statement is exactly what to run in order to convert the values in the column.

The result is as expected:

convert_test=# select * from listings; id | inserts | inner_sleeve 
----+---------+--------------
  1 | t       | f
  2 | t       | f
  3 | f       | f
  4 | f       | f
(4 rows)

Migration from string to Boolean Rails

1 Answers1