如何在PostgreSQL中无需逐个输入表名合并100+同结构表？

核心思路 #

借助PostgreSQL系统表生成动态SQL，自动拼接所有目标表的UNION ALL语句，彻底避免手动逐个输入表名的繁琐操作。

步骤1：生成合并语句 #

通过查询information_schema.tables筛选出需要合并的表，自动拼接完整的合并SQL。假设你的表都在public schema下，且表名有统一前缀（比如spatial_data_），执行以下SQL：

SELECT string_agg('SELECT * FROM ' || quote_ident(table_name), ' UNION ALL ')
FROM information_schema.tables
WHERE table_schema = 'public'
  AND table_name LIKE 'spatial_data_%'; -- 根据你的实际表名规则修改筛选条件

执行后会得到类似SELECT * FROM spatial_data_1 UNION ALL SELECT * FROM spatial_data_2 ...的语句，复制该结果即可用于后续合并操作。

步骤2：执行合并（两种实用方式） #

方式一：创建新主表

如果还没有目标主表，直接在生成的语句前加上CREATE TABLE main_spatial_table AS ，执行即可完成创建并合并数据：

CREATE TABLE main_spatial_table AS
SELECT * FROM spatial_data_1 UNION ALL
SELECT * FROM spatial_data_2 UNION ALL
-- ... 自动生成的其他所有表语句

方式二：全自动执行（无需手动复制）

用DO块实现全流程自动化，自动判断主表是否存在，不存在则创建，存在则追加数据：

DO $$
DECLARE
  merge_sql TEXT;
BEGIN
  -- 生成合并语句
  SELECT string_agg('SELECT * FROM ' || quote_ident(table_name), ' UNION ALL ')
  INTO merge_sql
  FROM information_schema.tables
  WHERE table_schema = 'public'
    AND table_name LIKE 'spatial_data_%' -- 筛选目标表
    AND table_name != 'main_spatial_table'; -- 排除主表本身，避免重复合并

  -- 处理主表的创建或插入逻辑
  IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'main_spatial_table') THEN
    merge_sql := 'CREATE TABLE main_spatial_table AS ' || merge_sql;
  ELSE
    merge_sql := 'INSERT INTO main_spatial_table SELECT * FROM (' || merge_sql || ') AS temp_data';
  END IF;

  -- 执行动态SQL
  EXECUTE merge_sql;
END $$;

关键注意事项 #

• 优先使用UNION ALL：UNION会自动去重，性能远低于UNION ALL，且你明确各表数据不同，无需去重操作。
• quote_ident函数作用：处理表名包含特殊字符（如空格、大写字母）的情况，避免出现语法错误。
• 空间索引优化：合并完成后，记得给主表的空间列重建索引（假设空间列名为geom），提升后续查询性能：

  CREATE INDEX idx_main_spatial_geom ON main_spatial_table USING GIST(geom);
  

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