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 23609,Migration fails when removing a NULL constraint,Markus Holtermann,Markus Holtermann,"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) and PostgreSQL due to no default being used for existing rows. The respective SQL code Django produces is: {{{#!sql -- SQLite CREATE TABLE ""testapp_foo__new"" (""id"" integer NOT NULL PRIMARY KEY AUTOINCREMENT, ""d"" integer NOT NULL); INSERT INTO ""testapp_foo__new"" (""id"", ""d"") SELECT ""id"", ""d"" FROM ""testapp_foo""; DROP TABLE ""testapp_foo""; ALTER TABLE ""testapp_foo__new"" RENAME TO ""testapp_foo""; -- PostgreSQL ALTER TABLE ""testapp_foo"" ALTER COLUMN ""d"" SET DEFAULT 42, ALTER COLUMN ""d"" SET NOT NULL; ALTER TABLE ""testapp_foo"" ALTER COLUMN ""d"" DROP DEFAULT; }}} 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. jarshwah came up with `coalesce(""d"", 42)`. {{{#!sql -- SQLite CREATE TABLE ""testapp_foo__new"" (""id"" integer NOT NULL PRIMARY KEY AUTOINCREMENT, ""d"" integer NOT NULL); INSERT INTO ""testapp_foo__new"" (""id"", ""d"") SELECT ""id"", coalesc(""d"", 42) FROM ""testapp_foo""; DROP TABLE ""testapp_foo""; ALTER TABLE ""testapp_foo__new"" RENAME TO ""testapp_foo""; -- PostgreSQL ALTER TABLE ""testapp_foo"" ALTER COLUMN ""d"" SET DEFAULT 42; UPDATE ""testapp_foo"" SET ""d"" = 42 WHERE ""d"" IS NULL; ALTER TABLE ""testapp_foo"" ALTER COLUMN ""d"" SET NOT NULL; ALTER TABLE ""testapp_foo"" ALTER COLUMN ""d"" DROP DEFAULT; }}}",Uncategorized,assigned,"Database layer (models, ORM)",1.7,Normal,,,Markus Holtermann,Unreviewed,1,0,0,1,0,0