Translating MySQL query to PostgreSQL

Question

I have a query which works fine in MySQL, however when I try to recreate the same query in PostgreSQL (same DB structure) I just get a syntax error.

MySQL:

SELECT ticket.queue_id, queue.name, article.create_time,
COUNT(article.id)
FROM article
JOIN ticket
JOIN queue
ON article.ticket_id=ticket.id
and ticket.queue_id=queue.id
GROUP BY ticket.queue_id

Postgres:

SELECT ticket.queue_id, queue.name, article.create_time,
sum(article.id) FROM article
JOIN ticket
JOIN queue
ON article.ticket_id=ticket.id
AND ticket.queue_id=queue.id 
GROUP BY ticket.queue_id;

The error:

ERROR: syntax error at or near "GROUP"
LINE 7: GROUP BY ticket.queue_id;
 ^

I'm quite at a loss here, as I can't really pinpoint my error. The same error appears when I leave out the 'sum' statement in the query.

Any help much appreciated.

• Craig Ringer: The syntax error is because of the join, which is invalid. You must write table1 JOIN table2 ON condition. The ON condition is not optional for an INNER JOIN (which JOIN without qualifiers is).

Answer 1

You must group by all of the columns i the select clause:

GROUP BY ticket.queue_id, queue.name, article.create_time

or add aggregate functions such as MAX:

SELECT ticket.queue_id, MAX(queue.name), MAX(article.create_time),
 COUNT(article.id)
FROM article
JOIN ticket
 ON article.ticket_id=ticket.id
JOIN queue
 ON ticket.queue_id=queue.id
GROUP BY ticket.queue_id

I also moved the ON clause.

SQL92 required a full group by, SQL99 loosened this and requires that all columns in the select clause should be functionally dependent of the group by clause. AFAIK, all vendors kept the stricter SQL92 definition.

You can make MySQL more compliant with other vendors by adding ONLY_FULL_GROUP_BY to @@sql_mode. By default MySQL will not complain if you "under specify" the group by, instead it will give you a random row. Example:

create table t (x int, y int, z int);
insert into t (x,y,z) values (1,1,1),(1,2,1);
select x,y,count(*) from t group by x;
+------+------+----------+
| x | y | count(*) |
+------+------+----------+
| 1 | 1 | 2 |
+------+------+----------+
1 row in set (0.00 sec)
set @@sql_mode = 'ONLY_FULL_GROUP_BY'
select x,y,count(*) from t group by x;
ERROR 1055 (42000): 'mysql.t.y' isn't in GROUP BY

There is also a bunch of other settings (PIPES_AS_CONCAT for example) that can be added to @@sql_info to make MySQL more compliant.

• Erwin Brandstetter: Postgres implemented the SQL99 version with version 9.1 (mostly). A PRIMARY KEY in the GROUP BY clause covers the whole table in the SELECT list. (Other functional dependencies like UNIQUE constraints are not covered, though.) Details in this related answer: dba.stackexchange.com/questions/41494/…
• Lennart - Slava Ukraini: Ah, thanks. I did not know that. Most of my answer is a waste then. My next guess would have been the ON clause.
• Erwin Brandstetter: You are still correct about the missing GROUP BY items. The three columns come from three different tables. (The alternative with max() would produce completely different results though.)