PostgreSQL 更新数据库所有表的字段类型 timestamp 类型改为 timestamptz

-- 更新 PostgreSQL 数据库 CSFrameworkV6_System 中所有 timestamp 类型为 timestamptz 类型
-- 注意：请在执行前备份数据库！！！

DO $$
DECLARE
    table_record RECORD;
    column_record RECORD;
    alter_sql TEXT;
BEGIN
    -- 遍历所有包含 timestamp 类型字段的表
    FOR table_record IN 
        SELECT DISTINCT table_name 
        FROM information_schema.columns 
        WHERE table_catalog = 'CSFrameworkV6_System' 
          AND table_schema = 'public' 
          AND data_type = 'timestamp without time zone'
    LOOP
        -- 遍历每个表中的 timestamp 字段
        FOR column_record IN 
            SELECT column_name 
            FROM information_schema.columns 
            WHERE table_catalog = 'CSFrameworkV6_System' 
              AND table_schema = 'public' 
              AND table_name = table_record.table_name 
              AND data_type = 'timestamp without time zone'
        LOOP
            -- 构建 ALTER TABLE 语句
            alter_sql := 'ALTER TABLE "' || table_record.table_name || '" ALTER COLUMN "' || column_record.column_name || '" TYPE timestamptz';
            
            -- 执行修改
            RAISE NOTICE '执行: %', alter_sql;
            EXECUTE alter_sql;
            
            RAISE NOTICE '表 % 的字段 % 已从 timestamp 改为 timestamptz', 
                         table_record.table_name, column_record.column_name;
        END LOOP;
    END LOOP;
    
    RAISE NOTICE '所有 timestamp 字段已成功转换为 timestamptz 类型';
END $$;

-- 查看所有需要转换的 timestamp 字段（只查询不执行）
SELECT 
    table_name as "表名",
    column_name as "字段名",
    data_type as "当前类型",
    'ALTER TABLE ' || table_name || ' ALTER COLUMN ' || column_name || ' TYPE timestamptz;' as "转换SQL"
FROM information_schema.columns 
WHERE table_catalog = 'CSFrameworkV6_System' 
  AND table_schema = 'public' 
  AND data_type = 'timestamp without time zone'
ORDER BY table_name, column_name;