Django ProgrammingError must appear in the GROUP BY clause or be used in an aggregate function

user_1748888 0
Viewed 2099

Give it any basic model say;

class Post(models.Model):
    created = models.DateTimeField(auto_now_add=True)
    title = models.CharField(_('Title'), max_length=100)
    content = models.TextField(_('Content html'), max_length=65000)
    author = models.ForeignKey('user.User', on_delete=models.SET_NULL)

A query like Post.objects.annotate(Count('id')) (or any field, any annotate()) fails with the following error:

ProgrammingError: column "post.created" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT "post"."id", "post"."created", "post"."ti...

Using django 1.11.16, and postgres 9.4.19.

As I read here in another stackoverflow question tried different django versions and postgres versions; using django 2.0, 2.1.2, postgres 9.5.. same error! Reading around I've seen that might be a problem related to SQL, but I'm having this problem only at one server running Ubuntu 18.04 (bionic). Running the query locally at Ubuntu 16.04, with django 1.11.16, or any version above that and postgres 9.4 or above runs fine in my local system.. So the problem might actually be related with some low level libraries perhaps, I'm not running complex queries, any simple annotate() with django 1.11+ fails in ubuntu 18.04 with postgres 9.4 or 9.5

[UPDATE] Might be useful for you if you find yourself in this scenario with no clues of what's going on, verify that the table in question has its indexes created. My problem turned out to be the posts table had no PRIMARY KEY definition nor any other constraint, a fail in a pg_restore which restored all the data and some schema definitions, yeah you read right some other schema definitions were missing, no idea of why.. But instead of trying to debug what happened with pg_restore in the first place, I ran an initial python manage migrate on an empty DB so the schema was correctly created this time and verified (psql -d <db_name> -c '\d posts'), then run pg_restore again with the --data-only and --disable-triggers flags.. So finally I got the schema and data properly restored and the query worked

group-by postgresql django python
2 Answers

That error message is because PostgreSQL will not make a guess as to what to do with non-grouped columns when there's a aggregate function in the query. This is one of the cases where the Django ORM handwaves too much and allows us to shoot ourselves in the foot.

I ran a test on my projects that use Django 2.1 and another that uses 1.11 with Model.objects.annotate(Count('id')) with no issues. If you post the full QuerySet, people will be able to help you further.

user_16373510

I met the problem and I solved it. you should add the .order_by("xxx") follow your ORM!

before:

search_models.Author.objects.values("first_institution", 'institution_code').annotate(counts=Count('first_institution'))

after:

search_models.Author.objects.values("first_institution", 'institution_code').annotate(counts=Count('first_institution')).order_by('-counts')

It's worked for me, also wish can help you.

user_137271990
Related