Django+PostgreSQL迁移时character varying长度超限问题排查

问题描述 #

执行python manage.py migrate时，在应用posts.0005_post_description迁移步骤触发psycopg2.errors.StringDataRightTruncation异常，提示value too long for type character varying(200)。修改模型中description字段的max_length或改为TextField后，问题仍未解决。

错误日志 #

(myenv) root@srv524469:~/django-paynans# python manage.py migrate
Operations to perform:
  Apply all migrations: accounts, admin, auth, contenttypes, django_celery_beat, posts, rest_framework_tracking, sessions, store
Running migrations:
  Applying posts.0005_post_description...Traceback (most recent call last):
  File "/root/miniconda/envs/myenv/lib/python3.9/site-packages/django/db/backends/utils.py", line 87, in _execute
    return self.cursor.execute(sql)
psycopg2.errors.StringDataRightTruncation: value too long for type character varying(200)


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/root/django-paynans/manage.py", line 22, in <module>
    main()
  File "/root/django-paynans/manage.py", line 18, in main
    execute_from_command_line(sys.argv)
  File "/root/miniconda/envs/myenv/lib/python3.9/site-packages/django/core/management/__init__.py", line 442, in execute_from_command_line
    utility.execute()
  File "/root/miniconda/envs/myenv/lib/python3.9/site-packages/django/core/management/__init__.py", line 436, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/root/miniconda/envs/myenv/lib/python3.9/site-packages/django/core/management/base.py", line 412, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/root/miniconda/envs/myenv/lib/python3.9/site-packages/django/core/management/base.py", line 458, in execute
    output = self.handle(*args, **options)
  File "/root/miniconda/envs/myenv/lib/python3.9/site-packages/django/core/management/base.py", line 106, in wrapper
    res = handle_func(*args, **kwargs)
  File "/root/miniconda/envs/myenv/lib/python3.9/site-packages/django/core/management/commands/migrate.py", line 356, in handle
    post_migrate_state = executor.migrate(
  File "/root/miniconda/envs/myenv/lib/python3.9/site-packages/django/db/migrations/executor.py", line 135, in migrate
    state = self._migrate_all_forwards(
  File "/root/miniconda/envs/myenv/lib/python3.9/site-packages/django/db/migrations/executor.py", line 167, in _migrate_all_forwards
    state = self.apply_migration(
  File "/root/miniconda/envs/myenv/lib/python3.9/site-packages/django/db/migrations/executor.py", line 252, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/root/miniconda/envs/myenv/lib/python3.9/site-packages/django/db/migrations/migration.py", line 132, in apply
    operation.database_forwards(
  File "/root/miniconda/envs/myenv/lib/python3.9/site-packages/django/db/migrations/operations/fields.py", line 108, in database_forwards
    schema_editor.add_field(
  File "/root/miniconda/envs/myenv/lib/python3.9/site-packages/django/db/backends/base/schema.py", line 713, in add_field
    self.execute(sql, params)
  File "/root/miniconda/envs/myenv/lib/python3.9/site-packages/django/db/backends/postgresql/schema.py", line 48, in execute
    return super().execute(sql, None)
  File "/root/miniconda/envs/myenv/lib/python3.9/site-packages/django/db/backends/base/schema.py", line 201, in execute
    cursor.execute(sql, params)
  File "/root/miniconda/envs/myenv/lib/python3.9/site-packages/django/db/backends/utils.py", line 102, in execute
    return super().execute(sql, params)
  File "/root/miniconda/envs/myenv/lib/python3.9/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
  File "/root/miniconda/envs/myenv/lib/python3.9/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/root/miniconda/envs/myenv/lib/python3.9/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
  File "/root/miniconda/envs/myenv/lib/python3.9/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/root/miniconda/envs/myenv/lib/python3.9/site-packages/django/db/backends/utils.py", line 87, in _execute
    return self.cursor.execute(sql)
django.db.utils.DataError: value too long for type character varying(200)

迁移状态（showmigrations结果） #

accounts
 [X] 0001_initial
admin
 [X] 0001_initial
 [X] 0002_logentry_remove_auto_add
 [X] 0003_logentry_add_action_flag_choices
auth
 [X] 0001_initial
 [X] 0002_alter_permission_name_max_length
 [X] 0003_alter_user_email_max_length
 [X] 0004_alter_user_username_opts
 [X] 0005_alter_user_last_login_null
 [X] 0006_require_contenttypes_0002
 [X] 0007_alter_validators_add_error_messages
 [X] 0008_alter_user_username_max_length
 [X] 0009_alter_user_last_name_max_length
 [X] 0010_alter_group_name_max_length
 [X] 0011_update_proxy_permissions
 [X] 0012_alter_user_first_name_max_length
contenttypes
 [X] 0001_initial
 [X] 0002_remove_content_type_name
django_celery_beat
 [X] 0001_initial
 [X] 0002_auto_20161118_0346
 [X] 0003_auto_20161209_0049
 [X] 0004_auto_20170221_0000
 [X] 0005_add_solarschedule_events_choices
 [X] 0006_auto_20180322_0932
 [X] 0007_auto_20180521_0826
 [X] 0008_auto_20180914_1922
 [X] 0006_auto_20180210_1226
 [X] 0006_periodictask_priority
 [X] 0009_periodictask_headers
 [X] 0010_auto_20190429_0326
 [X] 0011_auto_20190508_0153
 [X] 0012_periodictask_expire_seconds
 [X] 0013_auto_20200609_0727
 [X] 0014_remove_clockedschedule_enabled
 [X] 0015_edit_solarschedule_events_choices
 [X] 0016_alter_crontabschedule_timezone
 [X] 0017_alter_crontabschedule_month_of_year
 [X] 0018_improve_crontab_helptext
posts
 [X] 0001_initial
 [X] 0002_examplecategory_examplepost
 [X] 0003_comment
 [X] 0004_alter_examplepost_video_alter_post_video
 [ ] 0005_post_description
 [ ] 0006_questions
 [ ] 0007_remove_examplepost_video_examplepost_content_photo
 [ ] 0008_ticket
 [ ] 0009_alter_questions_question
 [ ] 0010_alter_examplecategory_description_and_more
 [ ] 0011_alter_post_description
 [ ] 0012_alter_post_description
 [ ] 0013_alter_post_cover_photo_alter_post_description
rest_framework_tracking
 [ ] 0001_initial
 [ ] 0002_auto_20170118_1713
 [ ] 0003_add_errors
 [ ] 0004_add_verbose_name
 [ ] 0005_auto_20171219_1537
 [ ] 0006_view_and_view_method_nullable
 [ ] 0006_auto_20180315_1442
 [ ] 0007_merge_20180419_1646
 [ ] 0008_auto_20200201_2048
 [ ] 0009_view_method_max_length_200
 [ ] 0010_auto_20200609_1404
 [ ] 0011_auto_20201117_2016
 [ ] 0012_auto_20210930_0713
 [ ] 0013_apirequestlog_user_agent
 [ ] 0014_alter_apirequestlog_id
sessions
 [ ] 0001_initial
store
 [ ] 0001_initial
 [ ] 0002_product_best_mark

Post模型代码 #

class Post(models.Model):
    author = models.ForeignKey(User, on_delete=models.SET_NULL, null=True)
    subject = models.ForeignKey(Subject, on_delete=models.SET_NULL, null=True)
    TYPE_CHOICES = (
        ('post', 'Post'),
        ('article', 'Article'),
    )
    post_type = models.CharField(max_length=20, choices=TYPE_CHOICES)
    title = models.CharField(max_length=50)
    description = models.CharField(max_length=190)
    content = models.TextField()
    cover_photo = models.ImageField(
        upload_to=cover_photo_path_generator
    )
    video = models.URLField(null=True, blank=True)
    required_time = models.IntegerField(default=10)
    saw_count = models.IntegerField(default=0)
    likes = models.IntegerField(default=0)
    created = models.DateTimeField(auto_now_add=True)
    updated = models.DateTimeField(auto_now=True)

    def increment_saw_count(self):
        """Increment the saw count of the post by 1."""
        Post.objects.filter(pk=self.pk).update(saw_count=F('saw_count') + 1)

    def increment_like(self):
        """Increment the like count of the post by 1."""
        Post.objects.filter(pk=self.pk).update(likes=F('likes') + 1)

    def __str__(self):
        return f'{self.title}_{self.pk}'

问题分析 #

核心问题在于未执行的posts.0005_post_description迁移文件本身：

1. 该迁移是向Post模型添加description字段，文件内定义的字段参数（如max_length=200）并未同步你后续对模型的修改。
2. 若添加字段时未设置null=True，Django会要求提供默认值，若你输入的默认值长度超过200，执行迁移时会触发长度超限错误；即使你后续修改了模型字段，未执行的旧迁移文件仍会使用原始定义执行。
3. 迁移是顺序执行的，0005未完成会阻塞后续所有posts相关迁移（如0011-0013的字段修改）。

解决方案 #

方案1：直接修改0005_post_description迁移文件 #

找到posts/migrations/0005_post_description.py，调整字段定义：

• 若允许字段为空，添加null=True, blank=True：

operations = [
    migrations.AddField(
        model_name='post',
        name='description',
        field=models.CharField(max_length=190, null=True, blank=True),
        # 或改为TextField：field=models.TextField(null=True, blank=True),
    ),
]

• 若必须设置默认值，确保默认值长度不超过max_length，同时调大max_length适配需求：

operations = [
    migrations.AddField(
        model_name='post',
        name='description',
        field=models.CharField(max_length=500, default='默认描述'),
    ),
]

修改后重新执行python manage.py migrate。

方案2：删除旧迁移并重新生成（适用于开发环境） #

若开发环境无重要数据，可删除0005_post_description及后续未执行的posts迁移，重新生成迁移：

# 删除未执行的posts迁移文件
rm posts/migrations/0005_*.py posts/migrations/0006_*.py ...
# 重新生成迁移
python manage.py makemigrations posts
# 执行迁移
python manage.py migrate

注意事项 #

• 生产环境禁止随意修改已执行的迁移文件，但若0005未执行则可安全修改。
• 后续修改模型字段后，需重新生成迁移文件并执行，确保迁移文件与模型定义同步。

内容的提问来源于stack exchange，提问作者shayan