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, Feb 07 '13 at 18:45
MySQL orders by id by default. This is a sensible assumption. — superluminary, Feb 08 '13 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, Feb 08 '13 at 11:25
OK, fair enough. If I modify the question would you reverse the downvote? — superluminary, Feb 08 '13 at 13:09

score 4 · Accepted Answer · edited May 23 '17 at 12:07

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 '17 at 12:07

Community

1
1

answered Feb 07 '13 at 15:50

blamattina

306
2
7

1

A default scope will override other scopes later in the query chain. It's not my preferred solution. – superluminary Feb 07 '13 at 15:56
an alternative is to create a scope so you can use Person.by_id.first – blamattina Feb 07 '13 at 15:58
I guess that's the way to go then. Thanks – superluminary Feb 07 '13 at 16:05
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') }`. – steve klein Jun 02 '15 at 13:38
@superluminary is there some web page about this? – lulalala Jul 19 '15 at 06:03

Rails with Postgres data is returned out of order

1 Answers1

Linked