Today at work, a question came up whether one could count on the last
inserted item in a table to have the most recent value for the
created_at timestamp, so I went looking.
- FactoryGirl insert
- Create multiple records
- SQL Insert with date formatting
- Single insert statement, multiple values with
The postgresql documentation for Date / Time Types shows the resolution as 14 digits and 1 microsecond.
I ran a small experiment where I first used
FactoryGirl to insert 50
records. These showed only increasing timestamps showing there’s
enough resolution at a microsecond to distinguish insertion order
accurately building and inserting objects with FG.
Create multiple records
Next I tried it by creating an array of new values and passing it to
.create method of the Model. This also showed plenty of
resolution where none of them would be considdred a “tie” when
SQL Insert with date formatting
One of the things Rails is poor at is mass uploading. In the past I’ve
used techniques to get around this. So giving that a go, I built a set
of insert statements that used rails’s
DateTime.now to generate the
Then made that into an SQL statement.
This gave the same result of increasing timestamps.
Forgetting the milliseconds: Ooops
Note that if the milliseconds are not included in the
method above, then it is quite possible to insert timestamps that look
Next, I build an SQL query using the
now() method for each new
Here we can see that every value inserted got the same
timestamp. Conclusion that Postgresql calculates
now() once in
compiling the insert statement.
Single insert statement, multiple values with
Final check, using a single
insert into statement with
(only for 100 items.)
This gives the same result as the previous, as expected.
So it seems as though one might not be able to count on inserted
values having an increasing
created_at value, but this won’t come up
very often in a Rails app unless you’re doing mass-inserts like the