Postgresql migration fails when changing a CharField to a TimeField

[Dirk Uys]

The code from the migration is as follows:

migrations.AlterField(
    model_name='studygroup',
    name='time',
    field=models.TimeField(),
),

The field used to be a models.CharField()

The error follows:

django.db.utils.ProgrammingError: column "time" cannot be cast automatically to type time without time zone
HINT:  Specify a USING expression to perform the conversion

I can manually fix this using SQL:

ALTER TABLE "studygroups_studygroup" ALTER COLUMN "time" TYPE time USING "time"::time;

I don't have code with the problem isolated, but this project shows the problem when configured to use postgres. ​http://github.com/p2pu/knight-app/.

[Simon Charette]

I'm not sure this qualify as a bug or an enhancement but a simple fix would be to set

DatabaseSchemaEditor.sql_alter_column_type = "ALTER COLUMN %(column)s TYPE %(type)s USING %(column)s::%(type)s".

I'll submit a tentative PR to run the full test suite against CI before adding a test.

[Simon Charette]

Not sure if this is something we want to backport to 1.8.x but the added test seems to be passing with the proposed changes.

[Markus Holtermann]

I don't thing this bug justifies for backport to 1.8. It's the same behavior on 1.7. Use AddField, RunSQL/RunPython, RemoveField, RenameField for the desired outcome.

That said, there is/was a ticket for VARCHAR/INTEGER conversation on a PK, I think. Do you mind investing that one too. Maybe they can both be closed by this patch?

[Simon Charette]

Replying to MarkusH:

That said, there is/was a ticket for VARCHAR/INTEGER conversation on a PK, I think. Do you mind investing that one too. Maybe they can both be closed by this patch?

I'd like to but I can't find it by searching for tickets with the Migration component.

[Tim Graham]

#24264 looks like a suspect.

[Simon Charette]

Markus, I'm not sure how I should proceed, do you want me to add an additional test that makes sure an integer field can be converted to a char one? It looks like it's already working from the tests and doesn't require an explicit USING.

[Simon Charette <charette.s@…>]

In 73040e58:

Fixed #25002 -- Used PostgreSQL column type alteration USING clause.

Thanks to Dirk Uys for the report.

[Tim Graham]

Error on Oracle for the new test is ORA-01758: table must be empty to add mandatory (NOT NULL) column.

[Simon Charette]

Created a tentative ​PR based on error message.

[Tim Graham]

Test still doesn't pass.

[Simon Charette]

Tests are passing on Oracle.

[Simon Charette <charette.s@…>]

In bdb382b:

Refs #25002 -- Supported textual to temporal column alteration on Oracle.

Thanks to Tim Graham for the report and Shai Berger for the review.

[Simon Charette]

Merged the patch marked as RFC by Shai on Github.