PostgreSQL grouping error in a query after moving from MySQL

Question

I've moved to PostgreSQL from MySQL, but got strange problem. My query worked perfect in MySQL.

SELECT MIN("events"."schedule") AS schedule 
FROM "events"  
WHERE ("events"."state" IN (1)) AND ("events"."schedule" >= '2014-07-01') 
GROUP BY EXTRACT(YEAR FROM "events"."schedule"), 
EXTRACT(MONTH FROM "events"."schedule")  
ORDER BY "events"."schedule" DESC

PostgreSQL gived me this error:

ERROR:  column "events.schedule" must appear in the GROUP BY clause or be used in an aggregate function
LINE 6: ORDER BY "events"."schedule" DESC
             ^

********** Error **********

ERROR: column "events.schedule" must appear in the GROUP BY clause or be used in an aggregate function
SQL state: 42803
Character: 238

How should I rewrite this query to have "events.schedule" in GROUP BY clause when I group data by two columns at once?

possible duplicate of [Converting MySQL select to PostgreSQL](http://stackoverflow.com/questions/1062158/converting-mysql-select-to-postgresql) — PM 77-1, Jul 25 '14 at 23:50

score 1 · Accepted Answer · answered Jul 25 '14 at 23:54

1

If you want to order by the resulting column (the min(schedule)) then you can use the following:

SELECT MIN("events"."schedule") AS schedule
  FROM "events"
 WHERE "events"."state" IN (1)
   AND "events"."schedule" >= '2014-07-01'
 GROUP BY EXTRACT(YEAR FROM "events"."schedule"),
          EXTRACT(MONTH FROM "events"."schedule")
 ORDER BY 1 desc

The reason you're getting an error is because you're trying to order by the schedule column itself, not the aggregated min(schedule)

answered Jul 25 '14 at 23:54

Brian DeMilia

13,103
1
23
33

Right :) I've just found the answer myself before read your answer: `ORDER BY schedule DESC` fixes the problem. Now my taks is to find out how to make ActiveRecord to generate needed query (already done while writing this comment). – kovpack Jul 25 '14 at 23:58

score 1 · Answer 2 · answered Jul 26 '14 at 03:26

You can also simplify quite a bit:

SELECT MIN(schedule) AS schedule 
FROM   events  
WHERE  state = 1
AND    schedule >= '2014-07-01'
GROUP  BY date_trunc('month', schedule)
ORDER  BY 1 DESC;

In particular, replace the two EXTRACT calls with a single date_trunc(). Cheaper.

score 0 · Answer 3 · answered Jul 25 '14 at 23:51

0

Your original query doesn't really make any sense, so PostgreSQL is right to reject it. I'm not sure why MySQL accepted it or what kind of ordering it's actually doing.

If your goal is to order based on the output schedule column you can do that by referring to the SELECT alias directly, not events.schedule:

SELECT MIN("events"."schedule") AS schedule 
FROM "events"  
WHERE ("events"."state" IN (1)) AND ("events"."schedule" >= '2014-07-01') 
GROUP BY EXTRACT(YEAR FROM "events"."schedule"), 
EXTRACT(MONTH FROM "events"."schedule")
ORDER BY schedule DESC

Otherwise, you need to clarify your question to indicate how you want the result sorted, possibly by supplying some example data.

answered Jul 25 '14 at 23:51

cdhowie

158,093
24
286
300

Actually, my query makes enough sense for my taks. I do no need to select ids or any other data. I just need to extract ONLY dates. But it seems I've found where is the problem in my query: I use `as schedule`, so it runs ok when I add `ORDER BY schedule DESC` instead of `ORDER BY "events"."schedule" DESC`. Query is generated by ActiveRecord, so now I have no idea how to make it generate query in other way. However, this is another question. – kovpack Jul 25 '14 at 23:55
@kovpack What I mean is that the `ORDER BY` clause in your question doesn't make sense -- you can't reasonably order the output by that column, because that column is being aggregated. Let's say you have an `INT` column with four records with values 1, 2, 3, and 4, and through some logic you aggregate the 1 and 4 values into one row, and the 2 and 3 values into another. What would you expect to happen if you order on that column? It's nonsense, which is why PostgreSQL rejects the query. – cdhowie Jul 25 '14 at 23:57
`"events"."schedule" >= '2014-07-01'` - this is not `int`, so yes, I can group and order. In my case I group by each month and need order to select the earliest date for every month that is in the table. – kovpack Jul 26 '14 at 00:03
@kovpack The type of the column doesn't really matter. My point is that if you try to order on a column you are aggregating, that request doesn't make any sense, because trying to order rows aggregating other rows by the rows being aggregated is nonsensical. That's why PostgreSQL gave the error message. – cdhowie Jul 26 '14 at 05:18

PostgreSQL grouping error in a query after moving from MySQL

3 Answers3