Django Postgres Aggregate Functions

Question

I want to use aggregate functions on a column in my model.

Following is my model:-

class ShipmentWeightMapping(models.Model):
    weight = models.CharField(max_length = 255)
    status = models.CharField(max_length = 255)
    time = models.DateTimeField( auto_now_add = True)
    shipment_id = models.ForeignKey('Shipment', related_name = 'weights')

Here is my aggregation query:-

shipment_weight_obj = ShipmentWeightMapping.objects.filter(shipment_id__in = shipment_id_list).aggregate(Avg('weight'),Max('weight'),Min('weight'),Sum('weight'))
total_weight = shipment_weight_obj['weight__sum']
max_weight = shipment_weight_obj['weight__max']
min_weight = shipment_weight_obj['weight__min']
avg_weight = shipment_weight_obj['weight__avg']

The above code runs with MySQL as DB but returns an error when used with postgres it returns an error:-

LINE 1: SELECT SUM("data_shipmentweightmapping"."weight") AS "weight...
[Tue Jul 08 04:05:38 2014] [error]                ^
[Tue Jul 08 04:05:38 2014] [error] HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

How come is this happening? I know that weight is a char field and I'm adding a char field, which I suppose is not allowed in postgres, but how can alter my query so that it works ? Also , why it works in MySql and not in postgres

Craig Ringer · Accepted Answer · 2014-07-08 04:35:28Z

2

I know that weight is a char field and I'm adding a char field, which I suppose is not allowed in postgres, but how can alter my query so that it works ?

Either:

Use a suitable data type for weight - probably float4 or float8 (double precision), but maybe numeric if you need it; or
Cast to a numeric type before aggregation, so you run SUM( CAST("data_shipmentweightmapping"."weight" AS float4))

Also , why it works in MySql and not in postgres

MySQL lets you do all sorts of crazy things that PostgreSQL doesn't. Like adding a bunch of text fields together and implicitly converting them to numbers. Inserting zero as a date. Comparing nulls for equality.

If you run MySQL in STRICT ANSI mode (which you should always do) then it's a lot more sensible about the weird things it lets you do - or doesn't.

answered Jul 8, 2014 at 4:35

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

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

2 Comments

Praful Bagai Over a year ago

How can I do it in Django ORM?

Craig Ringer Over a year ago

@user1162512 Ideally: change the column type to the correct type in your models. If you mean "how do I type-cast a column in a Django ORM query" ... no idea. I suggest posting a Django-specific question on that and linking back to this one for context.

Collectives™ on Stack Overflow

Django Postgres Aggregate Functions

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related