PG::InvalidTextRepresentation: ERROR: invalid input syntax for integer: “aaa”

Question

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.

The entry aaa in your gl_code column cannot be converted to an integer. What's unclear about that? — Laurenz Albe
– Laurenz Albe, Commented Feb 8, 2018 at 21:14
Have you tried looking at your data to see if there's a gl_code 'aaa' lying around anywhere? — dmfay
– dmfay, Commented Feb 8, 2018 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
– alexts, Commented Feb 8, 2018 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
– alexts, Commented Feb 8, 2018 at 22:47

Laurenz Albe · Accepted Answer · 2018-02-09 04:30:03Z

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;$$;

answered Feb 9, 2018 at 4:30

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

1 Comment

alexts Over a year ago

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.

Collectives™ on Stack Overflow

PG::InvalidTextRepresentation: ERROR: invalid input syntax for integer: “aaa”

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