Ticket #627 (assigned defect)

Opened 4 years ago

Last modified 4 months ago

alter_column fails on postgres when removing null=True

Reported by: anonymous Owned by: andrew
Priority: major Milestone: 1.0
Component: commands Version: unknown
Keywords: Cc:


I ran into an irritation while using South to change several text columns from null=True to null=False. In this case, South generates an update like this:

ALTER TABLE "table" ALTER COLUMN "my_col" TYPE varchar(500), ALTER COLUMN "my_col" SET NOT NULL, ALTER COLUMN "my_col" SET DEFAULT %s ; [u'']

The problem is that postgres won't apply the default value to rows with NULLs and instead simply returns an error ("'my_col' contains null values"). The solution appears to be generating a quick UPDATE foo set bar = "" where bar is null beforehand, which feels like the sort of boilerplate which South should handle for you.

Change History

comment:1 Changed 4 years ago by andrew

  • Status changed from new to assigned
  • Milestone set to 1.0

comment:2 Changed 4 years ago by jonathan.ca@…

I seem to be having this issue when using sqlite3,

This is easily reproducible by having an app with a class defining a single integerfield(null=True), sync, migrate etc and then remove the null=True attribute.

schemamigration works fine, but then the actual

root@case:/opt/BOB/south-test/southtest# python manage.py schemamigration test1 --auto
~ Changed field bar on test1.test
Created 0004_autochg_field_test_bar.py. You can now apply this migration with: ./manage.py migrate test1

but then the actual migration fails with an integrity issue (stacktrace follows):

File "manage.py", line 11, in <module>


File "/usr/lib/python2.6/site-packages/django/core/management/init.py", line 438, in execute_manager


File "/usr/lib/python2.6/site-packages/django/core/management/init.py", line 379, in execute


File "/usr/lib/python2.6/site-packages/django/core/management/base.py", line 191, in run_from_argv

self.execute(*args, options.dict)

File "/usr/lib/python2.6/site-packages/django/core/management/base.py", line 218, in execute

output = self.handle(*args, options)

File "/usr/lib/python2.6/site-packages/South-0.7.2-py2.6.egg/south/management/commands/migrate.py", line 105, in handle

ignore_ghosts = ignore_ghosts,

File "/usr/lib/python2.6/site-packages/South-0.7.2-py2.6.egg/south/migration/init.py", line 191, in migrate_app

success = migrator.migrate_many(target, workplan, database)

File "/usr/lib/python2.6/site-packages/South-0.7.2-py2.6.egg/south/migration/migrators.py", line 221, in migrate_many

result = migrator.class.migrate_many(migrator, target, migrations, database)

File "/usr/lib/python2.6/site-packages/South-0.7.2-py2.6.egg/south/migration/migrators.py", line 292, in migrate_many

result = self.migrate(migration, database)

File "/usr/lib/python2.6/site-packages/South-0.7.2-py2.6.egg/south/migration/migrators.py", line 125, in migrate

result = self.run(migration)

File "/usr/lib/python2.6/site-packages/South-0.7.2-py2.6.egg/south/migration/migrators.py", line 99, in run

return self.run_migration(migration)

File "/usr/lib/python2.6/site-packages/South-0.7.2-py2.6.egg/south/migration/migrators.py", line 81, in run_migration


File "/usr/lib/python2.6/site-packages/South-0.7.2-py2.6.egg/south/migration/migrators.py", line 57, in <lambda>

return (lambda: direction(orm))

File "/opt/BOB/south-test/southtest/test1/migrations/0004_autochg_field_keith_bar.py", line 12, in forwards

db.alter_column('test1_keith', 'bar', self.gf('django.db.models.fields.IntegerField?')())

File "/usr/lib/python2.6/site-packages/South-0.7.2-py2.6.egg/south/db/sqlite3.py", line 128, in alter_column

name: self._column_sql_for_create(table_name, name, field, explicit_name),

File "/usr/lib/python2.6/site-packages/South-0.7.2-py2.6.egg/south/db/sqlite3.py", line 79, in _remake_table

self._copy_data(table_name, temp_name, renames)

File "/usr/lib/python2.6/site-packages/South-0.7.2-py2.6.egg/south/db/sqlite3.py", line 105, in _copy_data


File "/usr/lib/python2.6/site-packages/South-0.7.2-py2.6.egg/south/db/generic.py", line 137, in execute

cursor.execute(sql, params)

File "/usr/lib/python2.6/site-packages/django/db/backends/util.py", line 15, in execute

return self.cursor.execute(sql, params)

File "/usr/lib/python2.6/site-packages/django/db/backends/sqlite3/base.py", line 200, in execute

return Database.Cursor.execute(self, query, params)

django.db.utils.IntegrityError?: _south_new_test1_test.bar may not be NULL

It looks like the execution of restoring the data is failing for some reason... Migration Data:
# encoding: utf-8
import datetime
from south.db import db
from south.v2 import SchemaMigration?
from django.db import models

class Migration(SchemaMigration?):

def forwards(self, orm):

# Changing field 'test.bar'
db.alter_column('test1_test', 'bar', self.gf('django.db.models.fields.IntegerField?')())

def backwards(self, orm):

# Changing field 'test.bar'
db.alter_column('test1_test', 'bar', self.gf('django.db.models.fields.IntegerField?')(null=True))

models = {

'test1.test': {

'Meta': {'object_name': 'test'},
'bar': ('django.db.models.fields.IntegerField?', [], {}),
'foo': ('django.db.models.fields.IntegerField?', [], {'null': 'True'}),
'id': ('django.db.models.fields.AutoField?', [], {'primary_key': 'True'})




comment:3 Changed 4 years ago by greg+south@…

Cc'ing self.

comment:4 Changed 2 years ago by edwin@…

Is anybody working on this problem?

comment:5 Changed 8 months ago by anonymous

This problem is still actual, and it appears when new user realize difference between "null=True" and blank="True" and trying to change model

comment:6 Changed 4 months ago by Seth Livingston

This is still happening, and running a query beforehand to remove nulls from the column doesn't work.

  1. Remove null=True.
  2. schemamigration ...
  3. migrate ...
  4. django.db.utils.IntegrityError: column "abc" contains null values
  5. update ... set abc = 'a default value' where abc = null
  6. migrate ...
  7. django.db.utils.IntegrityError: column "abc" contains null values

Expected: Step 5 should fix the problem.

Note: See TracTickets for help on using tickets.