如何修改PostgreSQL中被数组列引用的复合类型且不丢失数据？

因为PostgreSQL不允许直接修改被表列引用的复合类型，所以得通过创建新类型→迁移数据→替换列的流程来实现，结合Symfony/Doctrine的操作步骤如下：

一、数据库层操作（原生SQL） #

假设你的表名为target_table，原复合类型是composite_type(numeric(10,2), numeric(10,3))，新类型要改成new_composite_type(numeric(12,2), numeric(10,3))。

1. 创建新的复合类型

CREATE TYPE new_composite_type AS (
    attr1 numeric(12, 2),
    attr2 numeric(10, 3)
);

2. 给目标表添加临时列

ALTER TABLE target_table ADD COLUMN comp_temp new_composite_type[];

3. 迁移原列数据到临时列
   这里需要处理数组的逐个元素转换，用unnest展开数组，转换每个复合类型元素，再用array_agg重新聚合：

UPDATE target_table
SET comp_temp = (
    SELECT array_agg(
        (
            (elem.attr1)::numeric(12,2),
            elem.attr2
        )::new_composite_type
    )
    FROM unnest(comp) AS elem
);

注：如果原列有NULL值，这个语句会自动保留NULL，因为unnest(NULL)返回空，array_agg空结果会是NULL。

4. 验证数据一致性
   先检查转换后的数据是否正确，比如对比原列和临时列的元素数量、值精度：

-- 检查行数是否一致（排除NULL情况）
SELECT COUNT(*) FROM target_table WHERE comp IS NOT NULL AND comp_temp IS NULL;
-- 检查每个元素的属性值是否匹配（精度调整后的值应该一致）
SELECT
    (unnest(comp)).attr1 AS old_attr1,
    (unnest(comp_temp)).attr1 AS new_attr1,
    (unnest(comp)).attr2 AS old_attr2,
    (unnest(comp_temp)).attr2 AS new_attr2
FROM target_table
WHERE comp IS NOT NULL
LIMIT 100;

5. 替换原列
   验证无误后，删除原列并将临时列重命名：

ALTER TABLE target_table DROP COLUMN comp;
ALTER TABLE target_table RENAME COLUMN comp_temp TO comp;

6. 清理旧类型（可选）
   如果旧类型不再被其他对象引用，可以删除：

DROP TYPE composite_type;

二、Symfony/Doctrine层同步 #

1. 更新实体类映射
   假设你的实体类是TargetEntity，原复合类型对应的Doctrine类型如果是自定义类型，需要修改自定义类型的数据库类型为new_composite_type[]；如果是用原生映射，直接修改字段的type：

// src/Entity/TargetEntity.php
use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity]
class TargetEntity
{
    // ...其他字段

    #[ORM\Column(type: 'new_composite_type[]')] // 这里改成新类型的数组
    private ?array $comp = null;

    // ...getter/setter
}

注：如果Doctrine没有内置支持你的复合类型数组，可能需要自定义Doctrine类型，实现Type类将数据库类型映射为new_composite_type[]。

2. 生成并执行Doctrine迁移
   用Symfony命令生成迁移脚本（如果已经手动执行了数据库操作，迁移脚本需要调整以避免重复执行）：

php bin/console make:migration

打开生成的迁移文件，确认内容是否和手动执行的SQL一致，然后执行迁移：

php bin/console doctrine:migrations:migrate

3. 清理Doctrine缓存
   更新元数据缓存，确保Doctrine识别新的类型：

php bin/console cache:clear --env=prod
php bin/console doctrine:cache:clear-metadata

关键注意事项 #

• 操作前务必备份数据库，避免数据丢失。
• 如果表数据量很大，UPDATE语句可能会锁表，建议在低峰期执行，或者按ID分段分批更新。
• 复合类型的属性顺序必须和原类型一致，否则转换后数据会错位。

内容的提问来源于stack exchange，提问作者kifril-ltd