python - Constraint to forbid NaN in postgres numeric columns using Django ORM - Stack Overflow

admin管理员组

文章数量:1122832

Postgresql allows NaN values in numeric columns according to its documentation here.

When defining Postgres tables using Django ORM, a DecimalField is translated to numeric column in Postgres. Even if you define the column as bellow:

from django.db import models

# You can insert NaN to this column without any issue
numeric_field = models.DecimalField(max_digits=32, decimal_places=8, blank=False, null=False)

Is there a way to use Python/Django syntax to forbid NaN values in this scenario? The Postgres native solution is to probably use some kind of constraint. But is that possible using Django syntax?

Edit: As willeM_ Van Onsem pointed out, Django does not allow NaN to be inserted to DecimalField natively. However, the DB is manipulated from other sources as well, hence, the need to have an extra constraint at the DB level (as opposed to Django's built-in application level constraint).

Postgresql allows NaN values in numeric columns according to its documentation here.

When defining Postgres tables using Django ORM, a DecimalField is translated to numeric column in Postgres. Even if you define the column as bellow:

from django.db import models

# You can insert NaN to this column without any issue
numeric_field = models.DecimalField(max_digits=32, decimal_places=8, blank=False, null=False)

Is there a way to use Python/Django syntax to forbid NaN values in this scenario? The Postgres native solution is to probably use some kind of constraint. But is that possible using Django syntax?

Edit: As willeM_ Van Onsem pointed out, Django does not allow NaN to be inserted to DecimalField natively. However, the DB is manipulated from other sources as well, hence, the need to have an extra constraint at the DB level (as opposed to Django's built-in application level constraint).

• python
• django
• postgresql
• orm

Share Improve this question Follow edited Nov 22, 2024 at 16:01 szamani20 asked Nov 21, 2024 at 20:28 szamani20szamani20 7121010 silver badges2727 bronze badges 3

• 1 Django will not allow NaN to be entered, so unless the database is also "fed" from another source, NaN can not occur. – willeM_ Van Onsem Commented Nov 21, 2024 at 23:23
• That is true. In my comment under your post I clarified that the DB is manipulated from other sources as well. @willeM_VanOnsem – szamani20 Commented Nov 22, 2024 at 15:55
• yes, it was more as an "addendum" to the other post. Perhaps not relevant here, but for future readers. But regardless, it is always better to enforce at the database, so I would indeed strongly advise to use a constraint. – willeM_ Van Onsem Commented Nov 22, 2024 at 15:58

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

I don't have a PostgreSQL database to test against but you can try creating a database constraint using a lookup based on the IsNull looukup:

from decimal import Decimal
from django.db.models import (
    CheckConstraint,
    DecimalField,
    Field,
    Model,
    Q,
)
from django.db.models.lookups import (
    BuiltinLookup,
)


@Field.register_lookup
class IsNaN(BuiltinLookup):
    lookup_name = "isnan"
    prepare_rhs = False

    def as_sql(self, compiler, connection):
        if not isinstance(self.rhs, bool):
            raise ValueError(
                "The QuerySet value for an isnan lookup must be True or False."
            )
        sql, params = self.process_lhs(compiler, connection)
        if self.rhs:
            return "%s = 'NaN'" % sql, params
        else:
            return "%s <> 'NaN'" % sql, params


class Item(Model):
    numeric_field = DecimalField(
        max_digits=32,
        decimal_places=8,
        blank=False,
        null=False,
    )

    class Meta:
        constraints = [
            CheckConstraint(
                check=Q(numeric_field__isnan=False),
                name="numeric_field_not_isnan",
            ),
        ]

@MT0 answered the question. I just want to add that if you use a DemimalField and manipulate data in Django, you can normally not insert NaN or Infinity in the database. Indeed, Django itself checks if the value is finite (NaN is not considered finite).

Django first "prepares" the values to be representable in the database, and it does that with the .get_db_prep_save(…) method ^[GitHub]:

def get_db_prep_save(self, value, connection):
    if hasattr(value, "as_sql"):
        return value
    return connection.ops.adapt_decimalfield_value(
        self.to_python(value), self.max_digits, self.decimal_places
    )

Which calls the .to_python(…) function. The .to_python(…) method ^[GitHub] then checks if the Decimal is finite:

def to_python(self, value):
    # …
    if not decimal_value.is_finite():
        raise exceptions.ValidationError(
            self.error_messages["invalid"],
            code="invalid",
            params={"value": value},
        )

It is of course better to add a constraint, since it might still be possible that some update queries eventually result in a NaN, but for simple insertions/updates through Django, this should not happen.

本文标签： pythonConstraint to forbid NaN in postgres numeric columns using Django ORMStack Overflow