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?
Postgres offers a wider variety of data types than MySQL. If your application deals with any of the unique data types it has available, or unstructured data, PostgreSQL may be a better pick. If you're using only basic character and numeric data types, both databases will suit you.
Under the hood, MySQL FDW (mysql_fdw) facilitates the use of PostgreSQL server as a client for MySQL Server, which means it can then fetch data from the MySQL database as a client.
Both MySQL and PostgreSQL databases support stored procedures, but MySQL only supports standard SQL syntaxes, while PostgreSQL supports very advanced procedures. Stored Procedures are implemented in the form of functions in PostgreSQL with a RETURN VOID clause.
You have to list column names in SELECT which you are grouping in:
SELECT c.id, c.name, 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, c.name
ORDER BY score DESC LIMIT 8;
"It is not permissible to include column names in a SELECT clause that are not referenced in the GROUP BY clause."
I just had that issue but going from MySQL to SQL Server. I thought the fact that it is allowed it was strange!
Yes, in most databases, when you have a GROUP BY clause you can only select aggregates of columns or columns that appear in the GROUP BY clause. This is because it has no way of knowing if the other columns you're selecting are truly unique or not.
Just put the columns you want in the GROUP BY if they are indeed unique. This was a "feature" of MySQL that was questionable.
You can read about MySQL's behavior and how it is different here.
Example:
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, c.name, c.whatever_else
ORDER BY score DESC LIMIT 8;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With