Syntax error when using an index transform on an ArrayField in an ExclusionConstraint

[Lucidiot]

I am trying to create a GIST exclusion constraint on a PostgreSQL 12 database. One of the fields is an ArrayField, and I want to apply the constraint on its first item using my_array__0. When doing so, I get a syntax error from Postgres.

from django.db import models
from django.contrib.postgres.constraints import ExclusionConstraint
from django.contrib.postgres.fields import ArrayField, RangeOperators


class MyModel(models.Model):

    my_array = ArrayField()

    class Meta:
        constraints = (
            ExclusionConstraint(
                name='foo',
                expressions=[
                    ('my_array__0', RangeOperators.EQUAL),
                ]
            )
        )

django.db.utils.ProgrammingError: syntax error at or near "WITH"
LINE 1: ..." ADD CONSTRAINT "foo" EXCLUDE USING GIST ("my_array"[1] WITH =)
                                                                    ^

It seems a similar issue had occurred with casts (#32392). The ​docs for ALTER TABLE mention this syntax for expressions in an EXCLUDE constraint:

exclude_element in an EXCLUDE constraint is:

{ column_name | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]

This implies that parentheses around anything that is not a column name are mandatory. Maybe a more generic fix could be made to detect if something is only a column name, or just to add parentheses all the time? EXCLUDE USING GIST (("my_array"[1]) WITH =) works without any trouble.

[Simon Charette]

The proposed solution makes sense to me, looks like #32392 was just a workaround in the end.

It should be easy enough to add the conditional f'({sql})' wrapping if isinstance(expression, Col) as you've mentioned (Col is django.db.models.expressions.Col).

​https://github.com/django/django/blob/225d96533a8e05debd402a2bfe566487cc27d95f/django/contrib/postgres/constraints.py#L72-L83

Would you be able to provide a patch/PR?

I also wonder if we should just revert the non-test bits of fdfbc66331292def201c9344e3cd29fbcbcd076a since it might just be hiding other instances of this problem.

[Lucidiot]

I managed to make a ​PR! I did not however look into reverting the previous fix.

[Simon Charette]

That's looking great thanks! Lets leave the decision of reverting the previous fix to the mergers.

[Mariusz Felisiak <felisiak.mariusz@…>]

In b69b0c3f:

Fixed #32858 -- Fixed ExclusionConstraint crash with index transforms in expressions.

[Mariusz Felisiak <felisiak.mariusz@…>]

In e07609a0:

Refs #32858, Refs #32392 -- Restored using :: shortcut syntax in Cast() on PostgreSQL.

This partly reverts commit fdfbc66331292def201c9344e3cd29fbcbcd076a
unnecessary since b69b0c3fe871167a0ca01bb439508e335143801f.