Integer out of range on Postgres DB

Question

Simple rails app using Postgres DB, getting 'integer out of range' error when trying to insert 2176968859. Should be an easy fix to the migrations, but I'm not sure. Right now I've got...

create_table :targets do |t| t.integer :tid ...

Answer 1

What's the question? You are overflowing. Use a bigint if you need numbers that big.

http://www.postgresql.org/docs/8.3/interactive/datatype-numeric.html

Answer 2

Note the range of allowed values for the integer type in http://www.postgresql.org/docs/8.3/interactive/datatype-numeric.html. I think you are going to have to use a bigint, decimal, or double precision.

Answer 3

PostgreSQL integers are signed, there is no unsigned datatype - I bet that's your problem.

If you need larger values, use bigint. If bigint also isn't enough, use numeric - but use bigint rather than numeric unless you need the larger size or decimals, since it's much faster.

Answer 4

Here's the magic incantation in your migration when you declare the column:

create_table :example do |t|
  t.integer :field, :limit => 8
end

The :limit => 8 is the magic in this case as postgres only does signed 4-byte integers when you just say integer. This uses 8-byte signed integers.