Django - Annotate and cast an encrypted TextField to a FloatField to 2 decimal places

Question

I'm using Django pgcrypto fields to encrypt an amount value in a model Invoice as follows:

from pgcrypto import fields

class Invoice(models.Model):
    # Some other fields
    amount_usd = fields.TextPGPSymmetricKeyField(default='')

    objects = InvoicePGPManager()  # Manager used for PGP Fields

I'm using aTextPGPSymmetricKeyField because I've to store the value as a float and django-pgcrypto-fields does not have an equivalent for FloatField.

Now I need to pass this amount_usd value via an API and I've to restrict the decimals upto two places.

I've tried using the following:

Invoice.objects.all().values('amount_usd').annotate(
    amount_to_float=Cast('amount_usd', FloatField())
)

But this gives an error as bytes(encrypted data) cannot be converted to float.

I tried using this as well:

from django.db.models import Func

class Round(Func):
    function = 'ROUND'
    template='%(function)s(%(expressions)s, 2)'


Invoice.objects.all().annotate(PGPSymmetricKeyAggregate(
            'amount_usd')).annotate(amount=Cast(
            'amount_usd__decrypted', FloatField())).annotate(
            amount_final = Round('amount'))

I get the following error:

django.db.utils.ProgrammingError: function round(double precision, integer) does not exist
LINE 1: ...sd, 'ultrasecret')::double precision AS "amount", ROUND(pgp_...
                                                         ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Is there any way to convert the encrypted field to a FloatField of upto 2 decimal places?

Answer 1

Your error says: double precision AS "amount" That's because you're converting amount_usd to a FloatField which converts to a double precision in SQL.

Try using a DecimalField (converts to type numeric in SQL) with it's arguments.

Invoices.objects.all().annotate(amount=Cast(
    PGPSymmetricKeyAggregate('amount_usd'),
    DecimalField(max_digits=20, decimal_places=2)))

Check the documentation here: Django DecimalField