PostgreSQL删除同日期不同时间重复记录及字段转换问题

问题背景 #

Django应用从Python2/Django1.8迁移至Python3/Django4，同时时区从UTC+2改为UTC+3后，出现两个核心问题：

• 原模型中date字段为DateTimeField，同本地日期的记录被存为不同UTC时间（如2022-12-28 22:00:00+00和2022-12-28 21:00:00+00），导致用Pythondate类型查询时无结果
• 用户误操作创建了重复记录，同一child_id同日期存在多条不同时间的记录，因unique (date, child_id)约束，无法直接将字段类型改为date

第一步：删除重复记录（每日仅保留一条） #

需按child_id和日期分组，保留每组内一条记录（可选择保留最新或最早的，按需调整）。

保留每组最新记录 #

WITH duplicate_records AS (
    SELECT id,
           ROW_NUMBER() OVER (PARTITION BY child_id, DATE(date) ORDER BY date DESC) AS rn
    FROM reports_statchildvisit
)
DELETE FROM reports_statchildvisit
WHERE id IN (SELECT id FROM duplicate_records WHERE rn > 1);

• 逻辑：用窗口函数ROW_NUMBER()给每个child_id+日期分组的记录按时间倒序排序，标记序号rn，删除序号大于1的重复项。

保留每组最早记录 #

若需保留最早的记录，仅需调整排序规则：

WITH duplicate_records AS (
    SELECT id,
           ROW_NUMBER() OVER (PARTITION BY child_id, DATE(date) ORDER BY date ASC) AS rn
    FROM reports_statchildvisit
)
DELETE FROM reports_statchildvisit
WHERE id IN (SELECT id FROM duplicate_records WHERE rn > 1);

第二步：修改字段类型为date #

删除重复后，(date, child_id)的唯一约束不再冲突，可执行字段类型修改：

ALTER TABLE reports_statchildvisit ALTER COLUMN date TYPE date;

补充：修复Django模型与查询 #

修改数据库字段后，同步调整Django模型中的字段类型：

class StatChildVisit(models.Model):
    child = models.ForeignKey(Child, on_delete=models.CASCADE)
    date = models.DateField(default=datetime.date.today)  # 替换原DateTimeField
    visit = models.BooleanField(_('Atended'), default=True)
    disease = models.BooleanField(_('Sick'), default=False)
    other_approved = models.BooleanField(_('Other approved'), default=False)
    garden_group = models.ForeignKey(GardenGroup, verbose_name=_('Garden group'), editable=False, blank=True, null=True, on_delete=models.CASCADE)
    rossecure_visit = models.ForeignKey('rossecure.Visits', editable=False, null=True, blank=True, on_delete=models.CASCADE)

    class Meta:
        verbose_name = _('Attendence')
        verbose_name_plural = _('Attendence')
        index_together = (
            ('date', 'garden_group'),
        )
        unique_together = (
            ('date', 'child'),
        )

之后生成并执行Django迁移，确保模型与数据库结构一致。

原UPDATE语句报错原因 #

你执行的update reports_statchildvisit set date = date(date) + '21:00:00'::time会将同一child_id同日期的所有记录改为相同UTC时间，直接违反unique (date, child_id)约束，因此必须先清理重复记录再进行字段调整。

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