Arbitrary GROUP BY fields

I'm using pg-partitioning to partition tables on Django 2.2 & Postgres 11. It's working great as far as that goes, except that I bumped hard into annotated queries. Since PG can't guarantee PK uniqueness across partitioned tables, it requires including all fields in the GROUP BY that will in the output.

Django generates this query:

SELECT

  "page_titles"."id",

  "page_titles"."date",

  "page_titles"."name",

  "page_titles"."title",

  SUM("page_titles"."allow") AS "allow",

  SUM("page_titles"."block") AS "block",

  SUM("page_titles"."hit_count") AS "hits",

  "categories_category"."name" AS "category_name",

  "categories_rating"."name" AS "rating_name"

FROM

  "page_titles"

  LEFT OUTER JOIN "categories_category" ON ("page_titles"."category_id" = "categories_category"."id")

  LEFT OUTER JOIN "categories_rating" ON ("page_titles"."rating_id" = "categories_rating"."id")

GROUP BY

  "page_titles"."id",

  "categories_category"."name",

  "categories_rating"."name"

ORDER BY

  "page_titles"."date" DESC,

  "page_titles"."id" DESC;

That query raises an error:

ERROR:  column "page_titles.date" must appear in the GROUP BY clause or be used in an aggregate function

LINE 3:     "page_titles"."date",

            ^

So if we add all the (non-summed) fields in the GROUP BY that the query will return, as the query below, then the query runs successfully.

SELECT

  "page_titles"."id",

  "page_titles"."date",

  "page_titles"."name",

  SUM("page_titles"."allow") AS "allow",

  SUM("page_titles"."block") AS "block",

  SUM("page_titles"."hit_count") AS "hits",

  "categories_category"."name" AS "category_name",

  "categories_rating"."name" AS "rating_name"

FROM

  "page_titles"

  LEFT OUTER JOIN "categories_category" ON ("page_titles"."category_id" = "categories_category"."id")

  LEFT OUTER JOIN "categories_rating" ON ("page_titles"."rating_id" = "categories_rating"."id")

GROUP BY

  "page_titles"."id",

  "page_titles"."date",

  "page_titles"."name",
  "page_titles"."title",
  "categories_category"."name",

  "categories_rating"."name"

ORDER BY

  "page_titles"."date" DESC,

  "page_titles"."id" DESC;

I really can't be using raw SQL for these queries because of dynamic WHERE clauses. Is there a way to use / extend the ORM to add arbitrary GROUP BY fields? 

Or any other suggestion about how to use the ORM to generate queries on partitioned tables where Postgres doesn't have a global index...?

TIA!

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/414d3e70-418c-41e9-becb-65209ed0b7eb%40googlegroups.com.