-1

I believe this is a very simple query to make, but for some reason it doesn't work in Postgres (it works in other dbs)

This is how db schema kooks like:

tableA(
   id integer,
   ...
)

tableB(
   id integer
   tableA_id integer -- foreign key to tableA
   ...
)

And I want to select records for tableB, join with tableA, then group by tableA_id:

SELECT * from tableB b INNER JOIN tableA ON b.tableA_id = tableA.id
GROUP BY tableA_id
LIMIT 20

Works fine everywhere except Postgres, which throws that infamous "id must appear in group by or be used in aggragate function" error. But if I put b.id into group by then I don't get the duplicate records removed...

2
  • "Works fine everywhere" Yeah, right. Except that it doesn't work fine in any DBMS. Dec 4, 2022 at 16:24
  • If by "everywhere" you meant SQLite, then edit the question with that. Dec 4, 2022 at 16:28

2 Answers 2

4

Note you should always include the full table schema and example data, as it'll make your questions clearer.

In general, to include fields not in the GROUP BY clause in the SELECT list, you should be using an aggregate function, like so:

SELECT
    b.tableA_id,
    MAX(b.id) AS max_b_id -- Choose whichever aggregate function makes sense here
from tableB b 
INNER JOIN tableA 
    ON b.tableA_id = tableA.id
GROUP BY b.tableA_id
LIMIT 20

This will allow you to return values from the ungrouped columns, and your GROUP BY clause will still de-dupe the records the same as it was before. Note you have to choose which aggregate function makes the most sense for the ungrouped column you want to SELECT. Above I chose the MAX() function as an example, which will just return the largest b.id in this case. If you want the lowest b.id then use the MIN() function, etc.

Without knowing the rest of the fields in your tables or seeing sample data, I'm not sure what the goal of your query is. It'll also return a nondeterminsitic (random) 20 rows whenever you run it because you've specified a LIMIT without an ORDER BY clause.

Also, please don't use SELECT *, it is an anti-pattern. Always explicitly list out only the columns you need from the query. That is how you were receiving the error of your question, because * causes it to try to SELECT ungrouped columns.

Perhaps you don't even want to return the b.id column, and by explicitly listing out the columns names, you don't even need to include it. (Again, not sure what the goal of your query is?) So you could remove it from my example query, if that's the case.

9
  • But I use * because I need to get many other columns from table, and even if I listed them one by one, I get an error until I put all of them in GROUP BY which to me sounds ridiculous, because the grouping would literally render the same results as the select
    – Alex
    Dec 4, 2022 at 14:21
  • 1
    @alex It's not ridiculous, the database engine doesn't know that the other columns doesn't change the results until it actually executes the query (which is a later step in the process from when it's parsing your code syntactically). Your statement "Works fine everywhere except Postgres" is incorrect. The only 2 database systems that allow such syntax that I'm aware of is MySQL (which will pick a value at random from the ungrouped columns) or SQLite (which has different rules on what values get chosen). But it's unreliable, not ansi-standard, and mostly unsupported to not be explicit.
    – J.D.
    Dec 4, 2022 at 14:22
  • And again, * is an anti-pattern for many reasons (poor performance for multiple reasons and harder to manage in the long run), so you shouldn't be using it anyway.
    – J.D.
    Dec 4, 2022 at 14:26
  • ok I understand now what you mean, and what the error means. Basically PG wants us to specify which results to pick from ungrouped cols with aggregate function.. But I still think this is stupid, because it's obvious that I don't care how the others are grouped if I want to select distinct rows.. I think sqlite does it correctly
    – Alex
    Dec 4, 2022 at 14:28
  • Also, in my query I have order by last_updated_col, so if PG orders by this col, it does not need to know how to handle ungrouped cols because they should be sorted by last updated col, then grouped by the col I want
    – Alex
    Dec 4, 2022 at 14:56
3

I believe this is a very simple query to make, but for some reason it doesn't work in Postgres (it works in other dbs)

SELECT * 
FROM tableB b INNER JOIN tableA ON b.tableA_id = tableA.id
GROUP BY tableA_id
LIMIT 20 ;

This is incorrect. The statement as is will NOT work correctly in any SQL DBMS that has implemented standard SQL correctly. Postgres, SQL Server, Oracle, Firebird, DB2, event recent versions of MySQL will all reject the statement with similar error messages.

The only two exceptions (I know) are (old versions of) MySQL and SQLite.

Current versions of MySQL (with default settings) will reject the statement with a message similar to Postgres. Old versions of MySQL (5.6 and older, with default settings) will accept the statement and return results but they might be incorrect. There will be one row returned per values of the grouped column (tableAid) but is NO guarantee that the values will be from the same rows. It's possible thus to get results that do not exist in the tables!

In SQLite, the situation is a bit better. The statement is accepted and the result are consistent, coming from the same (one row per group). Details in Aggregate Queries Can Contain Non-Aggregate Result Columns That Are Not In The GROUP BY Clause . One problem is of course that this behaviour is non-standard SQL and you can't expect for this kind of queries to run as is in other DBMS. In fact you should expect that they will be rejected in all other DBMS and will need to be corrected.

For Postgres you are in luck because SQLite's implementation of this non-standard behaviour is exactly what Postgres (non-standard) DISTINCT ON does. So the specific query will be equivalent to the following. You can also use ORDER BY to affect which rows will be selected per group to be included in the results:

SELECT DISTINCT ON (tableA_id)
    * 
FROM tableB b INNER JOIN tableA ON b.tableA_id = tableA.id
-- ORDER BY tableA_id, ...
LIMIT 20 ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.