PostgreSQL -must appear in the GROUP BY clause or be used in an aggregate function

Question

I am getting this error in the pg production mode, but its working fine in sqlite3 development mode.

 ActiveRecord::StatementInvalid in ManagementController#index

PG::Error: ERROR:  column "estates.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT "estates".* FROM "estates"  WHERE "estates"."Mgmt" = ...
               ^
: SELECT "estates".* FROM "estates"  WHERE "estates"."Mgmt" = 'Mazzey' GROUP BY user_id

@myestate = Estate.where(:Mgmt => current_user.Company).group(:user_id).all

Zephaniah

with GROUP BY in PostgreSQL, you cannot use column in selection list if you didn't use it in group by. Don't know how to translate it into rails – Falgout

Related: https://mcmap.net/q/80034/-must-appear-in-the-group-by-clause-or-be-used-in-an-aggregate-function/398670 – Chercherbourg

Possible duplicate: #19602448 – Kasiekask

Answer 1

@myestate1 = Estate.where(:Mgmt => current_user.Company)
@myestate = @myestate1.select("DISTINCT(user_id)")

this is what I did.

Zephaniah

Answer 2

If user_id is the PRIMARY KEY then you need to upgrade PostgreSQL; newer versions will correctly handle grouping by the primary key.

If user_id is neither unique nor the primary key for the 'estates' relation in question, then this query doesn't make much sense, since PostgreSQL has no way to know which value to return for each column of estates where multiple rows share the same user_id. You must use an aggregate function that expresses what you want, like min, max, avg, string_agg, array_agg, etc or add the column(s) of interest to the GROUP BY.

Alternately you can rephrase the query to use DISTINCT ON and an ORDER BY if you really do want to pick a somewhat arbitrary row, though I really doubt it's possible to express that via ActiveRecord.

Some databases - including SQLite and MySQL - will just pick an arbitrary row. This is considered incorrect and unsafe by the PostgreSQL team, so PostgreSQL follows the SQL standard and considers such queries to be errors.

If you have:

col1    col2
fred    42
bob     9
fred    44
fred    99

and you do:

SELECT col1, col2 FROM mytable GROUP BY col1;

then it's obvious that you should get the row:

bob     9

but what about the result for fred? There is no single correct answer to pick, so the database will refuse to execute such unsafe queries. If you wanted the greatest col2 for any col1 you'd use the max aggregate:

SELECT col1, max(col2) AS max_col2 FROM mytable GROUP BY col1;

Chercherbourg

user_id is primary key in User table, but on in Estate table. – Zephaniah

@HrishikeshSardar in which case this query doesn't make sense. Which Estate does it return for any given user_id? Pick one randomly? It won't do that. You have to use an aggregate or re-think what you're doing. – Chercherbourg

I am trying to figure out is like if I have @estate with values like {1,1,2,3,4,5,6,6}, but now i just want {1,2,3,4,5,6} from it, what should i do, in this example i have given u a array, but I want the same thing for array of hash – Zephaniah

how i will translate this query "SELECT col1, max(col2) AS max_col2 FROM mytable GROUP BY col1;" into ruby ? – Zephaniah

@HrishikeshSardar api.rubyonrails.org/classes/ActiveRecord/Calculations.html . Oddly enough, the first hit for "activerecord max" in Google. – Chercherbourg

Answer 3

I recently moved from MySQL to PostgreSQL and encountered the same issue. Just for reference, the best approach I've found is to use DISTINCT ON as suggested in this SO answer:

Elegant PostgreSQL Group by for Ruby on Rails / ActiveRecord

This will let you get one record for each unique value in your chosen column that matches the other query conditions:

MyModel.where(:some_col => value).select("DISTINCT ON (unique_col) *") 

I prefer DISTINCT ON because I can still get all the other column values in the row. DISTINCT alone will only return the value of that specific column.

Wanwand

DISTINCT ON is useful, where appropriate. But the query planner isn't as smart with DISTINCT ON as with DISTINCT or GROUP BY so it's worth avoiding unless you actually need it. It's also nonstandard. – Chercherbourg

Answer 4

After often receiving the error myself I realised that Rails (I am using rails 4) automatically adds an 'order by id' at the end of your grouping query. This often results in the error above. So make sure you append your own .order(:group_by_column) at the end of your Rails query. Hence you will have something like this:

@problems = Problem.select('problems.username, sum(problems.weight) as weight_sum').group('problems.username').order('problems.username')

Hightoned

I had the same (default_scope that ordered my model). So what I did is: Model.reorder(nil).group_by(...) this way the order is removed. – Handstand

this comment... should be the answer. – Inmesh