0

I have followed all suggestions from similar questions but none has solved my problem. I want to change the field gl_code from string to integer and have tried the following:

class ChangeCategoryGlCode < ActiveRecord::Migration
  def change
    # Category.all.each { |cat| cat.update(gl_code: cat.gl_code.to_i) }
    Category.where("gl_code IS NULL OR gl_code = ''").update_all({gl_code: '0'})
    # change_column :categories, :gl_code, :integer, using: 'gl_code::integer'
    change_column :categories, :gl_code, 'integer USING CAST(gl_code AS integer)'
  end
end

But nothing seems to work. I even ssh'd to the server and run the commands manually but whenever I try to deploy it fails at rake db:migrate with the error above.

Any suggestions/hints are welcome.

Previous questrions (1), (2)

Edit: If that matters, I am using the Apartment gem, and have tried changing the gl_code for Category for each tenant.

alexts
  • 180
  • 2
  • 13
  • The entry `aaa` in your `gl_code` column cannot be converted to an integer. What's unclear about that? – Laurenz Albe Feb 08 '18 at 21:14
  • Have you tried looking at your data to see if there's a `gl_code` 'aaa' lying around anywhere? – dmfay Feb 08 '18 at 21:15
  • @LaurenzAlbe, @dmfay I have searched for `gl_code` "aaa" but there is no item with a code like that. `Category.where(gl_code: "aaa")` returns an empty list. I also tried `Category.all.each { |c| puts c.gl_code if c.gl_code.kind_of?(String) }` and I still get nothing. Tbh I remember adding a gl_code like that (for testing purposes) but shouldn't what I tried return something? – alexts Feb 08 '18 at 22:43
  • In my migration file `Category.all.each { |cat| cat.update(gl_code: cat.gl_code.to_i) }` or ` Category.where("gl_code IS NULL OR gl_code = ''").update_all({gl_code: '0'})`. Those should have fixed any `gl_code` that is a string, or am I missing something? – alexts Feb 08 '18 at 22:47

1 Answers1

0

Use PL/pgSQL to find the guilty row:

DO
$$DECLARE
   v_gl_code text;
   v_id bigint;
BEGIN
   FOR v_id, v_gl_code IN
      SELECT id, gl_code FROM category
   LOOP
      BEGIN
         PERFORM v_gl_code::integer;
      EXCEPTION
         WHEN OTHERS THEN
            RAISE NOTICE 'Bad value at id = %', v_id;
      END;
   END LOOP;
END;$$;
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • I tried this and got nothing. I eventually dropped the db and created a new one, as it was a dev db, but it is not the solution I wanted, so marking my action as correct would not feel right. Thank you for the help. – alexts Feb 09 '18 at 13:54