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 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.
What will it take to add hash support for ExclusionConstraint in Django?
