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

by

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;

More Related Contents:

  1. Why the “Group BY” by month didn’t work? [closed]
  2. What to do with null values when modeling and normalizing?
  3. Truncating all tables in a Postgres database
  4. How can I drop all the tables in a PostgreSQL database?
  5. postgres default timezone
  6. How to change PostgreSQL user password?
  7. quick random row selection in Postgres
  8. Time zone names with identical properties yield different result when applied to timestamp
  9. CONSTRAINT to check values from a remotely related table (via join etc.)
  10. How do I get a column with consecutive, increasing numbers, without having any numbers missing?
  11. Execute a dynamic crosstab query
  12. Copy a table from one database to another in Postgres
  13. Can I automatically create a table in PostgreSQL from a csv file with headers?
  14. User defined variables in PostgreSQL
  15. How to add an auto-incrementing primary key to an existing table, in PostgreSQL?
  16. Postgres query optimization (forcing an index scan)
  17. Difference between timestamps with/without time zone in PostgreSQL
  18. Postgres FOR LOOP
  19. Omitting the double quote to do query on PostgreSQL
  20. Strange behaviour in Postgresql
  21. Postgres: Distinct but only for one column
  22. In PostgreSQL, how to insert data with COPY command?
  23. Cursor based records in PostgreSQL
  24. How to add column if not exists on PostgreSQL?
  25. Docker – Postgres and pgAdmin 4 : Connection refused
  26. Configuration parameter work_mem in PostgreSQL on Linux
  27. How to work with PGpoint for Geolocation using PostgreSQL?
  28. Why unsigned integer is not available in PostgreSQL?
  29. Rails: FATAL – Peer authentication failed for user (PG::Error)
  30. now() default values are all showing same timestamp

Leave a Comment