varchar_pattern_ops
indices created when using db_index=True
or unique=True
For the following model definition, django will create two varchar_pattern_ops
indices (for a_field
and another_field
). varchar_pattern_ops
indices are useful for LIKE
queries, but sometimes you don't plan on using them.
from django.db import models
class Something(models.Model):
a_field = models.CharField(unique=True)
another_field = models.CharField(db_index=True)
yet_another_field = models.CharField()
root@e812b0255180:/app# python manage.py makemigrations
Migrations for 'api':
api/migrations/0002_something.py
- Create model Something
root@e812b0255180:/app# python manage.py sqlmigrate api 0002_something
BEGIN;
--
-- Create model Something
--
CREATE TABLE "api_something" ("id" bigint NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, "a_field" varchar NOT NULL UNIQUE, "another_field" varchar NOT NULL, "yet_another_field" varchar NOT NULL);
CREATE INDEX "api_something_a_field_6e82347c_like" ON "api_something" ("a_field" varchar_pattern_ops);
CREATE INDEX "api_something_another_field_dd6f5906" ON "api_something" ("another_field");
CREATE INDEX "api_something_another_field_dd6f5906_like" ON "api_something" ("another_field" varchar_pattern_ops);
COMMIT;
How can you avoid those autogenerated varchar_pattern_ops
indices?. Use Meta
constraints
and indexes
, as seen below:
from django.db import models
class Something(models.Model):
a_field = models.CharField()
another_field = models.CharField()
yet_another_field = models.CharField()
class Meta:
constraints = (
models.UniqueConstraint(fields=(a_field,) name="a_field_uniq")
)
indexes = (
models.Index(fields=("another_field",)),
)
root@e812b0255180:/app# python manage.py makemigrations
Migrations for 'api':
api/migrations/0002_something_something_a_field_uniq.py
- Create model Something
- Create constraint a_field_uniq on model something
root@e812b0255180:/app# python manage.py sqlmigrate api 0002_something_something_a_field_uniq
BEGIN;
--
-- Create model Something
--
CREATE TABLE "api_something" ("id" bigint NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, "a_field" varchar NOT NULL, "another_field" varchar NOT NULL, "yet_another_field" varchar NOT NULL);
--
-- Create constraint a_field_uniq on model something
--
ALTER TABLE "api_something" ADD CONSTRAINT "a_field_uniq" UNIQUE ("a_field");
CREATE INDEX "api_somethi_another_a44b66_idx" ON "api_something" ("another_field");
COMMIT;