SQL problems when migrating from MySQL to PostgreSQL

Question

I have a Ruby on Rails 2.3.x application that I'm trying to migrate from my own VPS to Heroku, including porting from SQLite (development) and MySQL (production) to Postgres.

This is a typical Rails call I'm using:

spots = Spot.paginate(:all, :include => [:thing, :user, :store, {:thing => :tags}, {:thing => :brand}], :group => :thing_id, :order => order, :conditions => conditions, :page => page, :per_page => per_page)

Question 1: I get a lot of errors like PG::Error: ERROR: column "spots.id" must appear in the GROUP BY clause or be used in an aggregate function. SQLite/MySQL was evidently more forgiving here. Of course I can easily fix these by adding the specified fields to my :group parameter, but I feel I'm messing up my code. Is there a better way?

Question 2: If I throw in all the GROUP BY columns that Postgres is missing I end up with the following statement (only :group has changed):

spots = Spot.paginate(:all, :include => [:thing, :user, :store, {:thing => :tags}, {:thing => :brand}], :group => 'thing_id,things.id,users.id,spots.id', :order => order, :conditions => conditions, :page => page, :per_page => per_page)

This in turn produces the following SQL code:

SELECT * FROM (SELECT DISTINCT ON ("spots".id) "spots".id, spots.created_at AS alias_0 FROM "spots"  
LEFT OUTER JOIN "things" ON "things".id = "spots".thing_id 
WHERE (spots.recommended_to_user_id = 1 OR spots.user_id IN (1) OR things.is_featured = 't')  
GROUP BY thing_id,things.id,users.id,spots.id) AS id_list 
ORDER BY id_list.alias_0 DESC LIMIT 16 OFFSET 0;

...which produces the error PG::Error: ERROR: missing FROM-clause entry for table "users". How can I solve this?

Answer 1

Question 1:

...Is there a better way?

Yes. Since PostgreSQL 9.1 the primary key of a table logically covers all columns of a table in the GROUP BY clause. I quote the release notes for version 9.1:

Allow non-GROUP BY columns in the query target list when the primary key is specified in the GROUP BY clause (Peter Eisentraut)

Question 2:

The following statement ... produces the error

PG::Error: ERROR: missing FROM-clause entry for table "users"

How can I solve this?

First (as always!), I formatted your query to make it easier to understand. The culprit has bold emphasis:

SELECT *
FROM  (
   SELECT DISTINCT ON (spots.id)
          spots.id, spots.created_at AS alias_0
   FROM   spots  
   LEFT   JOIN things ON things.id = spots.thing_id 
   WHERE (spots.recommended_to_user_id = 1 OR
          spots.user_id IN (1) OR
          things.is_featured = 't')  
   GROUP  BY thing_id, things.id, users.id, spots.id
   ) id_list 
ORDER  BY id_list.alias_0 DESC
LIMIT  16
OFFSET 0;

It's all obvious now, right?
Well, not all of it. There is a lot more. DISTINCT ON and GROUP BY in the same query for one, which has its uses, but not here. Radically simplify to:

SELECT s.id, s.created_at AS alias_0
FROM   spots s
WHERE  s.recommended_to_user_id = 1 OR
       s.user_id = 1 OR
       EXISTS (
          SELECT 1 FROM things t
          WHERE  t.id = s.thing_id
          AND    t.is_featured = 't')
ORDER  BY s.created_at DESC
LIMIT  16;

The EXISTS semi-join avoids the later need to GROUP BY a priori. This should be much faster (besides being correct) - if my assumptions about the missing table definitions hold.