id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
28646	"Migration calls ""CREATE INDEX"" when one already exists when 'unique' field attribute is added (PostgreSQL)"	Hari - 何瑞理		"PostgreSQL migration automatically creates an index for fields that set `db_index=True`. An example is `SlugField`, which sets this property implicitly. Thereafter when the `unique=True` property is added to the field the resultant migration script generates an AlterField object to apply this unique attribute. The schema editor then incorrectly detects this new `unique=True` attribute to indicate the need to create a like index statement on the field which causes an error as it conflicts with the already existing index.

The offending piece of code seems to be at django/db/backends/postgresql/schema.py:117. 

{{{
 if ((not (old_field.db_index or old_field.unique) and new_field.db_index) or
                (not old_field.unique and new_field.unique)):
            like_index_statement = self._create_like_index_sql(model, new_field)
            if like_index_statement is not None:
                self.execute(like_index_statement)
}}}

If it's changed as:
{{{
 if (not (old_field.db_index or old_field.unique) and (new_field.db_index or new_field.unique)):
            like_index_statement = self._create_like_index_sql(model, new_field)
            if like_index_statement is not None:
                self.execute(like_index_statement)
}}}

this error won't occur. 

PostgreSQL 9.5 introduces `IF NOT EXISTS` to the `CREATE INDEX` statement which if added to the schema template can also address this problem without changing the above logic.

I encountered the problem with `SlugField()` which implicitly sets `db_index=True` on PostgreSQL 9.4.

Interestingly, I only discovered this when I used `django-tenant-schemas` which adds a thin layer on top of the default Database router setting the schema search path before handing over the work to the default router. With a vanilla Django installation using default router, the second call to create a like index does not throw an error. However, upon reviewing the code, the logic does look incorrect. Also issuing the duplicate SQL statement in PostgreSQL console also throws an error.

I'm still investigating to see if this there's more to this than what I just described."	Bug	new	Migrations	1.11	Normal		postgresql,migration,index,#djangocph	Tomer Chachamu bcail	Accepted	1	0	0	1	0	0