ruby on rails 3.1 - PostgreSQL -must appear in the GROUP BY clause or be used in an aggregate function -

i getting error in pg production mode, working fine in sqlite3 development mode.

 activerecord::statementinvalid in managementcontroller#index  pg::error: error:  column "estates.id" must appear in group clause or used in aggregate function line 1: select "estates".* "estates"  "estates"."mgmt" = ...                ^ : select "estates".* "estates"  "estates"."mgmt" = 'mazzey' group user_id  @myestate = estate.where(:mgmt => current_user.company).group(:user_id).all 

if user_id primary key need upgrade postgresql; newer versions correctly handle grouping primary key.

if user_id neither unique nor primary key 'estates' relation in question, query doesn't make sense, since postgresql has no way know which value return each column of estates multiple rows share same user_id. must use aggregate function expresses want, min, max, avg, string_agg, array_agg, etc or add column(s) of interest group by.

alternately can rephrase query use distinct on , order by if want pick arbitrary row, though doubt it's possible express via activerecord.

some databases - including sqlite , mysql - pick arbitrary row. considered incorrect , unsafe postgresql team, postgresql follows sql standard , considers such queries errors.

if have:

col1    col2 fred    42 bob     9 fred    44 fred    99 

and do:

select col1, col2 mytable group col1; 

then it's obvious should row:

bob     9 

but result fred? there no single correct answer pick, database refuse execute such unsafe queries. if wanted greatest col2 col1 you'd use max aggregate:

select col1, max(col2) max_col2 mytable group col1;