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

Carrington

check once the column data type in the schema.rb file again? – Buggery

The error message suggests that the column is actually an int. What does your schema.rb have to say? What does \d my_object_times say from inside psql? – Colchicum

On a side note, you should store durations with the interval data type, not a bigint. It's a 64-bit integer internally, like bigint, but if you use interval it's more semantically useful and you get access to all sorts of handy operators. – Radiometeorograph

“\d my_object_times” lists “time_in_ms | bigint,” exactly what I put in my question above. In the db/schema.rb file, it lists ‘ t.integer "time_in_ms", limit: 8’. – Carrington

This type of question where something is happening that doesn't make sense is difficult to answer without precise steps to reproduce the issue (the shorter the better). For example can you reproduce the issue using the rails bug template (github.com/rails/rails/blob/master/guides/bug_report_templates/… - adjust rails version as appropriate) – Natale

Is it possible you created the table manually then overwrote it with the migration? – Mucoprotein

can you paste the create table statement generated in postgresql?. This type of error occurs if the column is integer. You can create migration to convert the integer field to bigint. So please verify the exact datatype of the column from postgresql. – Hophead

Are you sure the error is coming from the time_in_ms column not the other integer column in your query? – Natale

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

Decastere

Answer 2

edit: I just re-read this and my original answer actually does not make sense in your case. I do believe you need to look outside that column for an answer, and confirm every bit of what you think you know about the state of it, manually. Any addition of detail would help us find a correct answer. Set breakpoints to step through the request and see if you can spot the integer

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

t.integer - this looks like your culprit to me. ... Well, I tried, my last thought is that it has to be related to some kind of Rails request middleware, but I'm ignorant of what the specifics might be. Something in the request path thinks that column is an integer. I didn't understand how Rails migration datatypes worked until now, so I learned something. (And I went fishing all day, so I'll count this day a win.) Good luck!

Mucoprotein

I'm not understanding what you want me to do. What additional information woudl help? – Carrington

I honestly don't know, I am just trying to think of what I would do. I'd confirm the data schema shows a bigint on the actual DB column for time_in_milliseconds. The error message is definitely indicating some part of the system (I'm not a rails expert, but Active Record looks like it is saying so) believes that value should be an integer not bigint... post your model, seeds, migrations and table structure output from PostGre, maybe? – Mucoprotein

I included the db/schema.rb in my question and also in my question was included the "\d my_object_times" definition from the PostGresdb. – Carrington

In my db/schema.rb file, I already have 't.integer "time_in_ms", limit: 8', so I'm not clear on what you're asking above. If "t.integer" is the culprit, what do you want me to change the column type to? – Carrington

Answer 3

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

Hophead

Answer 4

For anyone on Rails 5/6 using the paper_trail gem, check what the polymorphic foreign_key id field item_id is set as in versions. I had it set as an integer, and got this bug.

Changing versions.item_id to a bigint fixed the error.

Groot

Answer 5

bigint is 64-bit, while Rails is 32-bit.

3000002000 is greater than 2^32. That's why converting it into a 32-bit integer fails with NumericValueOutOfRange.

Isoagglutinin

Huh? The error message suggests that the database is complaining and the OP claims that they created the column as a 64 bit bigint. – Colchicum

@muistooshort PG::NumericValueOutOfRange - suggests it is the range-related conversion issue, exactly the problem I explain in my answer ;) – Isoagglutinin

The error is coming from the database, not Rails. Ruby will upgrade integers to bignums as needed. The Ruby in use almost certainly 64bit anyway seeing as this is 2016. – Colchicum

@vitaly-t, I'm not understanding what answer you're proposing to fix my problem. Can you list that out? If it works, I'll accept and make up for that "-1" that someone docked you with. – Carrington