up vote 26 down vote favorite
12

I've been migrating some of my mySQL queries to postgreSQL to use Heroku... most of my queries work fine, but I keep having a similar recurring error when I use group by:

ERROR:  column "XYZ" must appear in the GROUP BY clause or be used in an aggregate function

Could someone could tell me what I'm doing wrong?

MySQL which works 100%:

SELECT `availables`.* FROM `availables` INNER JOIN `rooms` ON `rooms`.id = `availables`.room_id WHERE (rooms.hotel_id = 5056 AND availables.bookdate BETWEEN '2009-11-22' AND '2009-11-24') GROUP BY availables.bookdate ORDER BY availables.updated_at

PostgreSQL error:

ActiveRecord::StatementInvalid: PGError: ERROR:  column "availables.id" must appear in the GROUP BY clause or be used in an aggregate function
: SELECT "availables".* FROM "availables"   INNER JOIN "rooms" ON "rooms".id = "availables".room_id  WHERE (rooms.hotel_id = 5056 AND availables.bookdate BETWEEN E'2009-10-21' AND E'2009-10-23')  GROUP BY availables.bookdate ORDER BY availables.updated_at

Ruby code generating the SQL:

expiration = Available.find(:all,
    :joins => [ :room ],
    :conditions => [ "rooms.hotel_id = ? AND availables.bookdate BETWEEN ? AND ?", hostel_id, date.to_s, (date+days-1).to_s ],
    :group => 'availables.bookdate',
    :order => 'availables.updated_at')

Expected Output (from working mySQL query):

+-----+-------+-------+------------+---------+---------------+---------------+
| id  | price | spots | bookdate   | room_id | created_at    | updated_at    |
+-----+-------+-------+------------+---------+---------------+---------------+
| 414 | 38.0  | 1     | 2009-11-22 | 1762    | 2009-11-20... | 2009-11-20... |
| 415 | 38.0  | 1     | 2009-11-23 | 1762    | 2009-11-20... | 2009-11-20... |
| 416 | 38.0  | 2     | 2009-11-24 | 1762    | 2009-11-20... | 2009-11-20... |
+-----+-------+-------+------------+---------+---------------+---------------+
3 rows in set
share|improve this question
sooo... would I be better served using the distinct function on bookdate? If I did that, would I still need the group by clause? – holden Nov 20 '09 at 10:07
DISTINCT is slower than GROUP BY. So you should be careful and prefer a GROUP BY solution if it is possible. – Franz Nov 20 '09 at 11:10

8 Answers

up vote 52 down vote

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.

Note that here, it's not an aggregate I'm computing. So GROUP BY actually makes no sense. DISTINCT ON makes a lot more sense.

Rails is married to MySQL, so I'm not surprised that it generates SQL that doesn't work in postgres.

share|improve this answer
4  
Adding to this, though, Postgres 9.1 allows to not list all columns if their table's primary key is part of the group by clause. – Denis Jun 2 '11 at 15:59
According to this article "Debunking GROUP BY myths", it has nothing to do with "non standards compliant GROUP BY". – Rafa Aug 8 '12 at 16:26
1  
According to this article, MySQL's GROUP BY is still noncompliant to both versions of the standard, because it does not verify if the extra columns in the selectlist are dependent on the group by columns. It will output incorrect data without warning (but can serve useful purposes too). PG 9.1 assumes that incuding the PK of a table means all other columns are dependent, which is right. This does not cover the standard 100% (other correct queries may be flagged as errors) but covers most use cases without returning incorrect results... – peufeu Aug 18 '12 at 9:15
up vote 10 down vote

PostgreSQL is more SQL compliant than MySQL. All fields - except computed field with aggregation function - in the output must be present in the GROUP BY clause.

share|improve this answer
up vote 4 down vote

Correct, the solution to fixing this is to use :select and to select each field that you wish to decorate the resulting object with and group by them.

Nasty - but it is how group by should work as opposed to how MySQL works with it by guessing what you mean if you don't stick fields in your group by.

share|improve this answer
I suppose MySQL has spoiled me, or ruined me, whichever adjective you prefer, so there's no better way? Ie. throwing in an aggregate function such as MAX(bookdate) or DISTINCT which i was told above is much slower? – holden Nov 20 '09 at 11:33
I would stick with group by - but tread carefully, especially since you have to manually select which fields you want to decorate the object with. Also writing the manual select with group by is a more database agnostic approach, considering that MSSQL (if you are unfortunate enough to have to use it) and Oracle will also complain in a similar fashion. – Omar Qureshi Nov 21 '09 at 0:15
DISTINCT doesn't necessarily mean slower. – nos Nov 22 '09 at 1:40
up vote 3 down vote

MySQL's GROUP BY can be used without an aggregate function (which is contrary to the SQL standard), and returns the first row in the group (I don't know based on what criteria), while PostgreSQL must have an aggregate function (MAX, SUM, etc) on the column, on which the GROUP BY clause is issued.

share|improve this answer
up vote 3 down vote

If I remember correctly, in PostgreSQL you have to add every column you fetch from the table where the GROUP BY clause applies to the GROUP BY clause.

share|improve this answer
up vote 2 down vote

Not the prettiest solution, but changing the group parameter to output every column in model works in PostgreSQL:

expiration = Available.find(:all,
:joins => [ :room ],
:conditions => [ "rooms.hotel_id = ? AND availables.bookdate BETWEEN ? AND ?", hostel_id, date.to_s, (date+days-1).to_s ],
:group => Available.column_names.collect{|col| "availables.#{col}"},
:order => 'availables.updated_at')
share|improve this answer
up vote 1 down vote

According to MySQL's "Debuking GROUP BY Myths" http://dev.mysql.com/tech-resources/articles/debunking-group-by-myths.html. SQL (2003 version of the standard) doesn't requires columns referenced in the SELECT list of a query to also appear in the GROUP BY clause.

share|improve this answer
up vote 0 down vote

I think that .uniq [1] will solve your problem. 

[1] Available.select('...').uniq

Take a look at http://guides.rubyonrails.org/active_record_querying.html#selecting-specific-fields

share|improve this answer

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.