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 35336 Adding GeneratedField fails with ProgrammingError when using When on CharField Adrian Garcia Simon Charette "Forgive me if I am doing something incorrectly, but I cannot for the life of me add a GeneratedField to one of my models. To replicate this error, I created this test model: {{{ from django.db import models class TestModel(models.Model): description = models.TextField() }}} Running makemigrations/migrate, then modifying the model to add this `contains_heck` field: {{{ from django.db import models class TestModel(models.Model): description = models.TextField() contains_heck = models.GeneratedField( expression=models.Case( models.When(description__icontains=""HECK"", then=models.Value(True)), default=models.Value(False), output_field=models.BooleanField(), ), output_field=models.BooleanField(), db_persist=True, ) }}} Which generates this migration: {{{ # Generated by Django 5.0.3 on 2024-03-27 20:34 from django.db import migrations, models class Migration(migrations.Migration): dependencies = [ (""core"", ""00001_initial""), ] operations = [ migrations.AddField( model_name=""testmodel"", name=""contains_heck"", field=models.GeneratedField( db_persist=True, expression=models.Case( models.When(description__icontains=""HECK"", then=models.Value(True)), default=models.Value(False), output_field=models.BooleanField() ), output_field=models.BooleanField(), ), ), ] }}} And after running `python manage.py migrate` I get the following error: {{{ Operations to perform: Apply all migrations: admin, auth, consumer, contenttypes, core, db, sessions Running migrations: Applying core.0002_testmodel_contains_heck...Traceback (most recent call last): File ""/opt/project/app/manage.py"", line 24, in main() File ""/opt/project/app/manage.py"", line 20, in main execute_from_command_line(sys.argv) File ""/usr/local/lib/python3.11/site-packages/django/core/management/__init__.py"", line 442, in execute_from_command_line utility.execute() File ""/usr/local/lib/python3.11/site-packages/django/core/management/__init__.py"", line 436, in execute self.fetch_command(subcommand).run_from_argv(self.argv) File ""/usr/local/lib/python3.11/site-packages/django/core/management/base.py"", line 413, in run_from_argv self.execute(*args, **cmd_options) File ""/usr/local/lib/python3.11/site-packages/django/core/management/base.py"", line 459, in execute output = self.handle(*args, **options) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File ""/usr/local/lib/python3.11/site-packages/django/core/management/base.py"", line 107, in wrapper res = handle_func(*args, **kwargs) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File ""/usr/local/lib/python3.11/site-packages/django/core/management/commands/migrate.py"", line 356, in handle post_migrate_state = executor.migrate( ^^^^^^^^^^^^^^^^^ File ""/usr/local/lib/python3.11/site-packages/django/db/migrations/executor.py"", line 135, in migrate state = self._migrate_all_forwards( ^^^^^^^^^^^^^^^^^^^^^^^^^^^ File ""/usr/local/lib/python3.11/site-packages/django/db/migrations/executor.py"", line 167, in _migrate_all_forwards state = self.apply_migration( ^^^^^^^^^^^^^^^^^^^^^ File ""/usr/local/lib/python3.11/site-packages/django/db/migrations/executor.py"", line 252, in apply_migration state = migration.apply(state, schema_editor) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File ""/usr/local/lib/python3.11/site-packages/django/db/migrations/migration.py"", line 132, in apply operation.database_forwards( File ""/usr/local/lib/python3.11/site-packages/django/db/migrations/operations/fields.py"", line 108, in database_forwards schema_editor.add_field( File ""/usr/local/lib/python3.11/site-packages/django/db/backends/base/schema.py"", line 750, in add_field self.execute(sql, params) File ""/usr/local/lib/python3.11/site-packages/django/db/backends/postgresql/schema.py"", line 46, in execute sql = self.connection.ops.compose_sql(str(sql), params) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File ""/usr/local/lib/python3.11/site-packages/django/db/backends/postgresql/operations.py"", line 195, in compose_sql return mogrify(sql, params, self.connection) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File ""/usr/local/lib/python3.11/site-packages/django/db/backends/postgresql/psycopg_any.py"", line 22, in mogrify return ClientCursor(cursor.connection).mogrify(sql, params) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File ""/usr/local/lib/python3.11/site-packages/psycopg/client_cursor.py"", line 40, in mogrify pgq = self._convert_query(query, params) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File ""/usr/local/lib/python3.11/site-packages/psycopg/client_cursor.py"", line 79, in _convert_query pgq.convert(query, params) File ""/usr/local/lib/python3.11/site-packages/psycopg/_queries.py"", line 208, in convert (self.template, self._order, self._parts) = f(bquery, self._encoding) ^^^^^^^^^^^^^^^^^^^^^^^^^ File ""/usr/local/lib/python3.11/site-packages/psycopg/_queries.py"", line 242, in _query2pg_client_nocache parts = _split_query(query, encoding, collapse_double_percent=False) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File ""/usr/local/lib/python3.11/site-packages/psycopg/_queries.py"", line 388, in _split_query raise e.ProgrammingError( psycopg.ProgrammingError: only '%s', '%b', '%t' are allowed as placeholders, got '%H' }}} I took a look with the debugger and I think either Django is incorrectly passing the values to psycopg, or psycopg isn't splitting this SQL statement correctly. I say this because when `_split_query` tries to split the query, it ends up matching and consuming the first character of each Value: {{{ [ ('ALTER TABLE ""core_testmodel"" ADD COLUMN ""contains_heck"" boolean GENERATED ALWAYS AS (CASE WHEN UPPER(""description""::text) LIKE UPPER(\'', """"), (""ECK"", ''), ("") THEN true ELSE false END) STORED"", ""None""), ] }}} Switching to `models.When(description__icontains=models.Value(""HECK""), ...) yields a similar error except it's complaining about `%'` instead of `%H`. If the `contains_heck` field is created at the same time as the rest of the model, everything works in either the `description__icontains=""HECK""` or `description__icontains=models.Value(""HECK"")` configuration. **Versions:** * Postgres 14.9 * Python 3.11.2 * Django 5.0.3 * psycopg[binary] 3.1.18" Bug closed Database layer (models, ORM) 5.0 Release blocker fixed postgres, generatedfield, contains Ready for checkin 1 0 0 0 0 0