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.

Postgres numbers schema

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

Create number image
Stacktrace

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.

it works for me in Rails console, but not with server started — Mikhail Chuprynski, Aug 07 '15 at 13:12
I appreciate this is way old now, but were you using a counter cache? I've been struggling with a similar error and it seems to relate to https://github.com/rails/rails/issues/22602 — Hugh, Dec 12 '16 at 23:50
@Hugh no i wasn't using counter cache. My issue was just the using BigInt as the primary key. — Turgs, Dec 13 '16 at 00:59
I have _exactly_ the same problem almost 2 years later... What solution did you come up with? — RobDil, Mar 09 '17 at 20:34
@ToniTornado i never fixed it. i think i heard there's a gem now thatll do it or it may be that bigint is default in Rails 5... i cant remember because ive not had time to dig into it again — Turgs, Mar 09 '17 at 20:39
Yes, primary keys default to bigint in Rails 5.1... But I'm on Rails 5.0 and I try to use it for an ordinary integer column with big values. Thank you for the quick response :) — RobDil, Mar 09 '17 at 20:57
I also have exactly the same problem on Rails 4.2.9 more than two years later — Josh, Jul 07 '17 at 07:04

score 1 · Answer 1 · edited May 23 '17 at 12:32

1

Try

t.column :number, :bigint

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

edited May 23 '17 at 12:32

Community

1
1

answered Apr 22 '15 at 13:39

Maurice Qch

166
1
4

1

Sorry, didn't change anything. Same error. The problem seems *different* to that mentioned in the question you've referenced. The column in my database *is* creating *correctly* as a BigInt as I mention. However, "on save" of a large number, the Adaptor is throwing an error that the value is out of range. From the Stack Trace screenshot I've included, it appears the Adaptor is thinking the column is a standard Integer rather than a BigInt. – Turgs Apr 24 '15 at 12:07
Could you try to insert a bigint value using a SQL request directly on the database without using active record to see if the problem is not on the database side ? – Maurice Qch Apr 26 '15 at 19:05
1

Inserting the number 71731224865 directly into the database worked fine. `INSERT INTO numbers VALUES (71731224865,NULL,NULL,1,NULL,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP);` – Turgs Apr 27 '15 at 22:56
I've also tried recreating the column as a DECIMAL(11,0) and that ALSO fails with the same error. – Turgs Apr 27 '15 at 23:39

score 0 · Answer 2 · answered Feb 15 '18 at 07:19

0

create_table :numbers do |t|
  t.bigint :mynumber

  t.timestamps
end

i tried with the above migration it works for the following length of number

7173122486511111111

i think there is a problem with the version please update your postgres/rails version

answered Feb 15 '18 at 07:19

chinna2580

2,065
2
16
30

2

What version of Rails are you using? This was a Rails 4 problem, it's fixed in Rails 5. Rails 5 wasn't released 3 years ago at the time of the question. – Turgs Feb 16 '18 at 02:30

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

2 Answers2