Changes between Initial Version and Version 3 of Ticket #23609


Ignore:
Timestamp:
Oct 6, 2014, 7:59:54 PM (10 years ago)
Author:
Markus Holtermann
Comment:

Updated description after discussion with jarshwah and charettes

Legend:

Unmodified
Added
Removed
Modified

  • Ticket #23609

    • Property Has patch set
    • Property Patch needs improvement set
    • Property Status new → assigned
    • Property Summary Migration fails on SQLite when removing a NULL constraint → Migration fails when removing a NULL constraint

  • Ticket #23609 – Description

    initial v3  
    1 If a model has e.g `IntegerField(null=True)` and a migration changes it to `IntegerField(default=42)` the migration fails on SQLite due to the way the new table is created (create new table and copy old data into it). The respective SQL code Django produces is:
     1If a model has e.g `IntegerField(null=True)` and a migration changes it to `IntegerField(default=42)` the migration fails on SQLite due to the way the new table is created (create new table and copy old data into it) and PostgreSQL due to no default being used for existing rows. The respective SQL code Django produces is:
    22
    33{{{#!sql
     4-- SQLite
    45CREATE TABLE "testapp_foo__new" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "d" integer NOT NULL);
    56INSERT INTO "testapp_foo__new" ("id", "d") SELECT "id", "d" FROM "testapp_foo";
     7DROP TABLE "testapp_foo";
     8ALTER TABLE "testapp_foo__new" RENAME TO "testapp_foo";
     9
     10-- PostgreSQL
     11ALTER TABLE "testapp_foo" ALTER COLUMN "d" SET DEFAULT 42, ALTER COLUMN "d" SET NOT NULL;
     12ALTER TABLE "testapp_foo" ALTER COLUMN "d" DROP DEFAULT;
    613}}}
    714
    8 The first idea for a patch was to do a two-step insert, first all rows w/o NULL values and second those w/ NULL and adding a the default explicitly. This would involve some more code changes. apollo13 came up with the idea to use a `CASE WHEN` SQL syntax which, as it turns out, seems quite nice:
     15The first idea for a patch was to do a two-step insert, first all rows w/o NULL values and second those w/ NULL and adding a the default explicitly. This would involve some more code changes. apollo13 came up with the idea to use a `CASE WHEN` SQL syntax which, as it turns out, seems quite nice. jarshwah came up with `coalesce("d", 42)`.
    916
    1017{{{#!sql
     18-- SQLite
    1119CREATE TABLE "testapp_foo__new" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "d" integer NOT NULL);
    12 INSERT INTO "testapp_foo__new" ("id", "d") SELECT "id", CASE WHEN "d" IS NULL THEN 42 ELSE "d" END FROM "testapp_foo";
     20INSERT INTO "testapp_foo__new" ("id", "d") SELECT "id", coalesc("d", 42) FROM "testapp_foo";
     21DROP TABLE "testapp_foo";
     22ALTER TABLE "testapp_foo__new" RENAME TO "testapp_foo";
     23
     24-- PostgreSQL
     25ALTER TABLE "testapp_foo" ALTER COLUMN "d" SET DEFAULT 42;
     26UPDATE "testapp_foo" SET "d" = 42 WHERE "d" IS NULL;
     27ALTER TABLE "testapp_foo" ALTER COLUMN "d" SET NOT NULL;
     28ALTER TABLE "testapp_foo" ALTER COLUMN "d" DROP DEFAULT;
    1329}}}
Back to Top