# ActiveRecord Integer Saving Problem

**URL:** <https://discuss.rubyonrails.org/t/activerecord-integer-saving-problem/61258>\
**Category:** rubyonrails-talk\
**Created:** [October 12, 2011, 8:18pm UTC](https://discuss.rubyonrails.org/t/activerecord-integer-saving-problem/61258 "2011-10-12T20:18:14Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Chris\_N](https://avatars.discourse-cdn.com/v4/letter/c/c68b51/32.png) [@Chris\_N](https://discuss.rubyonrails.org/u/Chris_N)\
**Post date:** [October 12, 2011, 8:18pm UTC](https://discuss.rubyonrails.org/t/activerecord-integer-saving-problem/61258/1 "2011-10-12T20:18:14Z")

</div>

Using: Ruby 1.9.2, Rails 3.0.9, SQLite3

I am seeing some odd behavior when saving an integer field in activerecord. I have setup a test scenario in the Rails console using the following migration and corresponding model:

class CreateMyobjs \< ActiveRecord::Migration &nbsp;&nbsp;def self.up &nbsp;&nbsp;&nbsp;&nbsp;create\_table :myobjs do |t| &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;t.integer :int, :default =\> 0, :null =\> false

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;t.timestamps &nbsp;&nbsp;&nbsp;&nbsp;end &nbsp;&nbsp;end

&nbsp;&nbsp;def self.down &nbsp;&nbsp;&nbsp;&nbsp;drop\_table :myobjs &nbsp;&nbsp;end end

In the Rails console (line numbers added by me):

1 ruby-1.9.2-p290 :001 \> o = Myobj.new 2 =\> #\<Myobj id: nil, int: 0, created\_at: nil, updated\_at: nil\> 3 ruby-1.9.2-p290 :002 \> o.save 4 =\> true 5 ruby-1.9.2-p290 :003 \> o 6 =\> #\<Myobj id: 1, int: 0, created\_at: "2011-10-12 19:59:17", updated\_at: "2011-10-12 19:59:17"\> 7 ruby-1.9.2-p290 :004 \> o.int = '' 8 =\> "" 9 ruby-1.9.2-p290 :005 \> o 10 =\> #\<Myobj id: 1, int: nil, created\_at: "2011-10-12 19:59:17", updated\_at: "2011-10-12 19:59:17"\> 11 ruby-1.9.2-p290 :006 \> o.save 12 =\> true 13 ruby-1.9.2-p290 :007 \> o 14 =\> #\<Myobj id: 1, int: nil, created\_at: "2011-10-12 19:59:17", updated\_at: "2011-10-12 19:59:17"\> 15 ruby-1.9.2-p290 :008 \> o2 = Myobj.find(1) 16 =\> #\<Myobj id: 1, int: 0, created\_at: "2011-10-12 19:59:17", updated\_at: "2011-10-12 19:59:17"\>

In lines 1-6 I create a new Myobj, save it and verify its attributes, at this point o.int = 0, the default value from the database.

In lines 7-10 I set the value of o.int to '' (blank string), which activerecord translates to nil, since it is an integer field.

Lines 11-12 successfully saves o with o.int set to nil, this save \*should\* raise an InvalidStatement exception from the database, but it does not!

Lines 13-14 verify's that the apparently saved o object still thinks the int field is nil.

Line 15-16 lookups up the record from the database and shows that the int field is not actually nil, but rather is still 0. It is apparent that the original o object did not save the int attribute properly to the database.

Trying to do the same thing when o.int starts ut as non-zero results in the following:

17 ruby-1.9.2-p290 :009 \> o.int = 3 18 =\> 3 19 ruby-1.9.2-p290 :010 \> o.save 20 =\> true 21 ruby-1.9.2-p290 :011 \> o 22 =\> #\<Myobj id: 1, int: 3, created\_at: "2011-10-12 19:59:17", updated\_at: "2011-10-12 20:08:00"\> 23 ruby-1.9.2-p290 :012 \> o.int = nil 24 =\> nil 25 ruby-1.9.2-p290 :013 \> o.save 26 ActiveRecord::StatementInvalid: SQLite3::ConstraintException: myobjs.int may not be NULL: UPDATE "myobjs" SET "int" = NULL, "updated\_at" = '2011-10-12 20:08:13.550661' WHERE "myobjs"."id" = 1 ...

I wont give a step-by-step description of this one, but as you can see the expected database exception is raised.

From these tests it appears that activerecord's save method is not updating integer fields when they change from 0 to nil. I think it is likely that this is because it is internally coercing the value of the integer field using to\_i, and of course nil.to\_i == 0.

Can anyone else confirm this behavior or think of a good reason why it would be like this?

Thanks, Chris

---

<div class="post-metadata">

**Author:** ![Philip\_Hallstrom](https://avatars.discourse-cdn.com/v4/letter/p/bbe5ce/32.png) [@Philip\_Hallstrom](https://discuss.rubyonrails.org/u/Philip_Hallstrom)\
**Post date:** [October 12, 2011, 9:03pm UTC](https://discuss.rubyonrails.org/t/activerecord-integer-saving-problem/61258/2 "2011-10-12T21:03:40Z")

</div>

> Using: Ruby 1.9.2, Rails 3.0.9, SQLite3
> 
> I am seeing some odd behavior when saving an integer field in activerecord. I have setup a test scenario in the Rails console using the following migration and corresponding model:
> 
> class CreateMyobjs \< ActiveRecord::Migration def self.up &nbsp;&nbsp;&nbsp;create\_table :myobjs do |t| &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;t.integer :int, :default =\> 0, :null =\> false
> 
> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;t.timestamps &nbsp;&nbsp;&nbsp;end end
> 
> def self.down &nbsp;&nbsp;&nbsp;drop\_table :myobjs end end
> 
> In the Rails console (line numbers added by me):
> 
> 1 ruby-1.9.2-p290 :001 \> o = Myobj.new 2 =\> #\<Myobj id: nil, int: 0, created\_at: nil, updated\_at: nil\> 3 ruby-1.9.2-p290 :002 \> o.save 4 =\> true 5 ruby-1.9.2-p290 :003 \> o 6 =\> #\<Myobj id: 1, int: 0, created\_at: "2011-10-12 19:59:17", updated\_at: "2011-10-12 19:59:17"\> 7 ruby-1.9.2-p290 :004 \> o.int = '' 8 =\> "" 9 ruby-1.9.2-p290 :005 \> o 10 =\> #\<Myobj id: 1, int: nil, created\_at: "2011-10-12 19:59:17", updated\_at: "2011-10-12 19:59:17"\> 11 ruby-1.9.2-p290 :006 \> o.save 12 =\> true 13 ruby-1.9.2-p290 :007 \> o 14 =\> #\<Myobj id: 1, int: nil, created\_at: "2011-10-12 19:59:17", updated\_at: "2011-10-12 19:59:17"\> 15 ruby-1.9.2-p290 :008 \> o2 = Myobj.find(1) 16 =\> #\<Myobj id: 1, int: 0, created\_at: "2011-10-12 19:59:17", updated\_at: "2011-10-12 19:59:17"\>
> 
> In lines 1-6 I create a new Myobj, save it and verify its attributes, at this point o.int = 0, the default value from the database.
> 
> In lines 7-10 I set the value of o.int to '' (blank string), which activerecord translates to nil, since it is an integer field.
> 
> Lines 11-12 successfully saves o with o.int set to nil, this save \*should\* raise an InvalidStatement exception from the database, but it does not!
> 
> Lines 13-14 verify's that the apparently saved o object still thinks the int field is nil.
> 
> Line 15-16 lookups up the record from the database and shows that the int field is not actually nil, but rather is still 0. It is apparent that the original o object did not save the int attribute properly to the database.
> 
> Trying to do the same thing when o.int starts ut as non-zero results in the following:
> 
> 17 ruby-1.9.2-p290 :009 \> o.int = 3 18 =\> 3 19 ruby-1.9.2-p290 :010 \> o.save 20 =\> true 21 ruby-1.9.2-p290 :011 \> o 22 =\> #\<Myobj id: 1, int: 3, created\_at: "2011-10-12 19:59:17", updated\_at: "2011-10-12 20:08:00"\> 23 ruby-1.9.2-p290 :012 \> o.int = nil 24 =\> nil 25 ruby-1.9.2-p290 :013 \> o.save 26 ActiveRecord::StatementInvalid: SQLite3::ConstraintException: myobjs.int may not be NULL: UPDATE "myobjs" SET "int" = NULL, "updated\_at" = '2011-10-12 20:08:13.550661' WHERE "myobjs"."id" = 1 ...
> 
> I wont give a step-by-step description of this one, but as you can see the expected database exception is raised.
> 
> From these tests it appears that activerecord's save method is not updating integer fields when they change from 0 to nil. I think it is likely that this is because it is internally coercing the value of the integer field using to\_i, and of course nil.to\_i == 0.

If this is true, why wouldn't your second example also succeed? Since in both you are setting o.int to nil which should then get coerced to zero. Actually you're not doing exactly the same thing.

In the former you are setting o.int to "" and in the latter setting it to nil. So it could be that Rails is calling to\_i on "", but not on nil... hence the error...

And "".to\_i =\> 0

I haven't actually checked the source though, but seems like skipping type coercion on nil fields is a logical thing to do.

---

<div class="post-metadata">

**Author:** ![Peter\_Vandenabeele1](https://avatars.discourse-cdn.com/v4/letter/p/59ef9b/32.png) [@Peter\_Vandenabeele1](https://discuss.rubyonrails.org/u/Peter_Vandenabeele1)\
**Post date:** [October 16, 2011, 6:48pm UTC](https://discuss.rubyonrails.org/t/activerecord-integer-saving-problem/61258/3 "2011-10-16T18:48:02Z")

</div>

I can confirm it (in Rails 3.1.1.rc1).

I believe the cause of the difference is that “o.changed” does not see the

difference between the nil in memory and the 0 in the database in the case

where a NULL is not allowed for that column.

Maybe ‘changed’ should see that difference (between nil and 0) to have

consistent behavior. I have the impression it is not correct that the trying

to save an object with an not allowed nil value for an attribute will behave

differently, dependent on the current state of the object in the database?

`
class AddAgeToUsers < ActiveRecord::Migration
def change
add_column :users, :age, :int, :default => 0, :null => false
end
end
``
049:0> u.age = 0
=> 0
050:0> u.save!
(0.4ms) BEGIN
(0.5ms) UPDATE “users” SET “age” = 0, “updated_at” = ‘2011-10-16 18:33:23.687350’ WHERE “users”.“id” = 2
(1.3ms) COMMIT
=> true
051:0> u.age = ‘’
=> “”
052:0> u.changed
=> 
053:0> u.changes
=> {}
054:0> u.save # not actually saving, so not hitting the database NOT NULL restriction
(0.4ms) BEGIN
(0.2ms) COMMIT
=> true
055:0> ActiveRecord::Base.partial_updates = false # force the save
=> false
056:0> u
=> #<User id: 2, name: “Peter”, created_at: “2011-10-11 10:56:14”, updated_at: “2011-10-16 18:33:23”, age: nil>
057:0> u.reload
User Load (0.7ms) SELECT “users”.* FROM “users” WHERE “users”.“id” = $1 LIMIT 1 [[“id”, 2]]
=> #<User id: 2, name: “Peter”, created_at: “2011-10-11 10:56:14”, updated_at: “2011-10-16 18:33:23”, age: 0>
058:0> u.age = ‘’
=> “”
059:0> u.changes
=> {}
060:0> u.save
(0.4ms) BEGIN
(1.0ms) UPDATE “users” SET “name” = ‘Peter’, “created_at” = ‘2011-10-11 10:56:14.824780’, “updated_at” = ‘2011-10-16 18:35:53.711462’, “age” = NULL WHERE “users”.“id” = 2
PGError: ERROR: null value in column “age” violates not-null constraint
: UPDATE “users” SET “name” = ‘Peter’, “created_at” = ‘2011-10-11 10:56:14.824780’, “updated_at” = ‘2011-10-16 18:35:53.711462’, “age” = NULL WHERE “users”.“id” = 2
(0.3ms) ROLLBACK
ActiveRecord::StatementInvalid: PGError: ERROR: null value in column “age” violates not-null constraint
: UPDATE “users” SET “name” = ‘Peter’, “created_at” = ‘2011-10-11 10:56:14.824780’, “updated_at” = ‘2011-10-16 18:35:53.711462’, “age” = NULL WHERE “users”.“id” = 2
…
`

HTH,

Peter
