Constraint to forbid NaN in postgres numeric columns using Django ORM

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?

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.

Back to Top