Django with Postgresql, column must appear in the GROUP BY clause or be used in an aggregate function

Question

I'm using Django 1.11 and Postgresql 9.6 In my app, there is a model called Person, it have several fields. In database, it's a materialized view.

class Person(models.Model):
    personid = models.CharField(max_length=18, primary_key=True)
    count = models.BigIntegerField()
    native = models.CharField(max_length=2)
    ...

When execute

persons = Person.objects.values('personid', 'native')\
    .annotate(total=Count('native'))

It says psycopg2.ProgrammingError: column "person.native" must appear in the GROUP BY clause or be used in an aggregate function

When only select one column or not set the personid as primary key or not execute annotate it won't get error.

I print the query sql:

SELECT
"person"."native",
"person"."personid",
COUNT("person"."native") AS "total"
FROM "person"
GROUP BY "person"."native", "person"."personid"

What can I do?

I make the view into table and set the personid as primary key, and then no problems.

Answer 1

This is a known bug in Django >= 1.8 and Django < 2.0. It has been fixed in Django 2.0. I had the same problem and brought it up in the django-users mailing list.

What happened is that Django performed some optimizations, especially based on PostgreSQL. In PostgreSQL, you only need to use the pk columns in the GROUP BY clause, but that is only for tables. (The query runs more quickly if you do that.) You cannot have a PK in a view in PostgreSQL, which is why it is a problem for us since we are using un-managed models coupled with views in the backend.

References:

• https://github.com/django/django/commit/daf2bd3efe53cbfc1c9fd00222b8315708023792
• https://groups.google.com/forum/#!topic/django-developers/lx3ZSq-W9X4
• https://groups.google.com/d/msg/django-developers/lx3ZSq-W9X4/yh4I2CsoBwAJ
• https://code.djangoproject.com/ticket/28107