Django is generating invalid SQL for Postgres

Question

I'm a noob django user, and I'm having some trouble with the Model.objects.all method. I've got a user model: (I know keeping passwords in plaintext is bad practice, but this is just supposed to be a toy example)

class UsersModel(models.Model):
    password = models.CharField(max_length=MAX_PASSWORD_LENGTH)
    user = models.CharField(max_length=MAX_USERNAME_LENGTH, primary_key=True)
    count = models.IntegerField()

And I've got a test method that's supposed to drop all the entries in the user table:

def function(self):
    UsersModel.objects.all().delete()

For some reason, calling UsersModel.objects.all() raises the error

DatabaseError: column "cs169proj1_usersmodel.user" must appear in the GROUP BY clause or 
be used in an aggregate function
LINE 1: SELECT "cs169proj1_usersmodel"."user", "cs169proj1_usersmode...

From Googling, I've found that this particular error in SQL only comes up on Postgresql (which I'm using). Anyone know how to get around/fix this?

I don't think it is the cause of the error but you should be careful when using something else than the id as primary_key. It may cause troubles with the admin site : see stackoverflow.com/q/2011629/117092 — luc
– luc, Commented Feb 11, 2013 at 6:41
Fixed it! I deleted and recreated the database, then ran manage.py syncdb. Don't know what it was, but it's gone now. — sfendell
– sfendell, Commented Feb 11, 2013 at 7:31

Erwin Brandstetter · Accepted Answer · 2013-02-11 06:35:00Z

5

Sounds like the column name count is misinterpreted as aggregate function.

Best solution: Never use reserved words as identifiers.

answered Feb 11, 2013 at 6:35

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

sfendell Over a year ago

This persisted after deleting the count column. I chose count as the name because this is for a class project, and I was just following the spec.

Erwin Brandstetter Over a year ago

@Yuji'Tomita'Tomita: Of course it wouldn't reference the field count if that was mistakenly taken to be an aggregate function.

Yuji 'Tomita' Tomita Over a year ago

Good point... Can postgresql get confused even if django quotes all SELECT args? "table"."count".

Erwin Brandstetter Over a year ago

@Yuji'Tomita'Tomita: No, PostgreSQL couldn't be confused. The column name would be count while the aggregate function would be count(something). The confusion would have to happen earlier in the food chain. And I am not convinced that's what actually happened, but my advice is good in any case.

Yuji 'Tomita' Tomita Over a year ago

Thanks for your responses! They have been insightful to me!

Community · Accepted Answer · 2017-04-13 12:42:39Z

2

I ran into simillar issue when upgrading from django 1.6 to 1.9.8 on production with postgresql.

In my case the issue was due to change in Django 1.9 requiring at least postgresql 9.1 as described here.

Nice postgresql update instruction for redhat/centos here.

edited Apr 13, 2017 at 12:42

CommunityBot

11 silver badge

answered Jul 20, 2016 at 17:53

pawel lukaszewicz

5754 silver badges6 bronze badges

Collectives™ on Stack Overflow

Django is generating invalid SQL for Postgres

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related