Getting "value "3000002000" is out of range for type integer"

Question

I’m using Rails 4.2.3 with a PostGre database. I want a column in my database to store a number of milliseconds — note, NOT a timestamp, but rather a duration in milliseconds. So I created my column like so

time_in_ms | bigint

However, when I go to store a value in Rails, I get the below error

ActiveRecord::StatementInvalid (PG::NumericValueOutOfRange: ERROR:  value "3000002000" is out of range for type integer
: INSERT INTO "my_object_times" ("time_in_ms", "my_object_id", "created_at", "updated_at") VALUES ($1, $2, $3, $4) RETURNING "id"):
  app/controllers/my_objects_controller.rb:31:in `update'

It would seem the number, “3000002000” is smaller than the maximum value for the column (which I’m reading is “9223372036854775807”), so I’m wondering what else is going wrong and how I can fix it.

Edit: To provide additional information, in my db/schema.rb file, the column in question is described thusly ...

create_table "my_object_times", force: :cascade do |t|
  ...
  t.integer  "time_in_ms",     limit: 8

Edit 2: Here is the output of create table in PSQL

CREATE TABLE my_object_times (
    id integer NOT NULL,
    first_name character varying,
    last_name character varying,
    time_in_ms bigint,
    created_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone NOT NULL,
    name character varying,
    age integer,
    city character varying,
    state_id integer,
    country_id integer,
    overall_rank integer,
    age_group_rank integer,
    gender_rank integer
);

Answer 1

I have had it happen to me before where when I initially try to create a bigint field in the db, for some reason the Model thinks it is an integer instead, even when the schema and migration file specify it as a bigint.

For example: I had this migration file

class CreateSecureUserTokens < ActiveRecord::Migration
  def change
    create_table :secure_user_tokens do |t|
      t.integer :sso_id, null: false, length: 8
      t.string :token, null: false

      t.timestamps null: false
    end
  end
end

Note, it has the included length: 8 requirement to make an integer a bigint. However, after I ran the migration, I was having the same issue as you. Eventually I just created another migration to try and fix the issue, and it worked. Here's the migration I used to fix the issue:

class ModifySecureTokensForLargerSsoIdSizes < ActiveRecord::Migration
  def change
    change_column :secure_user_tokens, :sso_id, :integer, limit: 8
  end
end

So if we changed that to fit your needs, it would be:

class ObjectTimesBigInt < ActiveRecord::Migration
  def change
    change_column :my_object_times, :time_in_ms, :integer, limit: 8
  end
end

Hope that helps! -Charlie

Answer 2

I guess, the table my_object_times might not be created from the schema.rb file or it might be overwritten in other migration file. Because in the migration file integer column with limit 8 is itself a bigint. So you should cross-check the table definition from the PG-admin. If the column is not bigInt then run the following migration

class ChangeTimeInMsToBigint < ActiveRecord::Migration
  def change
  execute <<-SQL
    ALTER TABLE my_object_times
    ALTER COLUMN time_in_ms TYPE bigint USING time_in_ms::bigint
  SQL
end
end