Compare expression with constant in CHECK constraint

Question

I'd like to use Django's CheckConstraint to add a check constraint to a table using PostgreSQLs num_nonnulls() function, similar to this:

create table foo(
    a text,
    b int,
    [...],
    check num_nonnulls(a, b, ...) = n);

n is a constant but may be different for different tables (mostly it's going to be 1 but I'd like to find a general solution). This is how far I got:

class Foo(models.Model):
    a = models.TextField(null=True)
    b = models.IntegerField(null=True)
    [...]

    class Meta:
        constraints = [
            models.CheckConstraint(
                check=models.ExpressionWrapper(
                    models.Func('a', 'b', function='num_nonnulls'),
                    models.BooleanField()),
                name='num_nonnulls_check')]

This is of course missing the step where the result of num_nonnulls() is compared to some constant integer. I tried defining a function to do this:

def equals(a, b):
    return models.Func(a, b, template='%(expressions[0])s = %(expressions[1])s')

But this doesn't work because the template argument expressions is (I think) a string (and %-template strings don't have this syntax to extract parts of an argument, I think).

Where do I go from here?

I'd like to find a solution that allows me to use arbitrary expressions supported by the Django ORM and also compare these expressions with other expressions or constants using an equality or inequality relation (e.g. = or <=).

score 2 · Accepted Answer · answered May 25 '22 at 16:04

As of Django 4 this is possible, see this changelog entry:

Lookup expressions may now be used in QuerySet annotations, aggregations, and directly in filters.

This should fit your example.

from django.db import models
from django.db.models.constraints import CheckConstraint
from django.db.models.expressions import Func
from django.db.models import Value
from django.db.models.fields import IntegerField, TextField


class Foo(models.Model):
    a = models.TextField(null=True)
    b = models.IntegerField(null=True)

    class Meta:
        constraints = [
            CheckConstraint(
                name='num_nonnulls_check',
                check=Exact(
                    lhs=Func('a', 'b', function='num_nonnulls', output_field=IntegerField()),
                    rhs=Value(1),
                ),
            )
        ]

Compare expression with constant in CHECK constraint

1 Answers1