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

Question

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

Answer 1

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.