PostgreSQL Programming error in Django Query

Question

I have a query as below which returns the grade of all students of a specific class in a specific term(semester) in a specific session(academic year):

grades = Grade.objects.filter(term='First', student__in_class=1,session=1).order_by('-total')

then another query that annotate through the grades in order to get the sum of the 'total' field.

grades_ordered = grades.values('student')\
    .annotate(total_mark=Sum('total')) \
    .order_by('-total_mark')

At first everything works fine until when i migrated from using SQLite to postgreSQL then the following error begins to show up.

ERROR:

function sum(character varying) does not exist
LINE 1: SELECT "sms_grade"."student_id", SUM("sms_grade"."total") AS...
                                         ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

EDIT: here is my model

class Grade(models.Model):
    session = models.ForeignKey(Session, on_delete=models.CASCADE)
    term = models.CharField(choices=TERM, max_length=7)
    student = models.ForeignKey(Student, on_delete=models.CASCADE)
    subject = models.ForeignKey(Subject, on_delete=models.CASCADE)
    fca = models.CharField(max_length=10)
    sca = models.CharField(max_length=10)
    exam = models.CharField(max_length=10)
    total = models.CharField(max_length=3, blank=True, null=True)
    grade = models.CharField(choices=GRADE, max_length=1, blank=True, null=True)
    remark = models.CharField(max_length=50, blank=True, null=True)

any help you can provide would be appreciated.

thanks

Please post your model, it looks like the `total` field type may not support arithmetic operations. — mfrackowiak, Feb 21 '19 at 12:09
@mfrackowiak: I edited the post which now includes the model. — Abdoul, Feb 21 '19 at 12:15
While I definitely with point in the answer, you can also check this question https://stackoverflow.com/questions/50733159/can-we-do-a-sum-on-charfield-in-django-orm — mfrackowiak, Feb 21 '19 at 12:18
thank you @mfrackowiak; it appears as you stated the `total` field is CharField in my case which will not support arithmmatic operations — Abdoul, Feb 21 '19 at 12:26

score 6 · Accepted Answer · answered Feb 21 '19 at 12:14

6

Store numbers in integer or decimal not in text/varchar field

total = models.Integer(max_length=3, blank=True, null=True)

see this link

PostgreSQL Programming error in Django Query

1 Answers1