Redshift中不同Schema下同名表的Union all非暴力实现方法

针对你提到的场景，不需要手动逐个编写表名和列名，可以通过以下两种高效方式实现：

1. 利用系统表生成动态SQL #

Redshift的information_schema系统表存储了所有表和列的元数据，我们可以通过查询这些表自动拼接出Union All语句，完全避免重复的手动编写工作。

针对表a（存在于x、y、z schema）的示例： #

-- 生成表a的Union All语句
SELECT string_agg(
    'SELECT ' || string_agg(column_name, ', ') || ' FROM ' || table_schema || '.a',
    ' UNION ALL '
) AS union_all_sql
FROM information_schema.columns
WHERE table_name = 'a'
  AND table_schema IN ('x', 'y', 'z')
GROUP BY table_name;

执行这个查询后，会直接输出可运行的Union All SQL语句，内容类似：

SELECT d, e, f, g, h FROM x.a UNION ALL SELECT d, e, f, g, h FROM y.a UNION ALL SELECT d, e, f, g, h FROM z.a

同理处理表b和表c： #

• 表b（存在于y、z schema）：只需把table_name改为'b'，table_schema IN改为('y', 'z')即可。
• 表c（存在于x、y schema）：把table_name改为'c'，table_schema IN改为('x', 'y')。

如果遇到同名表列不一致的情况，可以先筛选出所有表的共同列再拼接：

-- 针对列不一致的同名表，取共同列生成Union All
WITH common_columns AS (
    SELECT column_name
    FROM information_schema.columns
    WHERE table_name = 'a'
      AND table_schema IN ('x', 'y', 'z')
    GROUP BY column_name
    HAVING COUNT(DISTINCT table_schema) = 3 -- 确保所有schema的表都有该列
)
SELECT string_agg(
    'SELECT ' || string_agg(column_name, ', ') || ' FROM ' || table_schema || '.a',
    ' UNION ALL '
) AS union_all_sql
FROM information_schema.columns
JOIN common_columns USING (column_name)
WHERE table_name = 'a'
  AND table_schema IN ('x', 'y', 'z')
GROUP BY table_name;

2. 创建统一视图（长期复用场景） #

如果需要频繁查询这些Union All的结果，可以把动态生成的SQL创建为视图，后续直接查询视图即可：

-- 先运行动态SQL生成语句，复制结果后替换到这里
CREATE VIEW unified_a AS
SELECT d, e, f, g, h FROM x.a
UNION ALL
SELECT d, e, f, g, h FROM y.a
UNION ALL
SELECT d, e, f, g, h FROM z.a;

同样，视图的创建语句也可以通过动态SQL自动生成，进一步减少手动操作。

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