Q:

Transform query that works in SQLite into something that works in Postgres

Message.order("created_at DESC").where(user_id: current_user.id).group(:sender_id, :receiver_id).count

Works with my dev environment SQLite3 for a Rails 3.2 app but fails when pushed to Heroku using Postgres with this error: 
PG::GroupingError: ERROR: column "messages.created_at" must appear in the GROUP BY clause or be used in an aggregate function

It seems it wants :created_at in the query.  Unable to come up with anything.  Any ideas? 
Thanks 

A:

PostgreSQL can't figure out how to order by your created_at.  
Suppose that you find 2 groups of (:sender_id, :receiver_id), for instance [1, 2] and
[1, 3]. 
Now suppose that in the first group you have 2 messages, one from 1 day ago and one from 1 minute ago.  And let's say you have 1 message in the second group from 12 hours ago. 
Then ORDER BY created_at DESC doesn't make any sense: do you take the message from 1 day ago as the created_at of the first group (hence the first group appears after the second one), or the one from 1 minute ago (in which case the first group now appears first)? 
That's why PostgreSQL says that you need to either have created_at in the GROUP BY (in which case you now have 3 different group, as the first one is now split in two), or you need to use an aggregate function to transform multiple values of created_at into a single one. 

This will run (I don't know what you expect the results to be, you might not want to use MAX(created_at) !  You can find a list of PostgreSQL's aggregate functions here) :
Message.order("MAX(created_at) DESC")
       .where(user_id: current_user.id)
       .group(:sender_id, :receiver_id)
       .count

