How can i create Generated/Computed column Postgres/DJANGO?

Question

How can I create Generated/Computed column Postgres/DJANGO?

I tried in both ways:

(1) By a Class:

class Product(models.Model):
    name = models.CharField(max_length=200, null=True)
    precio_costo = models.FloatField(null=True)
    cantidad = models.IntegerField(null=True)
    monto_stock = models.FloatField(
        always_generated='precio_costo * cantidad', stored=True)

ERROR I GET:

TypeError: init() got an unexpected keyword argument 'always_generated'

(2) Directly by POSTGRES Admin

But I can't update or add a new field, Default value is required by Django.

Please explain... what you were doing? What problem you encounterd? What methods you tried to solve the problem ? — Biplove Lamichhane, Oct 01 '20 at 14:46
`always_generated` doesn't seem to be a thing. Why did you expect it to work? — Mad Wombat, Oct 01 '20 at 14:53

Ralf · Answer 1 · 2020-10-24T09:57:49.773

Do you really need to have the result monto_stock stored in the database?

If no, then maybe just use a Python method/property on the class:

class Product(models.Model):
    name = models.CharField(max_length=200, null=True)
    precio_costo = models.FloatField(null=True)
    cantidad = models.IntegerField(null=True)

    @property
    def monto_stock(self):
        return self.precio_costo * self.cantidad

If yes, then maybe calculate the result in the models save() method:

class Product(models.Model):
    name = models.CharField(max_length=200, null=True)
    precio_costo = models.FloatField(null=True)
    cantidad = models.IntegerField(null=True)
    monto_stock = models.FloatField(null=True)

    def save(self, *args, **kwargs):
        self.monto_stock = self.precio_costo * self.cantidad
        super().save(*args, **kwargs)

Just be carefull: your fields allow NULL, so there will be errors if any field actually contains the value NULL (because you cannot do NULL/None multiplication in Python).

Do any of these options work for you?

EDIT: in case of option 1, to use the property monto_stock in your view you can simply access it like any other attribute of an model (but it will be read-only). For example:

# get a single product
p = Product.objects.first()
print(p.monto_stock)

# get sum of all products
print(sum(p.monto_stock for p in Product.objects.all()))

Just note that because it is a Python class property and not a DB field, it can NOT be used in database queries directly this way.

# will NOT work
print(Product.objects.aggregate(Sum('monto_stock'))))

Lets say for example i will not store these values on my db. How can i access "def monto_stock" SUM, on my views. Thanks in advance! — Isesy Miguel Ng, Oct 24 '20 at 02:00

How can i create Generated/Computed column Postgres/DJANGO?

1 Answers1