Rails with Postgres data is returned out of order

Question

I've just transitioned my app from MySQL to Postgres. Previously, a request for .all returned all the rows in id order. On Postgres the rows are returned out of order. Likewise,

Person.first

used to return the record with id 1, now it sometimes returns another record.

If I add an order clause like this:

Person.order("id").first

The query succeeds and returns the first row. Is this expected behaviour?

"Make me feel icky" to have a known ordering (removing a bug from your app) vs having random ordering (and keeping the bug)??? — Richard Huxton
– Richard Huxton, Commented Feb 7, 2013 at 18:45
MySQL orders by id by default. This is a sensible assumption. — superluminary
– superluminary, Commented Feb 8, 2013 at 10:50
I think it's InnoDB that orders by primary-key as a side-effect of its implementation. Other storage engines behave differently and I'm not sure Oracle issue any guarantees about ordering remaining the same in future. The SQL standards are pretty clear about result-set ordering being undefined unless you explicitly request one. — Richard Huxton
– Richard Huxton, Commented Feb 8, 2013 at 11:25
OK, fair enough. If I modify the question would you reverse the downvote? — superluminary
– superluminary, Commented Feb 8, 2013 at 13:09

Community · Accepted Answer · 2017-05-23 12:07:22Z

4

this post answers your question:

I don't think ordering by ID is guaranteed by default, since I believe it's up to the database how a non-ordered query gets returned. You can make it be ordered so by defining a default scope at the top of your model like so:
default_scope order('id ASC')
Then calling Model.all will be equivalent to calling Model.order('id ASC').

edited May 23, 2017 at 12:07

CommunityBot

11 silver badge

answered Feb 7, 2013 at 15:50

blamattina

3062 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

superluminary Over a year ago

A default scope will override other scopes later in the query chain. It's not my preferred solution.

blamattina Over a year ago

an alternative is to create a scope so you can use Person.by_id.first

superluminary Over a year ago

I guess that's the way to go then. Thanks

steve klein Over a year ago

If you choose to use this in Rail 4.2.0 (and perhaps earlier) you will need the scope to be in a block like so default_scope { order('id ASC') }.

lulalala Over a year ago

@superluminary is there some web page about this?

Collectives™ on Stack Overflow

Rails with Postgres data is returned out of order

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related