PostgreSQL中更新NAME列并迁移冗余内容至NOTES列问题求助

你的迁移代码未生效主要有三个核心原因：

1. 正则替换未全局执行：PostgreSQL的REGEXP_REPLACE默认仅替换第一个匹配项，未加全局标志'g'导致大部分内容未被处理。
2. WHERE条件语法错误：使用了HTML转义的<>，SQL无法识别为不等于运算符，导致UPDATE语句未命中任何行。
3. NULL值处理遗漏：若notes列为NULL，直接用CONCAT会导致拼接结果为NULL，丢失原有内容。

修正后的迁移代码 #

export async function up({ context: queryInterface }: MigrationInterface) {
  await queryInterface.sequelize.query(`
    UPDATE nodes
    SET notes = COALESCE(notes, '') || ' ' || REGEXP_REPLACE(name, '[0-9.]+', '', 'g'),
        name = REGEXP_REPLACE(name, '[^0-9.]', '', 'g')
    WHERE name <> '';
  `);
}

/** Reverts the changes made in the up function */
export async function down({ context: queryInterface }: MigrationInterface) {
  // 注意：此down函数仅能近似恢复，因为up操作已将非数字内容与原有notes拼接，无法分离原有notes内容
  await queryInterface.sequelize.query(`
    UPDATE nodes
    SET name = name || ' ' || REGEXP_REPLACE(notes, '^.*?(\\(.*?\\))', '\\1'),
        notes = REGEXP_REPLACE(notes, ' \\(.*?\\)$', '')
    WHERE notes <> '';
  `);
}

关键说明 #

• 全局正则替换：添加'g'标志后，REGEXP_REPLACE会替换所有匹配项，确保name仅保留数字和小数点，notes获取所有非数字内容。
• NULL值处理：使用COALESCE(notes, '')将NULL转换为空字符串，避免拼接后结果为NULL。
• down函数局限性：由于up操作将迁移内容与原有notes拼接，无法完全分离原有notes内容，此down函数仅能恢复name的原始结构（数字+迁移内容），并移除notes中的迁移部分。若需要精确回滚，建议在up操作前添加临时列存储原始name值。

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