In my latest project I have been migrating my database which was using SQLite to a PostgreSQL database. I have been updating my SQL code to work with PostgreSQL, but have been struggling with one difference between SQLite and PostgreSQL, which is grouping.
For example, if I wanted to have multiple values in the SELECT clause, but only wanted to group on the one field, it was very easy in SQLite. This query works as intended in SQLite.
item_id is a foreign key
SELECT item_id, avg_price, min_price
FROM App_price
GROUP BY item_id
However executing the same query in PostgreSQL shows the error:
column "App_price.avg_price" must appear in the GROUP BY clause or be used in an aggregate function
The only field I want to group by is item_id, so I wont want to put avg_price
and min_price
in the GROUP BY clause. I also do not want to use any aggregate functions on these fields either. What is the solution to this?
2
Answers
The equivalent behavior, given that SQLite returns a random row, can be acheived with:
As @NickW pointed out, SQLite’s behavior is non-standard. It also isn’t logical: selecting a random row is not grouping.
Postgres Group by
Sqlite Group by 2.5. Bare columns in an aggregate query
So something like:
I know you said you didn’t want aggregate functions but that is what you will have to do. Even if you resorted to as sub-query the avg_price and min_price would basically be some essentially random value. Bottom line for a given grouping field(item_id) any non-grouped values are just going to be picked out of hat and not really useful.