ProgrammingError: function avg(character varying) does not exist' - Django project

Question

I have recently deployed a Django project to Heroku. While testing out the functionality in the browser, I've come across a new error when attempting to render a particular template:

ProgrammingError at /accelerators/6/
function avg(character varying) does not exist
LINE 1: SELECT AVG("reviews_review"."mentorship") AS "avg_mentorship...
           ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

This issue never arose in the development server, but I think I know what the problem is. I use Avg in my Accelerator model to determine the decimal value of a field (avg_mentorship) by determining the mean average of inputs (mentorship) into another model (review).

My mentorship field is a charfield where the choices are numbers as strings. I've included the relevant code from either model below:

class Accelerator(models.Model):
    avg_mentorship = models.DecimalField(decimal_places=2, max_digits=3)

    @property
    def avg_mentorship(self):
        quantity = Review.objects.filter(subject=self)
        if len(quantity) > 0:
            mentorship_result = Review.objects.filter(subject=self).aggregate(avg_mentorship=Avg('mentorship'))['avg_mentorship']
        else:
            mentorship_result = 0
        return mentorship_result

class Review(models.Model):
    RATINGS = (
        ('1', '1'),
        ('2', '2'),
        ('3', '3'),
        ('4', '4'),
        ('5', '5'),
    )
    mentorship = models.CharField(choices=RATINGS, blank=False, max_length=1, default='1')

    def save(self, *args, **kwargs):
        self.mentorship = int(self.mentorship)
        super(Review, self).save(*args, **kwargs)

It seemed like a pretty simple fix, so I added the bit of code converting mentorship to an int within the review model. However, this hasn't done the trick, so I'm wondering if there's any reason why my amended code shouldn't work, and also whether I'm definitely correct in interpreting the error message (it's not my strong suit). Any input greatly appreciated. Thanks.

Answer 1

You should make mentorship an IntegerField ^[Django-doc] (or FloatField or IntegerField), like:

class Review(models.Model):
    RATINGS = (
        (1, '1'),
        (2, '2'),
        (3, '3'),
        (4, '4'),
        (5, '5'),
    )
    mentorship = models.IntegerField(choices=RATINGS, default=1)

You can not calculate the average over a CharField. What would an average of 'foo' and 'bar' be? You thus need to store your data into numerical fields.

It will probably require some work to migrate this. If you do not need to preserve existing data, I advice to just remove the migration files where you construct the Review model, and just start over with this model and table.

Note that you can improve the efficiencly of calculating the aggregate with:

class Accelerator(models.Model):

    @property
    def avg_mentorship(self):
        return self.review_set.aggregate(
            avg_mentorship=Avg('mentorship')
        )['avg_mentorship'] or 0

Answer 2

As you can here in the SQL generated for the ORM query you made

LINE 1: SELECT AVG("reviews_review"."mentorship") AS "avg_mentorship...

It is trying to compute average on mentorship field of reviews_review table. AVG expects integer value to be passed as argument, as the data type defined for it was CharField according to your model it is throwing the error. You have it fixed by changing the type of your mentorship field.

Something like this.

class Review(models.Model):
    RATINGS = (
        (1, '1'),
        (2, '2'),
        (3, '3'),
        (4, '4'),
        (5, '5'),
    )
    mentorship = models.IntegerField(default=1, choices=RATINGS)