Cast() to DecimalField() raises DataError.

[Charles Cunningham]

I'm trying to create a functional index based on a newly created field, but keep getting the following error when migrations are run (using postgres):

psycopg2.errors.InvalidTextRepresentation: invalid input syntax for type integer: "none"

The relevant code (creating a new field "registration_volume" and a functional index "raw_demand" which should equal registration_volume/capacity if [capacity is not null and capacity>0], capacity being another field of the model), in a model called "Section":

    registration_volume = models.PositiveIntegerField(
        default=0, help_text="The number of active PCA registrations watching this section."
    )
    class Meta:
        indexes = [
            Index(
                Case(
                    When(
                        Q(capacity__isnull=False) & Q(capacity__gt=0),
                        then=(
                            Cast("registration_volume", models.DecimalField())
                            / Cast("capacity", models.DecimalField())
                        ),
                    ),
                    default=None,
                    output_field=models.DecimalField(null=True, blank=True),
                ),
                name="raw_demand",
            ),
        ]

The auto-generated migration:

class Migration(migrations.Migration):

    dependencies = [
        ("courses", "0031_userprofile_push_notifications"),
    ]

    operations = [
        migrations.AddField(
            model_name="section",
            name="registration_volume",
            field=models.PositiveIntegerField(
                default=0, help_text="The number of active PCA registrations watching this section."
            ),
        ),
        migrations.AddIndex(
            model_name="section",
            index=models.Index(
                django.db.models.expressions.Case(
                    django.db.models.expressions.When(
                        models.Q(("capacity__isnull", False), ("capacity__gt", 0)),
                        then=django.db.models.expressions.CombinedExpression(
                            django.db.models.functions.comparison.Cast(
                                "registration_volume", models.DecimalField()
                            ),
                            "/",
                            django.db.models.functions.comparison.Cast(
                                "capacity", models.DecimalField()
                            ),
                        ),
                    ),
                    default=None,
                    output_field=models.DecimalField(blank=True, null=True),
                ),
                name="raw_demand",
            ),
        ),
    ]

It seems to have an issue with this part of the AddIndex migration inside models.Index(...):

django.db.models.functions.comparison.Cast(
    "registration_volume", models.DecimalField()
)

The full traceback:

Run cd backend
cd backend
pipenv run coverage run --concurrency=multiprocessing manage.py test --settings=PennCourses.settings.ci --parallel
pipenv run coverage combine
shell: sh -e {0}
env: DATABASE_URL: ***postgres:5432/postgres

Creating test database for alias 'default'...
Traceback (most recent call last):
  File "/github/home/.local/share/virtualenvs/backend-6EUeMQ8O/lib/python3.8/site-packages/django/db/backends/utils.py", line 82, in _execute
    return self.cursor.execute(sql)
psycopg2.errors.InvalidTextRepresentation: invalid input syntax for type integer: "none"
LINE 1: ...apacity" > 0) THEN (CAST("registration_volume" AS numeric(No...
                                                             ^


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "manage.py", line 21, in <module>
    main()
  File "manage.py", line 17, in main
    execute_from_command_line(sys.argv)
  File "/github/home/.local/share/virtualenvs/backend-6EUeMQ8O/lib/python3.8/site-packages/django/core/management/__init__.py", line 419, in execute_from_command_line
    utility.execute()
  File "/github/home/.local/share/virtualenvs/backend-6EUeMQ8O/lib/python3.8/site-packages/django/core/management/__init__.py", line 413, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/github/home/.local/share/virtualenvs/backend-6EUeMQ8O/lib/python3.8/site-packages/django/core/management/commands/test.py", line 23, in run_from_argv
    super().run_from_argv(argv)
  File "/github/home/.local/share/virtualenvs/backend-6EUeMQ8O/lib/python3.8/site-packages/django/core/management/base.py", line 354, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/github/home/.local/share/virtualenvs/backend-6EUeMQ8O/lib/python3.8/site-packages/django/core/management/base.py", line 398, in execute
    output = self.handle(*args, **options)
  File "/github/home/.local/share/virtualenvs/backend-6EUeMQ8O/lib/python3.8/site-packages/django/core/management/commands/test.py", line 55, in handle
    failures = test_runner.run_tests(test_labels)
  File "/github/home/.local/share/virtualenvs/backend-6EUeMQ8O/lib/python3.8/site-packages/django/test/runner.py", line 723, in run_tests
    old_config = self.setup_databases(aliases=databases)
  File "/github/home/.local/share/virtualenvs/backend-6EUeMQ8O/lib/python3.8/site-packages/django/test/runner.py", line 641, in setup_databases
    return _setup_databases(
  File "/github/home/.local/share/virtualenvs/backend-6EUeMQ8O/lib/python3.8/site-packages/django/test/utils.py", line 179, in setup_databases
    connection.creation.create_test_db(
  File "/github/home/.local/share/virtualenvs/backend-6EUeMQ8O/lib/python3.8/site-packages/django/db/backends/base/creation.py", line 74, in create_test_db
    call_command(
  File "/github/home/.local/share/virtualenvs/backend-6EUeMQ8O/lib/python3.8/site-packages/django/core/management/__init__.py", line 181, in call_command
    return command.execute(*args, **defaults)
  File "/github/home/.local/share/virtualenvs/backend-6EUeMQ8O/lib/python3.8/site-packages/django/core/management/base.py", line 398, in execute
    output = self.handle(*args, **options)
  File "/github/home/.local/share/virtualenvs/backend-6EUeMQ8O/lib/python3.8/site-packages/django/core/management/base.py", line 89, in wrapped
    res = handle_func(*args, **kwargs)
  File "/github/home/.local/share/virtualenvs/backend-6EUeMQ8O/lib/python3.8/site-packages/django/core/management/commands/migrate.py", line 244, in handle
    post_migrate_state = executor.migrate(
  File "/github/home/.local/share/virtualenvs/backend-6EUeMQ8O/lib/python3.8/site-packages/django/db/migrations/executor.py", line 117, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/github/home/.local/share/virtualenvs/backend-6EUeMQ8O/lib/python3.8/site-packages/django/db/migrations/executor.py", line 147, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "/github/home/.local/share/virtualenvs/backend-6EUeMQ8O/lib/python3.8/site-packages/django/db/migrations/executor.py", line 227, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/github/home/.local/share/virtualenvs/backend-6EUeMQ8O/lib/python3.8/site-packages/django/db/migrations/migration.py", line 126, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "/github/home/.local/share/virtualenvs/backend-6EUeMQ8O/lib/python3.8/site-packages/django/db/migrations/operations/models.py", line 761, in database_forwards
    schema_editor.add_index(model, self.index)
  File "/github/home/.local/share/virtualenvs/backend-6EUeMQ8O/lib/python3.8/site-packages/django/db/backends/postgresql/schema.py", line 218, in add_index
    self.execute(index.create_sql(model, self, concurrently=concurrently), params=None)
  File "/github/home/.local/share/virtualenvs/backend-6EUeMQ8O/lib/python3.8/site-packages/django/db/backends/base/schema.py", line 145, in execute
    cursor.execute(sql, params)
  File "/github/home/.local/share/virtualenvs/backend-6EUeMQ8O/lib/python3.8/site-packages/sentry_sdk/integrations/django/__init__.py", line 499, in execute
    return real_execute(self, sql, params)
  File "/github/home/.local/share/virtualenvs/backend-6EUeMQ8O/lib/python3.8/site-packages/django/db/backends/utils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/github/home/.local/share/virtualenvs/backend-6EUeMQ8O/lib/python3.8/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/github/home/.local/share/virtualenvs/backend-6EUeMQ8O/lib/python3.8/site-packages/django/db/backends/utils.py", line 82, in _execute
    return self.cursor.execute(sql)
  File "/github/home/.local/share/virtualenvs/backend-6EUeMQ8O/lib/python3.8/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/github/home/.local/share/virtualenvs/backend-6EUeMQ8O/lib/python3.8/site-packages/django/db/backends/utils.py", line 82, in _execute
    return self.cursor.execute(sql)
django.db.utils.DataError: invalid input syntax for type integer: "none"
LINE 1: ...apacity" > 0) THEN (CAST("registration_volume" AS numeric(No...

[Mariusz Felisiak]

You cannot Cast() to DecimalField, or use it in output_field, without providing decimal_places and max_digits arguments, e.g. DecimalField(decimal_places=2, max_digits=10) works for me. Also, as far as I'm aware there is no need to use Cast() in your case:

    ...
    then=F("registration_volume") / F("capacity"),
    ...

[Charles Cunningham]

Replying to Mariusz Felisiak:

You cannot Cast() to DecimalField, or use it in output_field, without providing decimal_places and max_digits arguments, e.g. DecimalField(decimal_places=2, max_digits=10) works for me. Also, as far as I'm aware there is no need to use Cast() in your case:

    ...
    then=F("registration_volume") / F("capacity"),
    ...

Wouldn't that result in integer division? Or if the output_field is DecimalField, will it do decimal division?

[Mariusz Felisiak]

Replying to Charles Cunningham:

Wouldn't that result in integer division?

True, it depends on a database but on PostgreSQL you need to Cast() at least one of them.