PostgreSQL批量更新字段类型

曹振华

已于 2024-02-07 16:01:39 修改

阅读量853

点赞数 8

分类专栏： PostgreSQL 人大金仓文章标签： sql PostgreSQL 批量更新字段类型循环 LOOP

于 2024-02-02 16:59:59 首次发布

本文链接：https://blog.csdn.net/chadcao/article/details/135997395

版权

人大金仓同时被 2 个专栏收录

3 篇文章 0 订阅

订阅专栏

PostgreSQL

2 篇文章 0 订阅

订阅专栏

单个字段修改

ALTER TABLE "SYS_User" ALTER COLUMN "UserId" TYPE UUID
USING "UserId"::UUID;

批量修改

-- 更新字段varchar(36)到uuid
-- 获取所有varchar(36)字段
SELECT table_name, column_name, data_type, character_maximum_length, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'public' and data_type='varchar' and character_maximum_length='36'

-- 更新字段varchar(36)到uuid
DO $$ 
DECLARE
   table_name text;
   column_name text;
BEGIN
   FOR table_name, column_name IN
      SELECT table_name, column_name
      FROM information_schema.columns
      WHERE table_schema = 'public'
         AND data_type = 'varchar'
         AND character_maximum_length = 36
   LOOP
      EXECUTE format('ALTER TABLE %I ALTER COLUMN %I TYPE UUID USING %I::UUID', table_name, column_name, column_name);
   END LOOP;
END $$;

-- 校验结果
SELECT table_name, column_name, data_type, character_maximum_length, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'public' and data_type='uuid'