Changing CharField type referenced by ForeignKey crashes on PostgreSQL.

[Chris]

When a ForeignKey points to CharField, and the CharField is then changed to e.g. an AutoField, the migration fails with:

psycopg2.errors.DatatypeMismatch: operator class "varchar_pattern_ops" does not accept data type bigint

The reason seems to be that the original ForeignKey column in the database was a varchar, and Django created a varchar_pattern_ops index for it (e.g. one of those ..._like indexes). Changing the column type to bigint is not accepted by this index.

Manually dropping the index before attempting to run the migrations results in the migration being performed successfully.

Performing the same set of migrations on SQLite and on MySQL does not result in an error, it only fails on PostgreSQL.

---

Test case:

• Use models.py:

  class ModelA(models.Model):
      # Custom CharField primary key
      id = models.CharField(max_length=10, primary_key=True)
  
  class ModelB(models.Model):
      modela = models.ForeignKey(ModelA, on_delete=models.CASCADE)
  

• Run makemigrations and migrate commands
• Alter ModelA:

  class ModelA(models.Model):
      # No more custom primary key
      pass
  

• Run makemigrations and migrate commands again
• Observe error on PostgreSQL, success on SQLite and MySQL
• Manually DROP the <applabel>_modelb_modela_id_<...>_like index in PostgreSQL
• Again run the migrate command
• Now the migration succeeds

---

The problem is apparently that the BaseDatabaseSchemaEditor._alter_field() method simply calls _alter_column_type_sql() or _alter_column_collation_sql() to directly change the column type of relations pointing to the field. These methods do not generate any additional SQL related to indexes etc as the normal (PostgreSQL version of) _alter_field method would under similar circumstances. Note that the ..._like index for the primary key itself _is_ dropped.

Similarly also, if the primary key field is changed in the other direction, e.g. from AutoField to CharField, the ..._like index is not added to the existing ForeignKey, it is only added for the primary key field, as it normally is when an integer field is changed to a text field.

Possibly related issues: #27338 #27860

Reported for version 3.2 but also observed on 4.1.

[David Sanders]

PR: ​https://github.com/django/django/pull/16087

[Mariusz Felisiak <felisiak.mariusz@…>]

In 9f8c9948:

Fixed #34027 -- Fixed migrations crash when altering type of char/text fields referenced by foreign key on PostgreSQL.