Opened 3 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.