Alex Rivera | Logout

Converting MySQL select to PostgreSQL

Asked 2009-06-30T07:07:28.497
14

I have this query which works correctly in MySQL. More background on it here.

SELECT c.*, SUM(ABS(v.vote)) AS score
FROM categories c,items i, votes v
    WHERE c.id = i.category_id
    AND i.id = v.voteable_id
    AND v.created_at > '#{1.week.ago}'
GROUP BY c.id
ORDER BY score DESC LIMIT 8;

I tried running it in PostgreSQL, and it failed with this error message.

PGError: ERROR: column "c.name" must appear in the GROUP BY clause or be used in an aggregate function

I wasn't sure what this meant, so I tried changing "c.id" to "c.name" in the group by clause (both work in MySQL the same, assuming the name of an item is unique).

However this just produced another similar error

PGError: ERROR: column "c.id" must appear in the GROUP BY clause or be used in an aggregate function

How can this problem be resolved?

mysql sql postgresql
Edit
Report

1 Answer

2

If you change your statement to this it should work:

SELECT c.id, SUM(ABS(v.vote)) AS score
FROM categories c,items i, votes v
  WHERE c.id = i.category_id
  AND i.id = v.voteable_id
  AND v.created_at > '#{1.week.ago}'
GROUP BY c.id
ORDER BY score DESC LIMIT 8;

I'm not sure what MySQL gives as a result, but to give you a very small example of why this does not work in PostgreSQL, take a look at the following categories table:

id | name
---|-----
 1 | ABC
 1 | DEF

You group by id, so each row in the result should contain just one id. If you select name also, without grouping by it, what should be shown in the result for name?

It can be either ABC or DEF but the database engine can not really decide that for you (although MySQL apparently does).

answered 2009-06-30T07:16:32.037

Your Answer