Adding hash index support for exclusion constraint in PostgreSQL

[haki]

In PostgreSQL you can’t define a unique constraint using a hash index. However, you can enforce uniqueness using an exclusion constraint and a hash index. This is useful for large values that only use equality and that are not referenced by FKs (fields like UIDs, hashes, checksums, URLs, other large texts etc.).

However, the current implementation(​https://github.com/django/django/blob/4426b1a72dc289643e2ae8c190b8dc4b3a39daf7/django/contrib/postgres/constraints.py#L38) of ExclusionConstraint in django.contrib.postgres.constraints is limited to gist and spgist. It seems like the implementation haven’t changed much since it was added in 2019.

I made a local change to allow hash to the list of allowed index_types and generated this constraint:

from django.contrib.postgres.constraints import ExclusionConstraint

class ShortUrl(models.Model):
    class Meta:
        constraints = (
            ExclusionConstraint(
                index_type='hash',  # <--- this index type is currently unsupported
                expressions=[
                    (F('url'), '='),
                ],
                name='%(app_label)s_url_unique_hash',
            ),
        )

It produced the expected SQL:

ALTER TABLE "shorturl_shorturl" ADD CONSTRAINT "shorturl_url_unique_hash" EXCLUDE USING hash ("url" WITH =);

After applying the migration the constraint worked as expected.

[haki]

PR link ​https://github.com/django/django/pull/20462

[Simon Charette]

Patch is looking great except for the lack of graceful handling of covering indices which will require adjustments and additional tests.

[Mariusz Felisiak <felisiak.mariusz@…>]

In d618387:

Fixed #36827 -- Added support for exclusion constraints using Hash indexes on PostgreSQL.