problem with Decimal fields

[Aminmalek]

function pgp_pub_encrypt(numeric, bytea) does not exist
LINE 241: ')), pgp_pub_encrypt(60.50, dearmor('-----BEGIN PGP PUBLIC K...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
this error bellow happens when I was trying to use decimal fields in models
model:
fields.DecimalPGPPublicKeyField(max_digits=3, decimal_places=1)
fields.DecimalPGPPublicKeyField(max_digits=5, decimal_places=2,validators=[MaxValueValidator(Decimal(250.0)), MinValueValidator(Decimal(60.0))])

[Pepedou]

Any pointers on this? I just updated my Django+Heroku+PostgreSQL app to the latest version and I'm getting this error.

[marteinn]

The error is because up until Django 3.1 decimals was treated as strings, this changed in Django 3.2 where the field was converted to a actual decimal in postgres, this change is what causes the error above.

[some1ataplace]

It seems that the issue you are facing is related to the fact that the pgp_pub_encrypt function used by django-pgcrypto-fields does not support numeric (i.e. Decimal) data types.

One possible workaround to this is to convert the Decimal values to a float data type before using them in the pgp_pub_encrypt function. Here's an example:

from django.db import models
from pgcrypto import fields as pgcrypto_fields
from decimal import Decimal

class MyModel(models.Model):
    my_field = pgcrypto_fields.DecimalPGPPublicKeyField(max_digits=5, decimal_places=2)

    def save(self, *args, **kwargs):
        # Convert the Decimal value to a float before saving
        self.my_field = float(self.my_field)
        super().save(*args, **kwargs)

    def __str__(self):
        return f"My Model ({self.my_field})"

In the above example, we convert the Decimal value to a float before passing it to the DecimalPGPPublicKeyField instance, and then convert it back to a Decimal before using it in the rest of the application.

This should resolve the issue you are facing, but keep in mind that it may not be the most efficient solution or maintainable for large datasets. It's important to consider the data types being used and the compatibility with the encryption functions being used in the database.

---

Unfortunately, I cannot provide you with a code solution to modify the existing django-pgcrypto-fields library to enable the pgp_pub_encrypt function to use Decimal data types. This would require a significant modification to the source code of the library and understanding of how the pgcrypto extension works in PostgreSQL.

However, I can suggest a possible solution to your problem. One possible workaround is to cast Decimal values to a string type, and then encrypt the string using the pgp_pub_encrypt function.

from decimal import Decimal
from pgcrypto import pgp_pub_encrypt


def encrypt_decimal(value, public_key):
    # Convert Decimal to string
    value_str = str(value)

    # Encrypt the string value
    encrypted_value = pgp_pub_encrypt(public_key, value_str)

    return encrypted_value

This function takes a Decimal value and a public key as input, converts the decimal value to a string, and then encrypts the string value using the pgp_pub_encrypt function. You can call this function whenever you need to encrypt a Decimal field value before storing it in the database.

---

1. Create a new encryption field class to support Decimal data types:

First, create a new file called decimal_encryption_field.py inside the django-pgcrypto-fields folder. You will put the new PGPPublicKeyDecimalField class in this file. Start by importing the necessary modules and classes:

from decimal import Decimal
from django.db import models
from .fields import EncryptedFieldMixin, BasePGPPublicKeyField, BaseEncryptedField
from .managers import PassphraseManager, EncryptedManager

2. Create the CustomDecimal model field class:

class CustomDecimal(models.DecimalField):
def from_db_value(self, value, expression, connection):
return Decimal(value)

def to_python(self, value):
    if isinstance(value, Decimal):
        return value

    return Decimal(value)

3. Define the PGPPublicKeyDecimalField class:

You'll need to define the new PGPPublicKeyDecimalField class, which inherits from EncryptedFieldMixin and BasePGPPublicKeyField. Implement a db_type method to specify the PostgreSQL data type for the encrypted field and override the to_python method to handle the CustomDecimal conversion.

class PGPPublicKeyDecimalField(EncryptedFieldMixin, BasePGPPublicKeyField, BaseEncryptedField):
    #metaclass = models.SubfieldBase

    description = "A PGP public key encrypted Decimal field."
    form_field_class = CustomDecimal

    def get_db_prep_value(self, value, connection, prepared=False):
        return super(PGPPublicKeyDecimalField, self).get_db_prep_value(str(value), connection, prepared)

    def db_type(self, connection):
        return super(PGPPublicKeyDecimalField, self).db_type(connection)

    def from_db_value(self, value, expression, connection):
        decrypted_value = super(PGPPublicKeyDecimalField, self).from_db_value(value, expression, connection)
        return Decimal(decrypted_value) if decrypted_value else None

    def to_python(self, value):
        return Decimal(value) if value else None

4. Finally, register the new field type in the fields.py file:

Open the fields.py file in the django-pgcrypto-fields folder and import the PGPPublicKeyDecimalField class at the top of the file:

from .decimal_encryption_field import PGPPublicKeyDecimalField

from django.db import models
from django_pgcrypto_fields import PGPPublicKeyDecimalField

class MyModel(models.Model):
    encrypted_decimal_field = PGPPublicKeyDecimalField()

This should allow you to store numeric Decimal data types using the pgp_pub_encrypt function within the django-pgcrypto-fields library.

[peterfarrell]

It's unadvisable to convert a decimal to a float. Float are an approximation of a number and not precise. Although decimals can suffer precision problems, they are far more precise than floats. Precision is especially important when dealing with financial numbers or money.

As you can see, putting a float into a decimal or vice versa show the imprecision of floats.

>>> Decimal(0.01)  # this is float as the input
Decimal('0.01000000000000000020816681711721685132943093776702880859375')
>>> Decimal("0.01")  # this is a string representation of a precision decimal value
Decimal('0.01')

More research needs to be done to handle this issue if indeed that Django (or Psycopg) changed behavior around decimals. PR are welcome that elegantly handle backwards compatibility and don't introduce issues with imprecision using floats.

[jdanielnd]

@peterfarrell I opened a PR #622 to address this issue and also include JSONField – introducing Django 5.0 support.