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

Question

Im using Rails 4.2.3 with a PostGre database. I want the column in my database to store a few milliseconds - note, not the timestamp, but the duration in milliseconds. So I created my column like this

time_in_ms | bigint 

However, when I go to store the value in Rails, I get the following 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 that the number "3000002000" is less than the maximum value for the column (reading of which is "9223372036854775807"), so I wonder what else is going wrong and how I can fix it.

Edit: To provide more information, in my db / schema.rb file, the column in question is described this way ...

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

Edit 2: Here is the result of creating a 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

This happened to me before where, when I initially try to create a bigint field in db, for some reason the model thinks it is an integer, even if the schema and migration file indicates it as 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 that it has an included length: 8 requirement to make an integer bigint. However, after I migrated, I had the same problem as you. In the end, I just created another migration to try to fix the problem, and it worked. Here, the migration I used to fix the problem:

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

So, if we change this according to your needs, it will be:

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

Hope this helps! -Charlie

Answer 2

edit: I just re-read this, and my original answer doesn't really make sense in your case. I believe that you need to look beyond this column to answer, and confirm each bit of what you think of its condition manually. Any addition of details will help us find the right answer. Set breakpoints to run the query and see if you can define an integer

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

t.integer - it looks like your criminal to me. ... Well, I tried, my last thought is that it should be connected with some kind of middleware Rails request, but I do not know what these features are. Something in the query path thinks the column is an integer. I did not understand how Datatypes Rails migrations worked so far, so I learned something. (And I went fishing all day, so on this day I will count the victory.) Good luck!

Answer 3

I assume that the table my_object_times may not be created from the schema.rb file or may be overwritten in another migration file. Because in the column of the integer migration file with a limit of 8, there is bigint . Therefore, you need to cross-check the table definition from PG-admin. If the column is not large, enter the next 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 

Answer 4

bigint is 64-bit and Rails is 32-bit.

3000002000 greater than 2^32 . Therefore, converting it to a 32-bit integer is not performed using NumericValueOutOfRange .