PostgreSQL批量修改多表字段为citext类型及默认值异常问题

问题原因 #

PostgreSQL执行ALTER TABLE {table_name} ALTER COLUMN {column_name} TYPE citext;时，只会修改字段本身的数据类型，不会自动同步原有默认值的类型。原来的默认值''::bpchar会保留原类型，仅在使用时隐式转换为citext，但元数据里显示的还是bpchar类型的默认值。

解决方法 #

要让默认值显式变为''::citext，需在修改字段类型后，额外执行修改默认值的语句：

单个字段处理

-- 修改字段类型
ALTER TABLE {table_name} ALTER COLUMN {column_name} TYPE citext;
-- 更新默认值为citext类型
ALTER TABLE {table_name} ALTER COLUMN {column_name} SET DEFAULT ''::citext;

批量处理206个字段

如果要批量处理所有目标字段，可通过查询系统表生成批量SQL，避免手动逐个编写：

SELECT 
  CONCAT(
    'ALTER TABLE ', table_name, ' ALTER COLUMN ', column_name, ' TYPE citext; ',
    'ALTER TABLE ', table_name, ' ALTER COLUMN ', column_name, ' SET DEFAULT ''''::citext;'
  ) AS batch_sql
FROM information_schema.columns
WHERE 
  table_schema = 'public' -- 替换为你的表所在schema
  AND data_type IN ('character', 'character varying') -- 匹配你要转换的原字段类型
  -- 可添加更多筛选条件，比如指定表名、列名规则等
  AND column_name IN ('col1', 'col2', ...); -- 或用LIKE匹配列名前缀/后缀

执行上述查询后，复制结果中batch_sql列的内容，批量执行即可完成所有字段的类型修改和默认值同步。

验证效果 #

执行完语句后，可通过以下查询确认字段类型和默认值：

SELECT 
  column_name, 
  data_type, 
  column_default
FROM information_schema.columns
WHERE table_name = '{table_name}' AND column_name = '{column_name}';

此时应能看到data_type为USER-DEFINED（citext属于用户自定义扩展类型），column_default为''::citext。

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