Postgres Rails Adapter is thinking Bigint/Varchar is Integer - causes exception when saving

Question

Update: This is regarding Rails 4.x and is not an issue in Rails 5.

I have a Number model which a number field that is a bigint. The schema.rb file correctly creates the table structure in the database.

However using the app, when I go to create a new Number, I get an error saying:

RangeError at /numbers
71731224865 is out of range for ActiveRecord::ConnectionAdapters::PostgreSQL::OID::Integer with limit 4

Why is this number field still being treated as a standard integer rather than a bigint?

This seems different to the other "out of range" errors on StackOverflow as they all seem to not be able to get the field defined as bigint in the database in the first place. However, I've got that.. this seems to be "on save" the adapter is freaking out.

Here's the create_table as it appears in schema.rb:

create_table "numbers", id: false, force: :cascade do |t|
  t.bigint   "number",           null: false, index: {name: "index_numbers_on_number", unique: true}
  t.string   "formatted_number"
  t.text     "description"
  t.integer  "user_id",          null: false, index: {name: "index_numbers_on_userid"}, foreign_key: {name: 'fk_numbers_user_id'}
  t.datetime "created_at",       null: false
  t.datetime "updated_at",       null: false
end

Update 1: I've recreated the column as a DECIMAL(11,0) in the hopes that'd be a temporary workaround, however that failed too with the same error! Maybe the only option I have as a workaround is VARCHAR(11).

Update 2: Ok. Something weird is going on. I've defined the field now as VARCHAR(11) so I can keep progressing with work... but that fails with the same error too. What the?

Update 3: Could it be because the number field in the numbers table is the Primary Key? I'm not using a id as the key, I've overridden it. Not that I'm trying to use the field as VARCHAR, it makes no sense why the PostgreSQLAdaptor is still showing ... is out of range for ActiveRecord::ConnectionAdapters::PostgreSQL::OID::Integer with limit 4 error.

Here's my Number model if it helps:

class Number < AbstractModel
  belongs_to :user
  has_many :extensions

  self.primary_key = 'number'
  validates :number, numericality: { only_integer: true, greater_than_or_equal_to: 611, less_than_or_equal_to: 61999999999 }, presence: true, uniqueness: true
  validates :user_id, presence: true
end

Update 4: Using the Rails Console to see what data type it thinks the columns is, and it says INTEGER! Grr. sql_type does return BIGINT though. What the?

Loading development environment (Rails 4.2.1)
irb(main):001:0> Number.column_for_attribute('number').type
  => :integer
irb(main):002:0> Number.column_for_attribute('number').sql_type
  => "bigint"
irb(main):003:0> quit

Making sure the DB is still setup as expected:

[turgs@web123 myapp]$ psql -h 127.0.0.1 -p 5432
psql (9.1.15)

db=> \d numbers
                   Table "public.numbers"
      Column      |            Type             | Modifiers 
------------------+-----------------------------+-----------
 number           | bigint                      | not null
 formatted_number | character varying           | 
 description      | text                        | 
 user_id          | integer                     | not null
 max_extn_length  | integer                     | 
 created_at       | timestamp without time zone | not null
 updated_at       | timestamp without time zone | not null
Indexes:
    "index_numbers_on_number" UNIQUE, btree (number)
    "index_numbers_on_userid" btree (user_id)
Referenced by:
    TABLE "extensions" CONSTRAINT "fk_extensions_number_id" FOREIGN KEY (number_id) REFERENCES numbers(number)

db=> 

Update 5: Yes.... another update! This time I thought I'd fall on my sword and try what everyone uses in the other posts where they can't get the BIGINT to create in the database in the first place. So, I changed by schema.rb to:

    create_table "numbers", id: false, force: :cascade do |t|
  t.integer  "number", limit: 8, null: false, index: {name: "index_numbers_on_number", unique: true}
  t.string   "formatted_number"
  t.text     "description"
  t.integer  "user_id", null: false, index: {name: "index_numbers_on_userid"}, foreign_key: {name: 'fk_numbers_user_id'}
  t.integer  "max_extn_length"
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
end

Note the t.integer "number", limit: 8. Surprisingly, that DID create a bigint field in the database still. My hopes were high. Unfortunately, no cigar. Same error when saving value.

Answer 1

Try

t.column :number, :bigint

See : Rails Migration: Bigint on PostgreSQL seems to be failing?