0

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.

  • 2
    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). – Craig Ringer Aug 29 '14 at 0:18
2

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.

| improve this answer | |
  • 2
    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/… – Erwin Brandstetter Aug 29 '14 at 2:38
  • Ah, thanks. I did not know that. Most of my answer is a waste then. My next guess would have been the ON clause. – Lennart Aug 29 '14 at 3:47
  • 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.) – Erwin Brandstetter Aug 29 '14 at 4:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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