Postgresql group by for many columns I have such query:

SELECT "Movie"."title", 
       "Movie"."id", 
       "Movie"."publicationDate", 
       "Movie"."authorID", 
       "Author"."photoURL" as "authorPhoto", 
       concat("Author"."name", ' ', "Author"."surname") as "authorName",
       "Movie"."seriesID", 
       "Series"."name" as "seriesName", 
       "Series"."logoURL" as "seriesLogo", 
       "Movie"."languageID" as "language", 
       "Movie"."ratingUp", 
       "Movie"."ratingDown", 
       "userWatched"."watchedDate", 
       CASE WHEN "UserMovieFavourited"."userID" = '6F9BD058-853F-4B50-92D6-9170118A2727' 
            THEN TRUE 
            ELSE FALSE 
            END as "favourited", 
       CASE WHEN "UserMovieWatchlist"."userID" = '6F9BD058-853F-4B50-92D6-9170118A2727' 
            THEN TRUE 
            ELSE FALSE 
            END as "onWatchlist",
       CASE WHEN "userWatched"."userID" = '6F9BD058-853F-4B50-92D6-9170118A2727' AND "userWatched"."watchedDate" IS NOT NULL 
            THEN TRUE 
            ELSE FALSE 
            END as "watched",
            count(DISTINCT "overallWatched"."watchedDate") as "viewCount" 
FROM "Movie" 
LEFT JOIN "Series" ON "Series"."id" = "Movie"."seriesID" 
LEFT JOIN "Author" ON "Author"."id" = "Movie"."authorID" 
LEFT JOIN "UserMovieFavourited" ON "UserMovieFavourited"."movieID" = "Movie"."id" 
                                AND "UserMovieFavourited"."userID" = '6F9BD058-853F-4B50-92D6-9170118A2727' 
LEFT JOIN "UserMovieWatchlist" ON "UserMovieWatchlist"."movieID" = "Movie"."id" 
                               AND "UserMovieWatchlist"."userID" = '6F9BD058-853F-4B50-92D6-9170118A2727' 
LEFT JOIN "UserMovieWatchProgress" as "userWatched" ON "userWatched"."movieID" = "Movie"."id" 
                                                    AND "userWatched"."userID" = '6F9BD058-853F-4B50-92D6-9170118A2727' 
LEFT JOIN "UserMovieWatchProgress" as "overallWatched" ON "overallWatched"."movieID" = "Movie"."id" 
GROUP BY "Movie"."id", 
         "Author"."photoURL", 
         "Author"."name", 
         "Author"."surname", 
         "Series"."name", 
         "Series"."logoURL", 
         "userWatched"."watchedDate", 
         "UserMovieFavourited"."userID", 
         "UserMovieWatchlist"."userID", 
         "userWatched"."userID"


In MySQL i needed only one column for grouping that's Movie.id. For postgresql I needed to add 10 columns to make this query work as shown above.

Is this natural for postgresql or this query should be refactored to remove need of so many grouping columns?
Imagine there is a row in "Movie" where id is 42.

Then imagine that there are two rows in "UserMovieWatchProgress" where "movieID" is 42.

Then the result of

"Movie" LEFT JOIN "UserMovieWatchProgress"
   ON "UserMovieWatchProgress"."movieID" = "Movie"."id" 


would be something like

 "Movie".id | "UserMovieWatchProgress"."movieID"
------------+------------------------------------
         42 |                                143
         42 |                                 93


Now consider the following query:

SELECT "Movie".id,
       "UserMovieWatchProgress"."movieID"
FROM "Movie"
   LEFT JOIN "UserMovieWatchProgress"
      ON "UserMovieWatchProgress"."movieID" = "Movie"."id"
GROUP BY "Movie".id;


By definition of GROUP BY, there can be only one result row per "Movie".id.
Which number should we take for "UserMovieWatchProgress"."movieID"? That is unclear and not well defined, so this is forbidden by the SQL standard.

Now if PostgreSQL can deduce that there can be only one possible "UserMovieWatchProgress"."movieID", it will allow you to omit the column from the GROUP BY clause.



If you just want any of the "UserMovieWatchProgress"."movieID" in the query result, and you don't care which one, you can do the following in PostgreSQL:

SELECT DISTINCT ON ("Movie".id)
       "Movie".id,
       "UserMovieWatchProgress"."movieID"
FROM "Movie"
   LEFT JOIN "UserMovieWatchProgress"
      ON "UserMovieWatchProgress"."movieID" = "Movie"."id";


Perhaps that is what you are looking for.