Problem with MySQL query translated to PostgreSQL

I have a query that worked fine on MySQL and now doesn't on PostgreSQL This is the query:

  SELECT "users".* 
    FROM "users" 
    JOIN "favorites" ON "favorites"."user_id" = "users"."id"
   WHERE users.id NOT IN (2)
     AND favorites.favoritable_id IN (1)
GROUP BY favorites.user开发者_Python百科_id 
ORDER BY RANDOM() 
   LIMIT 5

This is the error:

column "users.id" must appear in the GROUP BY clause or be used in an aggregate function

I have looked up this error but couldn't really find out what I must to do fix this.

MySQL is awfully lax when it comes to the GROUP BY-clause. In MySQL, you can add columns to your select clause that are not in the GROUP BY or an aggregate functions. Other RDBMS do not allow this, as it makes no sense semantically. MySQL just returns more or less random row values that fit the given query.

From the MySQL Doc

In standard SQL, a query that includes a GROUP BY clause cannot refer to nonaggregated columns in the select list that are not named in the GROUP BY clause. [...] MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause.

What you have to do is, find out what the query is supposed to do and rewrite it.

MySQL (and SQLite for that matter) do not require you to specify every column in the GROUP BY clause that are not wrapped in aggregate functions (IE: MIN, MAX, COUNT, SUM, etc). This is by design, and is also ANSI spec. However, this means that the values for those columns will be arbitrary -- they can not be guaranteed every time the query is run.

PostgreSQL/Oracle/SQL Server require you to either specify each column not wrapped in aggregate functions in the group by, or re-write the query so it is not necessary. Use:

  SELECT u.*
    FROM USERS u
   WHERE EXISTS (SELECT NULL
                   FROM FAVORITES f
                  WHERE f.user_id = u.id
                    AND f.favoritable_id = 1)
     AND u.id != 2
ORDER BY RANDOM()
   LIMIT 5

Another option in PostgreSQL is the distinct on clause:

select distinct on (col1, col2) * from users;

this will do pretty much what mysql was doing.