Opened 9 years ago

Closed 9 years ago

Last modified 7 years ago

#25002 closed Bug (fixed)

Postgresql migration fails when changing a CharField to a TimeField

Reported by: Dirk Uys Owned by: Simon Charette
Component: Migrations Version: dev
Severity: Normal Keywords: migrations
Cc: Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by 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/.

Change History (17)

comment:1 by Dirk Uys, 9 years ago

Component: UncategorizedDatabase layer (models, ORM)
Keywords: migrations added
Type: UncategorizedBug

comment:2 by Dirk Uys, 9 years ago

Description: modified (diff)

comment:3 by Simon Charette, 9 years ago

Easy pickings: set
Owner: changed from nobody to Simon Charette
Status: newassigned
Triage Stage: UnreviewedAccepted

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.

comment:4 by Simon Charette, 9 years ago

Easy pickings: unset
Has patch: set

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.

comment:5 by Markus Holtermann, 9 years ago

Version: 1.8master

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?

in reply to:  5 comment:6 by Simon Charette, 9 years ago

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.

comment:7 by Tim Graham, 9 years ago

#24264 looks like a suspect.

comment:8 by Simon Charette, 9 years ago

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.

comment:9 by Markus Holtermann, 9 years ago

Triage Stage: AcceptedReady for checkin

comment:10 by Simon Charette <charette.s@…>, 9 years ago

Resolution: fixed
Status: assignedclosed

In 73040e58:

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

Thanks to Dirk Uys for the report.

comment:11 by Tim Graham, 9 years ago

Has patch: unset
Resolution: fixed
Status: closednew
Triage Stage: Ready for checkinAccepted

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

comment:12 by Simon Charette, 9 years ago

Has patch: set

Created a tentative PR based on error message.

comment:13 by Tim Graham, 9 years ago

Patch needs improvement: set

Test still doesn't pass.

comment:14 by Simon Charette, 9 years ago

Patch needs improvement: unset

Tests are passing on Oracle.

comment:15 by Simon Charette <charette.s@…>, 9 years ago

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.

comment:16 by Simon Charette, 9 years ago

Resolution: fixed
Status: newclosed
Triage Stage: AcceptedReady for checkin

Merged the patch marked as RFC by Shai on Github.

comment:17 by Tim Graham, 7 years ago

Component: Database layer (models, ORM)Migrations
Note: See TracTickets for help on using tickets.
Back to Top