Getting Error Indicating Number Is "Out of Range for Activerecord::Type::Integer with Limit 4" When Attempting to Save Large(Ish) Integer Value

What datatype to use for Facebook user id in Rails and PostgreSQL

You can use :limit => 8 on your integer column to get a bigint. For example:

class Pancakes < ActiveRecord::Migration
    def change
        create_table :pancakes do |t|
            t.integer :c, :limit => 8
        end
    end
end

And then, from psql:

=> \d pancakes
                         Table "public.pancakes"
 Column |  Type   |                       Modifiers                       
--------+---------+-------------------------------------------------------
 id     | integer | not null default nextval('pancakes_id_seq'::regclass)
 c      | bigint  | not null
Indexes:
    "pancakes_pkey" PRIMARY KEY, btree (id)

And there's your eight byte bigint column.

You could also use a string for the Facebook ID. You're not doing any arithmetic on the IDs so they're really just opaque bags of bits that happen to look like large integers, strings will sort and compare just fine so they might be the best option. There would be some storage and access overhead due to the increased size of a string over the integer but it probably wouldn't be enough to make any noticeable difference.

Never use a double for something that needs to be exact. You'd probably be fine (except for the trailing .0 of course) in this case because you'd have 52 bits of mantissa and that means that the double would act like a 52 bit integer until your values got large enough to require the exponent. Even so, using double for this would be an awful idea and an abuse of the type system.

ActiveRecord create! with Postgresql saves Boolean as 't', but updates to 1/0 after update/save

You probably have value specified as integer in you migration. It should be changed to boolean.

ActiveRecord default field implementations

You should look at this link:
http://www.postgresql.org/docs/8.4/static/datatype-character.html

It basically says you can have virtually unlimited space.

For SQLite:
http://www.sqlite.org/faq.html#q9

SQLITE_INTEGER value bytes

When SQLite steps into a record, all integer values are expanded to 64 bits.

sqlite3_column_int() returns the lower 32 bits of that value without checking for overflows.

When you call sqlite3_column_bytes(), SQLite will convert the value to text, and return the number of characters.

You cannot know how large an integer value is before reading it.
Afterwards, you can check the list in the record format documentation for the smallest possible format for that value, but if you want to be sure that integer values are never truncated to 32 bits, you have to always use sqlite3_column_int64(), or ensure that large values get never written to the DB in the first place (if that is possible).

---

Related Topics