PostgreSQLism: all non-aggregated fields to be present in the GROUP BY clause

This issue is seen very frequently.

The following query executes under MySQL but fails under PostgreSQL:

SELECT u.uid, u.name FROM node n
INNER JOIN users u ON u.uid = n.uid
WHERE n.type = 'blog' AND n.status = 1
GROUP BY u.uid

To perform a GROUP BY, the parser will trigger distinct values.
In the above example, the distinct values are u.uid and u.name.

The right query is:

SELECT u.uid, u.name FROM node n
INNER JOIN users u ON u.uid = n.uid
WHERE n.type = 'blog' AND n.status = 1
GROUP BY u.uid, u.name

This error is seen very often in Drupal: for PostgreSQL (and the SQL92 standard AFAIK), all non-aggregated fields must be present in the GROUP BY clause.

The same happens when running SELECT count(*):

The following query works under MySQL but fails under PostgreSQL:

SELECT COUNT(nid) FROM node 
WHERE nid > 0 AND type IN ('page') 
ORDER BY nid

The right query is:

SELECT COUNT(nid) FROM node 
WHERE nid > 0 AND type IN ('page') 
GROUP BY nid
ORDER BY nid

In both cases, MySQL parser is smarter that PostgreSQL one, as it is able to rewrite the query.

Reference:
http://drupal.org/node/473876
http://drupal.org/node/455840
http://drupal.org/node/498708