Postgres: Error using GROUP BY and ORDER (on heroku)

Question

I am trying to solve my heroku problem which it seems to have problem of

We're sorry, but something went wrong. We've been notified about this issue and we'll take a look at it shortly.

Is there any mistake I have and how to overcome it? How can I interpret these Heroku logs?

ActionView::Template::Error (PGError: ERROR:  column "microposts.created_at" must appear     in the GROUP BY clause or be used in an aggregate function
2011-11-14T17:33:07+00:00 app[web.1]: : SELECT category FROM "microposts" GROUP BY category ORDER BY microposts.created_at DESC):
2011-11-14T17:33:07+00:00 app[web.1]:     2: <% @categories= Micropost.select("category").group("category")%>
2011-11-14T17:33:07+00:00 app[web.1]:     3: <% unless @categories.nil? %>
2011-11-14T17:33:07+00:00 app[web.1]:     4: 
2011-11-14T17:33:07+00:00 app[web.1]:     5: <ul><% @categories.each do |category| %>
2011-11-14T17:33:07+00:00 app[web.1]:     6: <li><%= link_to category.category, :controller =>"microposts", :category => category.category, :method => 'category_list' %></li>
2011-11-14T17:33:07+00:00 app[web.1]:     7: <% end %>
2011-11-14T17:33:07+00:00 app[web.1]:     8: </ul>

micropost model (New added)

 class Micropost < ActiveRecord::Base
belongs_to :users
default_scope :order => 'microposts.created_at DESC'


attr_accessible :title,:content,:category

validates :user_id, :presence => true
validates :title,    :presence => true,
                     :length => {:maximum =>500}                            
validates :content,  :presence => true,
                     :length => {:maximum =>3000}                           
validates :category, :presence => true
end

Answer 1

Your immediate problem is that you're producing invalid SQL for PostgreSQL:

SELECT category FROM "microposts" GROUP BY category ORDER BY microposts.created_at DESC

Your ORDER BY doesn't match the rest of your query. You can't use a column in a grouped query unless that column is also grouped or if the column appears in an aggregate function, that's what the error message means. The reason is that PostgreSQL won't know which row's created_at to use when a group of rows are combined by the GROUP BY clause; some databases will just silently pick a row on their own, PostgreSQL prefers to be strict and wants you to remove the ambiguity yourself.

Try specifying the order yourself:

@categories = Micropost.select("category").group("category").order("category")

Another option is to use DISTINCT instead of GROUP BY to avoid duplicates:

@categories = Micropost.select('DISTINCT(category)')

BTW, you really shouldn't be doing that sort thing in a view, you might want to move that to your controller.

Your real problem is that you're developing on top of one database while deploying on another. I'd recommend that you switch your development environment to PostgreSQL 8.3 (if you're deploying to a Heroku shared database) or PostgreSQL 9.0 (if you're deploying to a dedicated database).

Answer 2

Have you been developing your app on MySQL? Heroku doesn't include MySQL. It uses postgreSQL. Look at this question here - PostgreSQL GROUP BY different from MySQL?

I quote -

MySQL's totally non standards compliant GROUP BY can be emulated by Postgres' DISTINCT ON. Consider this :

mysql :

  SELECT a,b,c,d,e FROM table GROUP BY a

This delivers 1 row per value of a (which one, you don't really know). Well actually you can guess, because MySQL doesn't know about hash aggregates, so it will probably use a sort... but it will only sort on a, so the order of the rows could be random. Unless it uses a multicolumn index instead of sorting. Well, anyway, it's not specified by the query.

postgres :

   SELECT DISTINCT ON (a) a,b,c,d,e FROM table ORDER BY a,b,c

This delivers 1 row per value of a, this row will be the first one in the sort according to the ORDER BY specified by the query. Simple.