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?

Erwin Brandstetter · Accepted Answer · 2012-11-10 04:53:50Z

4

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.

edited Nov 10, 2012 at 4:53

answered Nov 10, 2012 at 4:28

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

2 Comments

Tom Söderlund Over a year ago

Thanks, but do I need to recode all my queries to pure SQL, or can I still use Spot.paginate?

Erwin Brandstetter Over a year ago

@TomSöderlund: I am no expert with Ruby, don't know about spot.paginate. The new query is much simpler than the old one. EXISTS is very basic standard SQL ...

Tom Söderlund · Accepted Answer · 2013-01-02 15:33:56Z

0

Going the "pure SQL" route opened up a can of worms for me, so I tried keeping the will_paginate gem and tweak the Spot.paginate parameters instead. The :joins parameter turned out to be very helpful.

This is currently working for me:

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

answered Jan 2, 2013 at 15:33

Tom Söderlund

4,7874 gold badges52 silver badges74 bronze badges

Collectives™ on Stack Overflow

SQL problems when migrating from MySQL to PostgreSQL

2 Answers 2

Question 1:

Question 2:

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Question 1:

Question 2:

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related