I have the following existing twitter field on the extended UserProfile model and I'd like to change the field type from a URLField to a CharField with max_length of 20. When I try to migrate the change, I get the error django.db.utils.DataError: value too long for type character varying(20). I do not care about existing data in that field and prefer they go blank if there is existing data when migrating. How can I change the field type and clear existing data?

class UserProfile(models.Model):
    user = models.OneToOneField(User, on_delete=models.SET_NULL, null=True)

    # old field
    twitter = models.URLField(verbose_name="Twitter", blank=True)

    # new field
    # twitter = models.CharField(
    #     max_length=20, verbose_name="Twitter Username", null=True, blank=True
    # )

CodePudding user response：

Approach 1st

In order to change to type and maximum length of a field which should know the longest url length in your db. If you are using sqlite3 database then run the following query in your db_shell or sqlite3 shell or whatever you have.

SELECT MAX(LENGTH(column_name)) FROM table_name;

and then set the max_length of the new field to that value.

Approach 2nd

python manage.py shell

and then

# Import your model
profiles = UserProfile.objects.all()
max_length = 0
for userprofile in userprofiles:
    if len(userprofile.twitter) > max_length:
        max_length = len(userprofile.twitter)
print(max_length)

and then set your max_length to the value printed from the above code.

CodePudding user response：

This error means that there are values on that column, that are longer than 20 characters.

You need to track and change them. This will change them into an empty string, so existing data will be lost for good.

UPDATE <table_name>
SET twitter = ""
WHERE LENGTH(twitter) > 20;

Then you can run you migrations.