如何在PostgreSQL中自动查找并合并数据库内所有同结构表

实现方案 #

这个需求完全可以实现，核心思路是通过数据库的内置信息表（系统表/INFORMATION_SCHEMA）动态匹配符合结构要求的表名，自动拼接为UNION ALL的合并查询语句，再执行动态SQL即可完成需求，无需手动维护表名列表。

通用实现逻辑说明 #

1. 从数据库的系统元数据表中过滤出当前库下，字段结构完全匹配要求的表名
2. 按SELECT '表名' AS source, date, value, notes FROM 表名的格式，为每个符合要求的表生成查询段
3. 用UNION ALL拼接所有查询段，执行拼接后的完整SQL即可得到带来源标注的合并数据

主流数据库实现示例 #

MySQL 实现

-- 调整分组拼接长度，避免表数量多时SQL被截断
SET SESSION group_concat_max_len = 1024000;

SET @merge_sql = NULL;
-- 动态拼接合并查询SQL
SELECT GROUP_CONCAT(
  CONCAT('SELECT ''', TABLE_NAME, ''' AS `source`, `date`, `value`, `notes` FROM `', TABLE_NAME, '`')
  SEPARATOR ' UNION ALL '
) INTO @merge_sql
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'all_data' -- 指定目标数据库名
GROUP BY TABLE_NAME
-- 校验表结构，确保只纳入字段完全匹配的表，可根据需求补充字段类型校验
HAVING GROUP_CONCAT(COLUMN_NAME ORDER BY COLUMN_NAME) = 'date,notes,value';

-- 执行生成的SQL得到合并结果
PREPARE stmt FROM @merge_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

PostgreSQL 实现

DO $$
DECLARE
    v_merge_sql text;
BEGIN
    SELECT string_agg(
        format('SELECT %L AS source, date, value, notes FROM %I', table_name, table_name),
        ' UNION ALL '
    ) INTO v_merge_sql
    FROM information_schema.columns
    WHERE table_catalog = 'all_data' -- 指定目标数据库名
      AND table_schema = 'public' -- 可根据实际schema调整
    GROUP BY table_name
    HAVING array_agg(column_name ORDER BY column_name) = ARRAY['date', 'notes', 'value'];

    -- 生成临时表存储合并结果
    EXECUTE 'CREATE TEMP TABLE merged_all_data AS ' || v_merge_sql;
END $$;

-- 直接查询临时表获取合并结果
SELECT * FROM merged_all_data;

注意事项 #

• 如果不需要校验表结构，直接合并库内所有表，可删除对应HAVING条件即可
• 表数量较多时要提前调整数据库的分组拼接长度参数，避免生成的SQL被截断
• 首次使用建议先打印拼接好的SQL语句校验，确认逻辑符合预期后再正式执行

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