ProgrammingError: column … must appear in the GROUP BY clause or be used in an aggregate function – Postgresql

Django’s documentation mentions aggregation:

q = Book.objects.annotate(Count('authors'))

My code is full of similar constructs (adding a count of something to a queryset, using annotate), and this always worked well. Now suddenly, for reasons unknown, I’m getting this error:

ProgrammingError: column ... must appear in the GROUP BY clause or be used in an aggregate function

In fact, I have the same version of Django on my dev and prod, and everything works on dev, but fails on prod (typical). Spontaneously. For no apparent reason.

And I cannot add the named column to the GROUP BY clause, because then it wants me to add the next column, and the next, and the next, etc. I can also not add some random aggregate function. In short: the solutions that the error mentions make no sense!

There are many more questions with this same error, many unanswered. I have no solution, but it seems this error started appearing lately, in Django versions 4+, at least for me.

I’ve tried the failing SQL query directly within Postgres and it fails there as well, of course, with exactly the same error. The fault is not with Django.

The only way to make this annotate query work, is when selecting distinct fields, like for example:

q = Book.objects.values('name').annotate(Count('authors'))

But unfortunately that makes no sense in many cases within my project: I do need the whole object, not a single field.

It’s seems logical to say that the version of Postgres changed on the server, but that isn’t the case. Things broke after a failed deploy that I had to roll back. The database got corrupted in the process (there were actually double ID’s in some tables). Afterwards this ProgrammingError appeared and some page requests fail with a 500 error.

Any clues to a solution?

UPDATE 1: It seems that there actually are queries, just like the one at the top of this post, that work well. At the same time there are others, that looks exactly the same, that don’t work. I’m trying to pinpoint the difference. To be continued…

UPDATE 2: I can’t really find a difference. I’ve updated this question to point at a more exact case. In my project it’s, say, a Product model that has a tags property, which is a ManyToMany relation to a Tag model – exactly like the relation in the documentation of Django (Book model with a m2m property ‘authors’). But it fails…

UPDATE 3: Additionally to the aforementioned case there’s also this one:

class ModelA(models.Model):
    a = models.IntegerField()

class ModelB(models.Model):
    b = models.IntegerField()

class ModelC(models.Model):
    a = models.ForeignKey(ModelA, related_name='cs')
    b = models.ForeignKey(ModelB, related_name='cs')

ModelA.objects.annotate(c=Count('cs'))  # fails
ModelB.objects.annotate(c=Count('cs'))  # works!

Someone has an explanation for this one? (Of course ModelA and ModelB have more fields, does that matter?)

UPDATE 4: As I said, this problem only occured on prod, not on dev. So I was curious and copied the prod database to dev, and now this same problem appears on dev as well! Conclusion: this error appears because of the data in the database! Not the structure, not the version. As mentioned, this problems started occurring after a database corruption, so that’s the key. How could I solve this? How to repair the DB corruption? Note: I’ve already repaired (as in: removed) all duplicate key and unique field issues, etc. Not sure what else could be wrong.