Changing output_field for GeneratedField leads to ProgrammingError with Postgres 16.5+

[Ryan Schave]

Consider the following model and assume the initial migration has been applied:

class Order(models.Model):  
    order_no = models.IntegerField()  
    item_no = models.CharField(max_length=25)  
    qty = models.IntegerField()  
    cost = models.DecimalField(max_digits=10, decimal_places=2)  
    total_cost = models.GeneratedField(  
        expression=F("cost") * F("qty"),  
        output_field=models.BigIntegerField(),  
        db_persist=True,  
    )

During a code review we determined the output field should be a Decimal field and the field was modified as follows:

total_cost = models.GeneratedField(  
    expression=F("cost") * F("qty"),  
    output_field=models.DecimalField(decimal_places=2, max_digits=16),  
    db_persist=True,  
)

And a new migration was generated:

migrations.AlterField(  
    model_name='order',  
    name='total_cost',  
    field=models.GeneratedField(db_persist=True, expression=django.db.models.expressions.CombinedExpression(models.F('cost'), '*', models.F('qty')), output_field=models.DecimalField(decimal_places=2, max_digits=16)),  
),

In Postgres 16.4 and earlier, this migration is applied without error. (I'm aware that the value of the total_cost field is not recomputed for existing records when this migration is applied.).

Starting with Postgres 16.5 and up, this migration fails with the following error:

psycopg2.errors.InvalidColumnDefinition: cannot specify USING when altering type of generated column
DETAIL:  Column "total_cost" is a generated column.
...
django.db.utils.ProgrammingError: cannot specify USING when altering type of generated column

This appears to be a result of the following change in Postgres 16.5 (​release notes):

Disallow a USING clause when altering the type of a generated column (Peter Eisentraut) [§](​https://postgr.es/c/5867ee005)
A generated column already has an expression specifying the column contents, so including USING doesn't make sense.

The Django ​documentation for GeneratedField makes it clear that

There are many database-specific restrictions on generated fields that Django doesn’t validate and the database may raise an error

For this reason, I almost didn't open a ticket. However, there is logic in db/backends/base/schema.py that checks if the expression of a GeneratedField changed. Consider this migration (which changes the expression from multiplication to addition):

migrations.AlterField(  
    model_name='order',  
    name='total_cost',  
    field=models.GeneratedField(db_persist=True, expression=django.db.models.expressions.CombinedExpression(models.F('cost'), '+', models.F('qty')), output_field=models.BigIntegerField()),  
),

Attempting to apply this migration will raise the following error (even in Postgres 16.4):

ValueError: Modifying GeneratedFields is not supported - the field sales.Order.total_cost must be removed and re-added with the new definition.

This error is more helpful. It explains the problem better and even suggests a workaround.

Should we throw a similar error if the output_field of a GeneratedField is changed? Or add a tip to the documentation?

The above was tested with:
Django version 5.1.3
psycopg2 version 2.9.10
Postgres versions: 16.4, 16.5, 16.6, 17.0, and 17.2

[Sarah Boyce]

Thank you for the detailed report

Replicated the error

tests/migrations/test_operations.py

@@ -6185 +6185 @@
         with self.assertRaisesMessage(ValueError, msg):
             self.apply_operations(app_label, project_state, operations)
 
+    @skipUnlessDBFeature("supports_stored_generated_columns")
+    def test_generated_field_changes_output_field(self):
+        app_label = "test_gfcof"
+        operation = migrations.AddField(
+            "Pony",
+            "modified_pink",
+            models.GeneratedField(
+                expression=F("pink") + F("pink"),
+                output_field=models.IntegerField(),
+                db_persist=True,
+            ),
+        )
+        project_state, new_state = self.make_test_state(app_label, operation)
+        # Add generated column.
+        with connection.schema_editor() as editor:
+            operation.database_forwards(app_label, editor, project_state, new_state)
+        # Update output_field used in the generated field.
+        operations = [
+            migrations.AlterField(
+                "Pony",
+                "modified_pink",
+                models.GeneratedField(
+                    expression=F("pink") + F("pink"),
+                    output_field=models.DecimalField(decimal_places=2, max_digits=16),
+                    db_persist=True,
+                ),
+            ),
+        ]
+        new_state = self.apply_operations(app_label, new_state, operations)
+        with connection.schema_editor() as editor:
+            operation.database_forwards(app_label, editor, project_state, new_state)
+
     def _test_add_generated_field(self, db_persist):
         app_label = "test_agf"

ValueError: Modifying GeneratedFields is not supported - the field sales.Order.total_cost must be removed and re-added with the new definition.

This error is more helpful. It explains the problem better and even suggests a workaround.

Having this error instead makes sense to me.
CC-ed a couple of other folks who might have thoughts

[Simon Charette]

Just to make sure before we commit to a solution.

Does Posgres still allows for generated field alterations but disallow that USING is not used? If that's the case it seems like a better solution would be to have generated field alteration continue to be supported simply not specify USING (which is effectively redundant with the column type) instead of disabling the feature?

django/db/backends/postgresql/schema.py

@@ -120 +120 @@
         return None
 
     def _using_sql(self, new_field, old_field):
+        if new_field.generated:
+            return ""
         using_sql = " USING %(column)s::%(type)s"
         new_internal_type = new_field.get_internal_type()
         old_internal_type = old_field.get_internal_type(

[Ryan Schave]

Replying to Simon Charette:

Just to make sure before we commit to a solution.

Does Posgres still allows for generated field alterations but disallow that USING is not used? If that's the case it seems like a better solution would be to have generated field alteration continue to be supported simply not specify USING (which is effectively redundant with the column type) instead of disabling the feature?

I found that I can rename the GeneratedField without any issues.

Renaming a field within the expression throws the ValueError:

ValueError: Modifying GeneratedFields is not supported - the field sales.Order.total_cost must be removed and re-added with the new definition.

This error appears to be raised by Django, so it's not even getting to Postgres.

[Simon Charette]

It seems to be latter as the above patch works with adjusted tests provided by Sarah

tests/migrations/test_operations.py

@@ -6135 +6135 @@
             operation.database_backwards(app_label, editor, new_state, project_state)
         self.assertColumnNotExists(f"{app_label}_pony", "modified_pink")
 
+    @skipUnlessDBFeature("supports_stored_generated_columns")
+    def test_generated_field_changes_output_field(self):
+        app_label = "test_gfcof"
+        operation = migrations.AddField(
+            "Pony",
+            "modified_pink",
+            models.GeneratedField(
+                expression=F("pink") + F("pink"),
+                output_field=models.IntegerField(),
+                db_persist=True,
+            ),
+        )
+        from_state, to_state = self.make_test_state(app_label, operation)
+        # Add generated column.
+        with connection.schema_editor() as editor:
+            operation.database_forwards(app_label, editor, from_state, to_state)
+        # Update output_field used in the generated field.
+        operation = migrations.AlterField(
+            "Pony",
+            "modified_pink",
+            models.GeneratedField(
+                expression=F("pink") + F("pink"),
+                output_field=models.DecimalField(decimal_places=2, max_digits=16),
+                db_persist=True,
+            ),
+        )
+        from_state = to_state.clone()
+        to_state = self.apply_operations(app_label, from_state, [operation])
+        with connection.schema_editor() as editor:
+            operation.database_forwards(app_label, editor, from_state, to_state)
+
     @skipUnlessDBFeature("supports_stored_generated_columns")
     def test_add_generated_field_stored(self):
         self._test_add_generated_field(db_persist=True)

[Simon Charette]

In summary what Postgres 16.5 changed is that you longer can specify USING on ALTER COLUMN for generated columns

ALTER TABLE "test_gfcof_pony"
ALTER COLUMN "modified_pink" TYPE numeric(16, 2) USING "modified_pink"::numeric(16, 2);

Which makes sense as USING is redundant with TYPE and could result in mismatches.

Well the solution appears to simply not specifying USING when dealing with GeneratedField alterations as suggested in comment:2

ALTER TABLE "test_gfcof_pony"
ALTER COLUMN "modified_pink" TYPE numeric(16, 2);