Opened 4 years ago

Last modified 3 years ago

#32858 closed Bug

Syntax error when using an index transform on an ArrayField in an ExclusionConstraint — at Initial Version

Reported by: Lucidiot Owned by: nobody
Component: Database layer (models, ORM) Version: 3.2
Severity: Normal Keywords:
Cc: Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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 = models.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.

Change History (0)

Note: See TracTickets for help on using tickets.
Back to Top