ERROR: column must appear in the GROUP BY clause or be used in an aggregate function

0
votes

I try rewrite one application from MySQL to PostgreSQL.

In MySQL I have SQL statement:

SELECT u.guid, u.* 
FROM ossn_users as u 
WHERE(u.time_created IS NOT NULL) 
ORDER by u.guid ASC 
LIMIT 0, 10;

In Postgres I got for this statement error. ERROR: column u.guide must appear in the GROUP BY clause or be used in an aggregate function

I modified SQL statment for PostgreSQL to this:

SELECT u.guid, u.* 
FROM ossn_users as u 
WHERE(u.time_created IS NOT NULL) 
GROUP by u.guid, u.type, u.username, u.email, u.password, 
         u.salt, u.first_name, u.last_name, u.last_login, 
         u.last_activity, u.activation, u.time_created 
ORDER by u.guid ASC 
LIMIT 0 OFFSET 10;

But in application it does not work.

Please help!

How I could modify MySQL statment to fit PostgrSQL needs.

Thank you.

1
sqlpostgresql

1 Answers

0
votes

Not sure why you need a GROUP BY, I see no aggregate, but in postgresql, LIMIT 0 will limit your results to 0. Change your LIMIT and remove the OFFSET to get results from your postgresql query.

MySQL: LIMIT 0, 10 means offset by 0, limit to 10

Postgresql: LIMIT 10, OFFSET 0 or simply LIMIT 10 means limit 10, offset by 0