Multiple db constraints in Django model not working

Question

I'm having issues getting multiple constraints to work (in postgres, if it matters). Here is the Meta:

class Meta:
    constraints = [
        CheckConstraint(
            check=(Q(target_value__gt=0) & ~Q(target_metric=DEFAULT)) |
                  (Q(enabled=False) |
                   Q(status=IMPORTING) |
                   Q(status=IMPORT_FAILURE)),
            name="positive_target_value",
        )
    ]

It would seem straightforward enough to me, but it ends up not working. Here is the generated SQL (pulled from the db itself):

target_value > 0.0::double precision AND NOT target_metric::text = 'DEFAULT'::text OR enabled = false OR status = 20 OR status = 30

And here is proof that it should actually work (pulled from a django console, where the conditions are copied from the migration file):

Folder.objects.filter(models.Q(models.Q(('target_value__gt', 0), models.Q(_negated=True, target_metric='DEFAULT')), ('enabled', False), ('status', 20), ('status', 30), _connector='OR'))
web_1       | Out[5]: <QuerySet [<Folder: aDifferentFolder>]>

Folder.objects.filter(models.Q(models.Q(('target_value__gt', 0), models.Q(_negated=True, target_metric='**SOMETHING DIFFERENT**')), ('enabled', False), ('status', 20), ('status', 30), _connector='OR'))
web_1       | Out[7]: <QuerySet [< Folder: Folder aDifferentFolder >, < Folder: Folder thisFolderShouldNotHaveBeenCreted>]>

Anybody has any suggestion? Btw, I also tried switching the first condition with the second one, effectively putting the AND at the bottom of the query, but it didn't seem to have any effect.

Thanks a lot.

mccc · Accepted Answer · 2020-04-09 15:01:03Z

1

The culprit was that postgres returns true on constraint also if the value is null, so now I know.

answered Apr 9, 2020 at 15:01

mccc

2,4541 gold badge21 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Multiple db constraints in Django model not working

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related