PostgreSQL中如何用单条查询批量修改同名列的数据类型

需求与初始尝试 #

需要将数据库中所有名为created_at的列的数据类型统一设置为timestamp without time zone，执行了以下PL/pgSQL脚本：

DO $$
DECLARE
  table_name text;
BEGIN
  FOR table_name IN SELECT table_name FROM information_schema.columns WHERE column_name = 'created_at' AND table_schema = 'public' LOOP
    EXECUTE 'ALTER TABLE ' || table_name || ' ALTER COLUMN created_at TYPE timestamp without time zone;';
  END LOOP;
END $$;

报错信息 #

执行后出现如下错误：

ERROR:  column reference "table_name" is ambiguous
LINE 1: SELECT table_name FROM information_schema.columns WHERE colu...
               ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
QUERY:  SELECT table_name FROM information_schema.columns WHERE column_name = 'created_at' AND table_schema = 'public'
CONTEXT:  PL/pgSQL function inline_code_block line 5 at FOR over SELECT rows
SQL state: 42702

问题原因与修复方案 #

错误原因是table_name同时是PL/pgSQL的变量名和information_schema.columns表的列名，导致解析歧义。只需在查询时为列名添加表名前缀，明确指定引用的是数据表中的列即可。修复后的脚本如下：

DO $$
DECLARE
  table_name text;
BEGIN
  FOR table_name IN SELECT columns.table_name FROM information_schema.columns WHERE column_name = 'created_at' AND table_schema = 'public' LOOP
    EXECUTE 'ALTER TABLE ' || table_name || ' ALTER COLUMN created_at TYPE timestamp without time zone;';
  END LOOP;
END $$;

非public模式的修改方法 #

如果需要修改非public模式（例如audit模式）下的表，需调整脚本，同时注意在ALTER TABLE语句中带上模式前缀（模式名与表名之间需添加.，避免拼接错误）：

DO $$
DECLARE
  table_name text;
BEGIN
  FOR table_name IN SELECT columns.table_name FROM information_schema.columns WHERE column_name = 'created_at' AND table_schema = 'audit' LOOP
    EXECUTE 'ALTER TABLE audit.' || table_name || ' ALTER COLUMN created_at TYPE timestamp without time zone;';
  END LOOP;
END $$;

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