id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
35275	Constraints validation fails on UniqueConstraint using OpClass	Mathieu Kniewallner	Mariusz Felisiak	"Adding a `UniqueConstraint` using PostgreSQL-specific `OpClass` on a model breaks constraints validation performed when calling `validate_constraints` on a model when using PostgreSQL.

=== Minimal reproducer
{{{#!python
from django.contrib.postgres.indexes import OpClass
from django.db import models
from django.db.models.functions import Lower


class Place(models.Model):
    name = models.CharField(max_length=255)

    class Meta:
        app_label = ""opclass_issue""
        constraints = [
            models.UniqueConstraint(
                OpClass(Lower(""name""), name=""text_pattern_ops""),
                name=""lower_name_uniq"",
            )
        ]


place = Place(name=""Narnia"")
    place.validate_constraints()
}}}

This leads to the following error:
{{{
django.db.utils.ProgrammingError: syntax error at or near ""text_pattern_ops""
LINE 1: ...place"" WHERE LOWER(""opclass_issue_place"".""name"") text_patte...
}}}

Full SQL query that is generated:
{{{#!sql
SELECT 1 AS ""a"" FROM ""opclass_issue_place"" WHERE LOWER(""opclass_issue_place"".""name"") text_pattern_ops = (LOWER('narnia') text_pattern_ops) LIMIT 1
}}}

Just in case, this happens even though `django.contrib.postgres` is correctly installed in `INSTALLED_APPS`, so the issue is not related to that.

I've also created a test and adapted the CI to run it in https://github.com/backmarket-oss/django/pull/2/files, which leads to https://github.com/backmarket-oss/django/actions/runs/8171237603/job/22339033423?pr=2 showing the failure.

=== Potential root cause

`OpClass` wrapper should only make sense when creating the constraint, and should not be used when validating the constraint, as this leads to an invalid SQL query.

Looking at the code for the PostgreSQL specific `ExclusionConstraint`, a similar conclusion was reached, and `OpClass` is explicitly removed in `validate` method: https://github.com/django/django/blob/4426b1a72dc289643e2ae8c190b8dc4b3a39daf7/django/contrib/postgres/constraints.py#L211-L216.

=== Potential fix

Applying a similar fix as the one above in `UniqueConstraint`, showcased in https://github.com/backmarket-oss/django/pull/1/files applied on top of the other PR above, leads to https://github.com/backmarket-oss/django/actions/runs/8171242801/job/22339050847?pr=1 where the added test is now passing.

If you think that this is the correct fix to apply, or have a lead for another fix, I'd be happy to make a proper pull request targeting Django repository."	Bug	closed	Database layer (models, ORM)	dev	Normal	fixed		Gagaro	Ready for checkin	1	0	0	0	0	0